remove character from string

Hi I am filling a table with a select statement and one of the columns
(Time1data)returned from the select statement looks like 10:30, it is a
character type. The table I am trying to put it in is expecting 1030 and is
character 4 type. Just wondering if anyone knows how to remove the : as part
of the select statement?
I tried select Right (Time1data,4) but this just gets the right 4 characters.
Thanks.
Paul G
Software engineer.
Hi Paul,
You can use REPLACE, like this:
SELECT REPLACE(Time1data, ':', '')
FROM ...
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||It worked! thanks.
Paul G
Software engineer.
"Plamen Ratchev" wrote:

> Hi Paul,
> You can use REPLACE, like this:
> SELECT REPLACE(Time1data, ':', '')
> FROM ...
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
sql

remove character from string

Hi I am filling a table with a select statement and one of the columns
(Time1data)returned from the select statement looks like 10:30, it is a
character type. The table I am trying to put it in is expecting 1030 and is
character 4 type. Just wondering if anyone knows how to remove the : as part
of the select statement?
I tried select Right (Time1data,4) but this just gets the right 4 characters.
Thanks.
--
Paul G
Software engineer.Hi Paul,
You can use REPLACE, like this:
SELECT REPLACE(Time1data, ':', '')
FROM ...
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||It worked! thanks.
--
Paul G
Software engineer.
"Plamen Ratchev" wrote:
> Hi Paul,
> You can use REPLACE, like this:
> SELECT REPLACE(Time1data, ':', '')
> FROM ...
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com

remove character from string

Hi I am filling a table with a select statement and one of the columns
(Time1data)returned from the select statement looks like 10:30, it is a
character type. The table I am trying to put it in is expecting 1030 and is
character 4 type. Just wondering if anyone knows how to remove the : as par
t
of the select statement?
I tried select Right (Time1data,4) but this just gets the right 4 characters
.
Thanks.
--
Paul G
Software engineer.Hi Paul,
You can use REPLACE, like this:
SELECT REPLACE(Time1data, ':', '')
FROM ...
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||It worked! thanks.
--
Paul G
Software engineer.
"Plamen Ratchev" wrote:

> Hi Paul,
> You can use REPLACE, like this:
> SELECT REPLACE(Time1data, ':', '')
> FROM ...
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com

Remove "Select All" Options from Multi Select Parameter Dropdown

Hi All

I am using SQL Server 2005 with SP2. I have multi select parameter in the report. In SP2 reporting services gives Select All option in the drop down.

Is there any way I can remove that option from the list?

Thanks

No you can't remove it. It was removed by the MS team in SP1, but popular demand meant it was reinstated. The only way to get rid of it is to build your own UI.|||

FYI : the story is here

http://blogs.msdn.com/bwelcker/archive/2006/08/14/700189.aspx

and here

http://blogs.msdn.com/bimusings/archive/2007/04/09/the-curious-saga-of-select-all-and-multi-valued-parameters-in-reporting-services.aspx

... note that in the followup comments in the first one, Brian W does note that:

re: Rather Ripped (Select All in Service Pack 1)

Sunday, February 04, 2007 5:49 PM by bwelcker

I understand all of the angst around this decision. We will definitely make this configurable in the future, either on a server level or a per-report level.

... after which somebody responds "I prefer per-server" -- but I personally (LSN) would prefer per-report. So they really can't win, can they <g>. No, seriously, in this case they can't win unless they do both...

>L<

|||

Thanks for the reply everyone.

That answers my question.

Regards

Remove "All" and Keep "select All" from parameters

I know that when you select Multi from the parameter report properties, that it automatically makes "Select All" Available. So how would i take off the "All Value". My users dont want to see All, if Select ALL is available.

How do i exclude from "All" from my report parameter/dataset?

Are you returning "All" from a stored procedure or is it one of your multi value parameter value?

remote sql query

we are trying to execute a remote sql query from one sql server against
another. Both servers are in the same domain. The query is a simple select
* from tablename where surname = whatever. We precede this with an
'opendatasource' statement. The query returns only a few rows. When we run
the query between two sql servers on the same lan segment the query runs in a
second or two. When we run the query between servers on two different
network segments (seperated by a firewall) but still both in the same domain
the same query takes 50+ seconds to run. Looking further, it seems that the
query is actually being executed on the local server so the entire table
(over 1 million rows) is copied locally before running the query. How can I
run the query as a remote query so that the entire query is run on the remote
sql server and only the query results are passed accross the network to the
local server? I have also tried creating a linked server and using OPENQUERY
but still takes ages to run.
Thanks
Open query should pass the query to the other server for execution. Are you
sure you are not doing a JOIN to a local table? In ay case you can create a
stored procedure on the remote server and execute that.
Andrew J. Kelly SQL MVP
"lightningtechie" <lightningtechie@.discussions.microsoft.com> wrote in
message news:4EB250D4-A5D1-4C5C-AC6D-DFA30B09F930@.microsoft.com...
> we are trying to execute a remote sql query from one sql server against
> another. Both servers are in the same domain. The query is a simple
> select
> * from tablename where surname = whatever. We precede this with an
> 'opendatasource' statement. The query returns only a few rows. When we
> run
> the query between two sql servers on the same lan segment the query runs
> in a
> second or two. When we run the query between servers on two different
> network segments (seperated by a firewall) but still both in the same
> domain
> the same query takes 50+ seconds to run. Looking further, it seems that
> the
> query is actually being executed on the local server so the entire table
> (over 1 million rows) is copied locally before running the query. How can
> I
> run the query as a remote query so that the entire query is run on the
> remote
> sql server and only the query results are passed accross the network to
> the
> local server? I have also tried creating a linked server and using
> OPENQUERY
> but still takes ages to run.
> Thanks
|||Perhaps it's the location of the WHERE clause? If you say
SELECT LastName
FROM
OPENQUERY(Otherserver, 'SELECT EmployeeID, LastName FROM
Northwind.dbo.Employees')
WHERE EmployeeID > 5
You are asking for all the rows and then you will apply the where clause
locally.
If you saySELECT LastName
FROM
OPENQUERY(Otherserver, 'SELECT EmployeeID, LastName FROM
Northwind.dbo.Employees WHERE EmployeeID > 5')
You are sending the where clause to the remote server - a good thing, and
only getting the qualified rows returned to the local server.
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OB7ad5%23KGHA.2604@.TK2MSFTNGP09.phx.gbl...
> Open query should pass the query to the other server for execution. Are
> you sure you are not doing a JOIN to a local table? In ay case you can
> create a stored procedure on the remote server and execute that.
>
> --
> Andrew J. Kelly SQL MVP
>
> "lightningtechie" <lightningtechie@.discussions.microsoft.com> wrote in
> message news:4EB250D4-A5D1-4C5C-AC6D-DFA30B09F930@.microsoft.com...
>

remote sql query

we are trying to execute a remote sql query from one sql server against
another. Both servers are in the same domain. The query is a simple select
* from tablename where surname = whatever. We precede this with an
'opendatasource' statement. The query returns only a few rows. When we run
the query between two sql servers on the same lan segment the query runs in
a
second or two. When we run the query between servers on two different
network segments (seperated by a firewall) but still both in the same domain
the same query takes 50+ seconds to run. Looking further, it seems that the
query is actually being executed on the local server so the entire table
(over 1 million rows) is copied locally before running the query. How can I
run the query as a remote query so that the entire query is run on the remot
e
sql server and only the query results are passed accross the network to the
local server? I have also tried creating a linked server and using OPENQUER
Y
but still takes ages to run.
ThanksOpen query should pass the query to the other server for execution. Are you
sure you are not doing a JOIN to a local table? In ay case you can create a
stored procedure on the remote server and execute that.
Andrew J. Kelly SQL MVP
"lightningtechie" <lightningtechie@.discussions.microsoft.com> wrote in
message news:4EB250D4-A5D1-4C5C-AC6D-DFA30B09F930@.microsoft.com...
> we are trying to execute a remote sql query from one sql server against
> another. Both servers are in the same domain. The query is a simple
> select
> * from tablename where surname = whatever. We precede this with an
> 'opendatasource' statement. The query returns only a few rows. When we
> run
> the query between two sql servers on the same lan segment the query runs
> in a
> second or two. When we run the query between servers on two different
> network segments (seperated by a firewall) but still both in the same
> domain
> the same query takes 50+ seconds to run. Looking further, it seems that
> the
> query is actually being executed on the local server so the entire table
> (over 1 million rows) is copied locally before running the query. How can
> I
> run the query as a remote query so that the entire query is run on the
> remote
> sql server and only the query results are passed accross the network to
> the
> local server? I have also tried creating a linked server and using
> OPENQUERY
> but still takes ages to run.
> Thanks|||Perhaps it's the location of the WHERE clause? If you say
SELECT LastName
FROM
OPENQUERY(Otherserver, 'SELECT EmployeeID, LastName FROM
Northwind.dbo.Employees')
WHERE EmployeeID > 5
You are asking for all the rows and then you will apply the where clause
locally.
If you saySELECT LastName
FROM
OPENQUERY(Otherserver, 'SELECT EmployeeID, LastName FROM
Northwind.dbo.Employees WHERE EmployeeID > 5')
You are sending the where clause to the remote server - a good thing, and
only getting the qualified rows returned to the local server.
--
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OB7ad5%23KGHA.2604@.TK2MSFTNGP09.phx.gbl...
> Open query should pass the query to the other server for execution. Are
> you sure you are not doing a JOIN to a local table? In ay case you can
> create a stored procedure on the remote server and execute that.
>
> --
> Andrew J. Kelly SQL MVP
>
> "lightningtechie" <lightningtechie@.discussions.microsoft.com> wrote in
> message news:4EB250D4-A5D1-4C5C-AC6D-DFA30B09F930@.microsoft.com...
>

Remote Join SQL7

This functionality seem not work fine on this version of Ms SQL Server.

The syntax
SELECT *
FROM CATAL_MAG CATM
REMOTE JOIN
CATAL CAT
ON CATM.CODE_ART=CAT.CODE_ART
WHERE CATM.NO_MAG='100'
AND CATM.CODE_ART='MART'

This query give me error on some SQL7 Servers (yes not all configurations crash)

The error is :

Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near 'REMOTE'.

What is wrong on my configuration?Originally posted by Franck6
yes not all configurations crash


I could be wrong but I highly doubt it

I've never seen that syntax...

Look in BOL..it's not even there...

You sure you're talking about SQL Server...you know Redmond?

http://www.ci.redmond.wa.us/

remote debug problem

I am trying to do remote debugging and doesn't seem to be working....I brin
g up query analyzer, select the datbase, select the sp, right click debug, e
nter a value and ok...when I do this it doesn't pass/break...it completes
the query and grays out the
buttons at the top....
steps I have done:got click happy
things I have tried...
1. i put in a local admin account under properties/security after you right
click on the sql instance
2. run dcomcnfg
a. edited the default access permission - gave everyone allow access permiss
ion
b. under applications tab - I went to properties ont he sqldbreg edited the
use custom access permission under security to allow everyone permission
c. on that same com(sqldbreg) - I changed the identity to use a the local us
er I setup with admin rights
what else can I try?|||got click happy
things I have tried...
1. i put in a local admin account under properties/security after you right
click on the sql instance
2. run dcomcnfg
a. edited the default access permission - gave everyone allow access permiss
ion
b. under applications tab - I went to properties ont he sqldbreg edited the
use custom access permission under security to allow everyone permission
c. on that same com(sqldbreg) - I changed the identity to use a the local us
er I setup with admin rights
what else can I try?|||got click happy
things I have tried...
1. i put in a local admin account under properties/security after you right
click on the sql instance
2. run dcomcnfg
a. edited the default access permission - gave everyone allow access permiss
ion
b. under applications tab - I went to properties ont he sqldbreg edited the
use custom access permission under security to allow everyone permission
c. on that same com(sqldbreg) - I changed the identity to use a the local us
er I setup with admin rights
what else can I try?|||Make sure the version of client tools is the same as the server. In Query
Analyzer look in Help/About.
That fixed the problem for me.
"Jamie Elliott" wrote:

> got click happy
> things I have tried...
> 1. i put in a local admin account under properties/security after you righ
t click on the sql instance
> 2. run dcomcnfg
> a. edited the default access permission - gave everyone allow acces
s permission
> b. under applications tab - I went to properties ont he sqldbreg ed
ited the use custom access permission under security to allow everyone permi
ssion
> c. on that same com(sqldbreg) - I changed the identity to use a the
local user I setup with admin rights
> what else can I try?

remote debug problem

I am trying to do remote debugging and doesn't seem to be working....I bring up query analyzer, select the datbase, select the sp, right click debug, enter a value and ok...when I do this it doesn't pass/break...it completes the query and grays out the
buttons at the top....
steps I have done:
got click happy
things I have tried...
1. i put in a local admin account under properties/security after you right click on the sql instance
2. run dcomcnfg
a. edited the default access permission - gave everyone allow access permission
b. under applications tab - I went to properties ont he sqldbreg edited the use custom access permission under security to allow everyone permission
c. on that same com(sqldbreg) - I changed the identity to use a the local user I setup with admin rights
what else can I try?
|||got click happy
things I have tried...
1. i put in a local admin account under properties/security after you right click on the sql instance
2. run dcomcnfg
a. edited the default access permission - gave everyone allow access permission
b. under applications tab - I went to properties ont he sqldbreg edited the use custom access permission under security to allow everyone permission
c. on that same com(sqldbreg) - I changed the identity to use a the local user I setup with admin rights
what else can I try?
|||got click happy
things I have tried...
1. i put in a local admin account under properties/security after you right click on the sql instance
2. run dcomcnfg
a. edited the default access permission - gave everyone allow access permission
b. under applications tab - I went to properties ont he sqldbreg edited the use custom access permission under security to allow everyone permission
c. on that same com(sqldbreg) - I changed the identity to use a the local user I setup with admin rights
what else can I try?
|||Make sure the version of client tools is the same as the server. In Query
Analyzer look in Help/About.
That fixed the problem for me.
"Jamie Elliott" wrote:

> got click happy
> things I have tried...
> 1. i put in a local admin account under properties/security after you right click on the sql instance
> 2. run dcomcnfg
> a. edited the default access permission - gave everyone allow access permission
> b. under applications tab - I went to properties ont he sqldbreg edited the use custom access permission under security to allow everyone permission
> c. on that same com(sqldbreg) - I changed the identity to use a the local user I setup with admin rights
> what else can I try?