Remove all non digit characters from some string

Hello.
How can I write some stored procedure or function to which I will pass the
string and it will return me only numbers from my string?
Exactly I need to remove all non digit characters from some string.
Tank you.
You could write an extended stored proc in a dll...dont know if this best
solution though
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello.
> How can I write some stored procedure or function to which I will pass the
> string and it will return me only numbers from my string?
> Exactly I need to remove all non digit characters from some string.
> Tank you.
>
|||David,
If you have a fixed set of characters to remove i.e., like with a phone
number you could do something like this:
DECLARE @.TELEPHONENUMBER VARCHAR(25)
SET @.TELEPHONENUMBER = '(503)999-1851'
SELECT REPLACE(REPLACE(REPLACE(REPLACE(@.TELEPHONENUMBER,' (',''),')',''),'
',''),'-','')
HTH
Jerry
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello.
> How can I write some stored procedure or function to which I will pass the
> string and it will return me only numbers from my string?
> Exactly I need to remove all non digit characters from some string.
> Tank you.
>
|||Hi,
This might not be the most efficient way of doing it but it works :-)
DECLARE @.InputString varchar(32)
DECLARE @.OutputString varchar(32)
DECLARE @.i int
SET @.InputString =3D 'ABC123!=A3$456'
SET @.i =3D NULLIF(PATINDEX('%[0-9]%',@.InputString),0)
WHILE @.i IS NOT NULL
BEGIN
SET @.OutputString =3D isnull(@.OutputString,'') +
SUBSTRING(@.Inputstring, @.i, 1)
SET @.i =3D @.i +
NULLIF(PATINDEX('%[0-9]%',SUBSTRING(@.InputString,@.i+1,len(@.InputString))) ,0)
END
SELECT @.OutputString -- This gives '123456'
I would normally use a auxilary numbers table instead of the loop, but
didn't have time to go through that in this example.
Jerry Spivey wrote:[vbcol=seagreen]
> David,
> If you have a fixed set of characters to remove i.e., like with a phone
> number you could do something like this:
> DECLARE @.TELEPHONENUMBER VARCHAR(25)
> SET @.TELEPHONENUMBER =3D '(503)999-1851'
> SELECT REPLACE(REPLACE(REPLACE(REPLACE(@.TELEPHONENUMBER,' (',''),')',''),'
> ',''),'-','')
> HTH
> Jerry
> "David Dvali" <david_dvali@.hotmail.com> wrote in message
> news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
the[vbcol=seagreen]
|||This should do the trick...
DECLARE @.Index smallint
DECLARE @.SearchString varchar(25)
DECLARE @.StringLength smallint
DECLARE @.CurrentChar Char(1)
SET @.searchstring = 'ab35d0l2rle.p1C9,:$#47)('
SET @.StringLength = LEN(@.SearchString)
SET @.Index = 1
WHILE @.Index <= @.StringLength
BEGIN
SET @.CurrentChar = SUBSTRING(@.SearchString, @.index, 1)
if @.CurrentChar NOT IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
BEGIN
SET @.SearchString = REPLACE(@.SearchString, @.CurrentChar, 'X')
END
SET @.Index = @.Index + 1
END
SELECT REPLACE(@.SearchString, 'X','')
Returns 35021947
Replace all the non-numeric values with 'X' (so as to not screw up the WHILE
index,) then replace all Xs with an empty string. In a stored proc,
@.SearchString would be the parameter...
HTH,
Mike

Remove all non digit characters from some string

Hello.
How can I write some stored procedure or function to which I will pass the
string and it will return me only numbers from my string?
Exactly I need to remove all non digit characters from some string.
Tank you.You could write an extended stored proc in a dll...dont know if this best
solution though
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello.
> How can I write some stored procedure or function to which I will pass the
> string and it will return me only numbers from my string?
> Exactly I need to remove all non digit characters from some string.
> Tank you.
>|||David,
If you have a fixed set of characters to remove i.e., like with a phone
number you could do something like this:
DECLARE @.TELEPHONENUMBER VARCHAR(25)
SET @.TELEPHONENUMBER = '(503)999-1851'
SELECT REPLACE(REPLACE(REPLACE(REPLACE(@.TELEPHONENUMBER,'(',''),')',''),'
',''),'-','')
HTH
Jerry
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello.
> How can I write some stored procedure or function to which I will pass the
> string and it will return me only numbers from my string?
> Exactly I need to remove all non digit characters from some string.
> Tank you.
>|||Hi,
This might not be the most efficient way of doing it but it works :-)
DECLARE @.InputString varchar(32)
DECLARE @.OutputString varchar(32)
DECLARE @.i int
SET @.InputString =3D 'ABC123!=A3$456'
SET @.i =3D NULLIF(PATINDEX('%[0-9]%',@.InputString),0)
WHILE @.i IS NOT NULL
BEGIN
SET @.OutputString =3D isnull(@.OutputString,'') +
SUBSTRING(@.Inputstring, @.i, 1)
SET @.i =3D @.i +
NULLIF(PATINDEX('%[0-9]%',SUBSTRING(@.InputString,@.i+1,len(@.InputString))),0)
END
SELECT @.OutputString -- This gives '123456'
I would normally use a auxilary numbers table instead of the loop, but
didn't have time to go through that in this example.
Jerry Spivey wrote:
> David,
> If you have a fixed set of characters to remove i.e., like with a phone
> number you could do something like this:
> DECLARE @.TELEPHONENUMBER VARCHAR(25)
> SET @.TELEPHONENUMBER =3D '(503)999-1851'
> SELECT REPLACE(REPLACE(REPLACE(REPLACE(@.TELEPHONENUMBER,'(',''),')',''),'
> ',''),'-','')
> HTH
> Jerry
> "David Dvali" <david_dvali@.hotmail.com> wrote in message
> news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> > Hello.
> >
> > How can I write some stored procedure or function to which I will pass =the
> > string and it will return me only numbers from my string?
> > Exactly I need to remove all non digit characters from some string.
> >
> > Tank you.
> >|||This should do the trick...
DECLARE @.Index smallint
DECLARE @.SearchString varchar(25)
DECLARE @.StringLength smallint
DECLARE @.CurrentChar Char(1)
SET @.searchstring = 'ab35d0l2rle.p1C9,:$#47)('
SET @.StringLength = LEN(@.SearchString)
SET @.Index = 1
WHILE @.Index <= @.StringLength
BEGIN
SET @.CurrentChar = SUBSTRING(@.SearchString, @.index, 1)
if @.CurrentChar NOT IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
BEGIN
SET @.SearchString = REPLACE(@.SearchString, @.CurrentChar, 'X')
END
SET @.Index = @.Index + 1
END
SELECT REPLACE(@.SearchString, 'X','')
Returns 35021947
Replace all the non-numeric values with 'X' (so as to not screw up the WHILE
index,) then replace all Xs with an empty string. In a stored proc,
@.SearchString would be the parameter...
HTH,
Mike

Remove all non digit characters from some string

Hello.
How can I write some stored procedure or function to which I will pass the
string and it will return me only numbers from my string?
Exactly I need to remove all non digit characters from some string.
Tank you.You could write an extended stored proc in a dll...dont know if this best
solution though
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello.
> How can I write some stored procedure or function to which I will pass the
> string and it will return me only numbers from my string?
> Exactly I need to remove all non digit characters from some string.
> Tank you.
>|||David,
If you have a fixed set of characters to remove i.e., like with a phone
number you could do something like this:
DECLARE @.TELEPHONENUMBER VARCHAR(25)
SET @.TELEPHONENUMBER = '(503)999-1851'
SELECT REPLACE(REPLACE(REPLACE(REPLACE(@.TELEPHO
NENUMBER,'(',''),')',''),'
',''),'-','')
HTH
Jerry
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello.
> How can I write some stored procedure or function to which I will pass the
> string and it will return me only numbers from my string?
> Exactly I need to remove all non digit characters from some string.
> Tank you.
>|||Hi,
This might not be the most efficient way of doing it but it works :-)
DECLARE @.InputString varchar(32)
DECLARE @.OutputString varchar(32)
DECLARE @.i int
SET @.InputString =3D 'ABC123!=A3$456'
SET @.i =3D NULLIF(PATINDEX('%[0-9]%',@.InputString),0)
WHILE @.i IS NOT NULL
BEGIN
SET @.OutputString =3D isnull(@.OutputString,'') +
SUBSTRING(@.Inputstring, @.i, 1)
SET @.i =3D @.i +
NULLIF(PATINDEX('%[0- 9]%',SUBSTRING(@.InputString,@.i+1,len(@.In
putString))
),0)
END
SELECT @.OutputString -- This gives '123456'
I would normally use a auxilary numbers table instead of the loop, but
didn't have time to go through that in this example.
Jerry Spivey wrote:[vbcol=seagreen]
> David,
> If you have a fixed set of characters to remove i.e., like with a phone
> number you could do something like this:
> DECLARE @.TELEPHONENUMBER VARCHAR(25)
> SET @.TELEPHONENUMBER =3D '(503)999-1851'
> SELECT REPLACE(REPLACE(REPLACE(REPLACE(@.TELEPHO
NENUMBER,'(',''),')',''),'
> ',''),'-','')
> HTH
> Jerry
> "David Dvali" <david_dvali@.hotmail.com> wrote in message
> news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
the[vbcol=seagreen]|||This should do the trick...
DECLARE @.Index smallint
DECLARE @.SearchString varchar(25)
DECLARE @.StringLength smallint
DECLARE @.CurrentChar Char(1)
SET @.searchstring = 'ab35d0l2rle.p1C9,:$#47)('
SET @.StringLength = LEN(@.SearchString)
SET @.Index = 1
WHILE @.Index <= @.StringLength
BEGIN
SET @.CurrentChar = SUBSTRING(@.SearchString, @.index, 1)
if @.CurrentChar NOT IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
BEGIN
SET @.SearchString = REPLACE(@.SearchString, @.CurrentChar, 'X')
END
SET @.Index = @.Index + 1
END
SELECT REPLACE(@.SearchString, 'X','')
Returns 35021947
Replace all the non-numeric values with 'X' (so as to not screw up the WHILE
index,) then replace all Xs with an empty string. In a stored proc,
@.SearchString would be the parameter...
HTH,
Mike

Remote-server execution of a global temp stored procedure

I have the following execution of a global temporary stored procedure on a remote SQL 2000 server:

insert into targetTable
exec remoteServer.master.dbo.sp_MSforeachdb ' ', @.precommand = 'exec ##up_fetchQuery'

This is an ugly duck query but it seems to work fine. when I try to directly execute the remote stored procedure such as with

insert into query_log exec remoteServer.master.dbo.##up_fetchQuery

I get execution error

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure '##up_xportQueryLog'.
Database name 'master' ignored, referencing object in tempdb.

When I try

insert into query_log exec remoteServer.tempdb.dbo.##up_fetchQuery

I get

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure '##up_xportQueryLog'.
Database name 'tempdb' ignored, referencing object in tempdb.

with

insert into query_log exec remoteServer..dbo.##up_fetchQuery

or

insert into query_log exec remoteServer...##up_fetchQuery

I get

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure '##up_xportQueryLog'.

I guess the remote server has trouble resolving the name of the global temp stored procedure when its reference comes in as a remote stored procedure calls. Is there any way to directly call a global temp stored procedure from a remote server or do I need to stick with this goofy-looking work-around?

Dave

You can do below instead of using the undocumented system SP "sp_MSforeachdb".

insert into targetTable
exec remoteServer.master.dbo.sp_executesql N'exec ##up_fetchQuery'

|||

Thank you. (Laughing at myself; I thought of everything except the obvious; oh, brother!)

It definitely helps to have another set of eyes look at something.

Dave

Remotely Executing a Stored Proc

I have an ASP page that executes a stored procedure on the local SQL Server instance, but I need that stored proc to execute another stored proc on a remote server. Basically, proc A on server 1 executes and passes a parameter to proc B on server 2. Is there a way to do this without linking server 2?
TIAPlease take a look at the linked server functionality. This allows you to execute a distributed query or call a remote stored procedure.

remote system call?

Hi all,
I want to be able to have a stored procedure on server A to call a
stored procedure on Server B. I have my stored procedures in place. I have
used Linked servers on server A to define Server B so, that server A can
talk to server B. However I seem to have missed something. This is the
message that I get:
Could not find stored procedure 'sp_tt_load'.(42000,2812)
Procedure(sp_tt_dumpandload_for_standby).
sp_tt_dumpandload_for_standby (server A) does a database dump and then calls
sp_tt_load ( server B). Server B will load the dump onto it's standby
database.
TIA for all the help.
Red
make sure you are using the full name to call the proc including the owner
name eg:
ServerB.databasename.dbo.sp_tt_load
"Red" <RedWolf_56@.yahoo.com> wrote in message
news:%23D2fn0CnEHA.2140@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I want to be able to have a stored procedure on server A to call a
> stored procedure on Server B. I have my stored procedures in place. I
have
> used Linked servers on server A to define Server B so, that server A can
> talk to server B. However I seem to have missed something. This is the
> message that I get:
> Could not find stored procedure 'sp_tt_load'.(42000,2812)
> Procedure(sp_tt_dumpandload_for_standby).
>
> sp_tt_dumpandload_for_standby (server A) does a database dump and then
calls
> sp_tt_load ( server B). Server B will load the dump onto it's standby
> database.
> TIA for all the help.
> Red
>
|||Thanks Mary, for the advise.
Actually, I found the error in my linked server configuration.
I had defined it using 'other data source' of Microsoft OLE DB provider for
SQL Server.
I should have used 'SQL Server' and then name the server.
Once I changed this, then I was able to make the stored procedure call,
which looks like this:
exec SERVERB...sp_tt_db_load
"Mary Bray" <reply@.tonewsgroup.com.NOSPAMPLEASE> wrote in message
news:eVuQWIDnEHA.2680@.TK2MSFTNGP15.phx.gbl...
> make sure you are using the full name to call the proc including the owner
> name eg:
> ServerB.databasename.dbo.sp_tt_load
> "Red" <RedWolf_56@.yahoo.com> wrote in message
> news:%23D2fn0CnEHA.2140@.TK2MSFTNGP11.phx.gbl...
> have
> calls
>
|||Lookup four part naming conventions which should help.
[server].[catalog].[owner].[object]
Adrian
Red wrote:

> Hi all,
> I want to be able to have a stored procedure on server A to call a
> stored procedure on Server B. I have my stored procedures in place. I have
> used Linked servers on server A to define Server B so, that server A can
> talk to server B. However I seem to have missed something. This is the
> message that I get:
> Could not find stored procedure 'sp_tt_load'.(42000,2812)
> Procedure(sp_tt_dumpandload_for_standby).
>
> sp_tt_dumpandload_for_standby (server A) does a database dump and then calls
> sp_tt_load ( server B). Server B will load the dump onto it's standby
> database.
> TIA for all the help.
> Red
>
sql

remote system call?

Hi all,
I want to be able to have a stored procedure on server A to call a
stored procedure on Server B. I have my stored procedures in place. I have
used Linked servers on server A to define Server B so, that server A can
talk to server B. However I seem to have missed something. This is the
message that I get:
Could not find stored procedure 'sp_tt_load'.(42000,2812)
Procedure(sp_tt_dumpandload_for_standby).
sp_tt_dumpandload_for_standby (server A) does a database dump and then calls
sp_tt_load ( server B). Server B will load the dump onto it's standby
database.
TIA for all the help.
Redmake sure you are using the full name to call the proc including the owner
name eg:
ServerB.databasename.dbo.sp_tt_load
"Red" <RedWolf_56@.yahoo.com> wrote in message
news:%23D2fn0CnEHA.2140@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I want to be able to have a stored procedure on server A to call a
> stored procedure on Server B. I have my stored procedures in place. I
have
> used Linked servers on server A to define Server B so, that server A can
> talk to server B. However I seem to have missed something. This is the
> message that I get:
> Could not find stored procedure 'sp_tt_load'.(42000,2812)
> Procedure(sp_tt_dumpandload_for_standby).
>
> sp_tt_dumpandload_for_standby (server A) does a database dump and then
calls
> sp_tt_load ( server B). Server B will load the dump onto it's standby
> database.
> TIA for all the help.
> Red
>|||Thanks Mary, for the advise.
Actually, I found the error in my linked server configuration.
I had defined it using 'other data source' of Microsoft OLE DB provider for
SQL Server.
I should have used 'SQL Server' and then name the server.
Once I changed this, then I was able to make the stored procedure call,
which looks like this:
exec SERVERB...sp_tt_db_load
"Mary Bray" <reply@.tonewsgroup.com.NOSPAMPLEASE> wrote in message
news:eVuQWIDnEHA.2680@.TK2MSFTNGP15.phx.gbl...
> make sure you are using the full name to call the proc including the owner
> name eg:
> ServerB.databasename.dbo.sp_tt_load
> "Red" <RedWolf_56@.yahoo.com> wrote in message
> news:%23D2fn0CnEHA.2140@.TK2MSFTNGP11.phx.gbl...
> > Hi all,
> > I want to be able to have a stored procedure on server A to call a
> > stored procedure on Server B. I have my stored procedures in place. I
> have
> > used Linked servers on server A to define Server B so, that server A can
> > talk to server B. However I seem to have missed something. This is the
> > message that I get:
> > Could not find stored procedure 'sp_tt_load'.(42000,2812)
> > Procedure(sp_tt_dumpandload_for_standby).
> >
> >
> > sp_tt_dumpandload_for_standby (server A) does a database dump and then
> calls
> > sp_tt_load ( server B). Server B will load the dump onto it's standby
> > database.
> >
> > TIA for all the help.
> >
> > Red
> >
> >
>|||Lookup four part naming conventions which should help.
[server].[catalog].[owner].[object]
Adrian
Red wrote:
> Hi all,
> I want to be able to have a stored procedure on server A to call a
> stored procedure on Server B. I have my stored procedures in place. I have
> used Linked servers on server A to define Server B so, that server A can
> talk to server B. However I seem to have missed something. This is the
> message that I get:
> Could not find stored procedure 'sp_tt_load'.(42000,2812)
> Procedure(sp_tt_dumpandload_for_standby).
>
> sp_tt_dumpandload_for_standby (server A) does a database dump and then calls
> sp_tt_load ( server B). Server B will load the dump onto it's standby
> database.
> TIA for all the help.
> Red
>

Remote Stored Procedures

I have a stored proc on a server called lax-dd and the
stored proc name is test. Both servers are in the same
domain and the sa passwords are the same on both servers.
I in query analyzer run exec lax-dd.dbname.dbo.test.
I get errors it doesn't understand the dash(-) in the
servername. I've tried placing the string in quot and
have problems finding the store proc...
Any ideas?
When you get errors and are having problems troubleshooting
those, it's always a good idea to post the error numbers and
the exact error messages.
Anyway...I'm guessing the problem is that you need to set
up a linked server to the other remote SQL Server. See the
books online topic: OLE DB Provider for SQL Server
You can also find more information in books online under
sp_addlinkedserver
-Sue
On Thu, 24 Jun 2004 13:26:08 -0700, "Remote server"
<jay_bukstein@.hotmail.com> wrote:

>I have a stored proc on a server called lax-dd and the
>stored proc name is test. Both servers are in the same
>domain and the sa passwords are the same on both servers.
>I in query analyzer run exec lax-dd.dbname.dbo.test.
>I get errors it doesn't understand the dash(-) in the
>servername. I've tried placing the string in quot and
>have problems finding the store proc...
>Any ideas?
|||No, in SQL 2000, This is Remote Server, not a linked
server, it looks like the difference is a remote server
is only for other MS SQL server's using native SQL
Drivers where link servers a OLE DB providers, and there
is no error number associated with it.
I also had no trouble define the Remote server, I just
can't execute a remote storder Procedure using the
following Query analyzer command
exec lax-dd.dbname.dbo.test.
Any other ideas.

>--Original Message--
>When you get errors and are having problems
troubleshooting
>those, it's always a good idea to post the error numbers
and
>the exact error messages.
>Anyway...I'm guessing the problem is that you need to
set
>up a linked server to the other remote SQL Server. See
the[vbcol=seagreen]
>books online topic: OLE DB Provider for SQL Server
>You can also find more information in books online under
>sp_addlinkedserver
>-Sue
>On Thu, 24 Jun 2004 13:26:08 -0700, "Remote server"
><jay_bukstein@.hotmail.com> wrote:
servers.
>.
>
|||One of the biggest differences is that remote servers are for
backwards compatability. You should be using linked servers if this is
to access a SQL Server that is higher than version 6.5.
If there is no error message and no error number, how do you know you
are getting errors? If you actually do have error messages, please
post the exact error message.
And what steps did you take to set this up as a remote server?
-Sue
On Fri, 25 Jun 2004 08:54:59 -0700, "Jay Bukstein"
<jay_bukstein@.hotmail.com> wrote:
[vbcol=seagreen]
>No, in SQL 2000, This is Remote Server, not a linked
>server, it looks like the difference is a remote server
>is only for other MS SQL server's using native SQL
>Drivers where link servers a OLE DB providers, and there
>is no error number associated with it.
>I also had no trouble define the Remote server, I just
>can't execute a remote storder Procedure using the
>following Query analyzer command
>exec lax-dd.dbname.dbo.test.
>Any other ideas.
>
>troubleshooting
>and
>set
>the
>servers.
|||In a query window I receive a message that it can't find
the store procedure. I can run the store procedure in
query analyzer when logged in directly on that server.
I've even tryed running system store procedures like
sp_helpdb, or sp_who and get the same message 'can't find
stored procedure.'
Too also mention that I can't create these as Link
servers because I have SQL replication running.
Replication creates the other servers as remote servers.

>--Original Message--
>One of the biggest differences is that remote servers
are for
>backwards compatability. You should be using linked
servers if this is
>to access a SQL Server that is higher than version 6.5.
>If there is no error message and no error number, how do
you know you
>are getting errors? If you actually do have error
messages, please
>post the exact error message.
>And what steps did you take to set this up as a remote
server?[vbcol=seagreen]
>-Sue
>On Fri, 25 Jun 2004 08:54:59 -0700, "Jay Bukstein"
><jay_bukstein@.hotmail.com> wrote:
there[vbcol=seagreen]
numbers[vbcol=seagreen]
under[vbcol=seagreen]
the[vbcol=seagreen]
same[vbcol=seagreen]
and
>.
>
|||I had the problem myself.
Call it through
EXEC [<ServerName>].[<DBName>].[<Owner>].[<ObjectName>]
(i.e., add [ ] on the server name).
After a whole day of testing this trivial solution came up. duh
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.

Remote Stored Procedures

I have a stored proc on a server called lax-dd and the
stored proc name is test. Both servers are in the same
domain and the sa passwords are the same on both servers.
I in query analyzer run exec lax-dd.dbname.dbo.test.
I get errors it doesn't understand the dash(-) in the
servername. I've tried placing the string in quot and
have problems finding the store proc...
Any ideas?When you get errors and are having problems troubleshooting
those, it's always a good idea to post the error numbers and
the exact error messages.
Anyway...I'm guessing the problem is that you need to set
up a linked server to the other remote SQL Server. See the
books online topic: OLE DB Provider for SQL Server
You can also find more information in books online under
sp_addlinkedserver
-Sue
On Thu, 24 Jun 2004 13:26:08 -0700, "Remote server"
<jay_bukstein@.hotmail.com> wrote:

>I have a stored proc on a server called lax-dd and the
>stored proc name is test. Both servers are in the same
>domain and the sa passwords are the same on both servers.
>I in query analyzer run exec lax-dd.dbname.dbo.test.
>I get errors it doesn't understand the dash(-) in the
>servername. I've tried placing the string in quot and
>have problems finding the store proc...
>Any ideas?|||No, in SQL 2000, This is Remote Server, not a linked
server, it looks like the difference is a remote server
is only for other MS SQL server's using native SQL
Drivers where link servers a OLE DB providers, and there
is no error number associated with it.
I also had no trouble define the Remote server, I just
can't execute a remote storder Procedure using the
following Query analyzer command
exec lax-dd.dbname.dbo.test.
Any other ideas.

>--Original Message--
>When you get errors and are having problems
troubleshooting
>those, it's always a good idea to post the error numbers
and
>the exact error messages.
>Anyway...I'm guessing the problem is that you need to
set
>up a linked server to the other remote SQL Server. See
the
>books online topic: OLE DB Provider for SQL Server
>You can also find more information in books online under
>sp_addlinkedserver
>-Sue
>On Thu, 24 Jun 2004 13:26:08 -0700, "Remote server"
><jay_bukstein@.hotmail.com> wrote:
>
servers.[vbcol=seagreen]
>.
>|||One of the biggest differences is that remote servers are for
backwards compatability. You should be using linked servers if this is
to access a SQL Server that is higher than version 6.5.
If there is no error message and no error number, how do you know you
are getting errors? If you actually do have error messages, please
post the exact error message.
And what steps did you take to set this up as a remote server?
-Sue
On Fri, 25 Jun 2004 08:54:59 -0700, "Jay Bukstein"
<jay_bukstein@.hotmail.com> wrote:
[vbcol=seagreen]
>No, in SQL 2000, This is Remote Server, not a linked
>server, it looks like the difference is a remote server
>is only for other MS SQL server's using native SQL
>Drivers where link servers a OLE DB providers, and there
>is no error number associated with it.
>I also had no trouble define the Remote server, I just
>can't execute a remote storder Procedure using the
>following Query analyzer command
>exec lax-dd.dbname.dbo.test.
>Any other ideas.
>
>
>troubleshooting
>and
>set
>the
>servers.|||In a query window I receive a message that it can't find
the store procedure. I can run the store procedure in
query analyzer when logged in directly on that server.
I've even tryed running system store procedures like
sp_helpdb, or sp_who and get the same message 'can't find
stored procedure.'
Too also mention that I can't create these as Link
servers because I have SQL replication running.
Replication creates the other servers as remote servers.

>--Original Message--
>One of the biggest differences is that remote servers
are for
>backwards compatability. You should be using linked
servers if this is
>to access a SQL Server that is higher than version 6.5.
>If there is no error message and no error number, how do
you know you
>are getting errors? If you actually do have error
messages, please
>post the exact error message.
>And what steps did you take to set this up as a remote
server?
>-Sue
>On Fri, 25 Jun 2004 08:54:59 -0700, "Jay Bukstein"
><jay_bukstein@.hotmail.com> wrote:
>
there[vbcol=seagreen]
numbers[vbcol=seagreen]
under[vbcol=seagreen]
the[vbcol=seagreen]
same[vbcol=seagreen]
and[vbcol=seagreen]
>.
>|||I had the problem myself.
Call it through
EXEC [<ServerName>].[<DBName>].[<Owner>].[<ObjectName>]
(i.e., add [ ] on the server name).
After a whole day of testing this trivial solution came up. duh
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.

Remote Stored Procedure exec from variable

Hello Folks,

I have a server that is linked to several other servers. I have been able to successfully execute a procedure manually to each from the common server by executing this:

exec server1.dbname.owner.procedure
go
exec server2.dbname.owner.procedure
go
exec server3.dbname.owner.procedure
go

While this is ok, I'd like to wrap this in a loop to execute the procedure but switch out the server name in each iteration (something like this):

while @.variable is not null
begin
select @.server = 'change name in loop'
select @.str = @.server+'.dbname.owner.procedure'
exec @.str
end

This unfortunately does not work. The execute is acting like the server portion of the name does not exist (defaulting to local). I have attempted to use the AT SERVERNAME syntax in a similar fashion and been unsuccessful.

Is there some way I could dynamically create the four part name and execute it?

Any assistance would be greatly appreciated.

Thanks, Mark

DECLARE @.server nvarchar(128)

DECLARE @.cmd nvarchar(1000)

SET @.server = 'MyServer'

SET @.cmd = 'EXEC ' + @.server + '.MyDatabase.MySchema.MySproc'

EXEC (@.cmd)

Remote stored procedure

I am trying to run a stored procedure on a remote server and insert the
output into a table on the local machine.
The two servers are linked and I am using the code as follows
BEGIN DISTRIBUTED TRAN
insert into ServerA.Database.dbo.drv_total
exec ServerB.master.dbo.xp_fixeddrives
COMMIT TRAN
The MSDTC is started on both machines.
I am getting this error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
Any thoughts?
TIA,
nivek
Hi Nivek,
Are you running on Windows 2003? If so, the following link may apply to you:
http://support.microsoft.com/default...b;en-us;329332
"The problem occurs because Microsoft Distributed Transaction Coordinator
(MS DTC) is not configured for network access. By default, the network access
settings of MS DTC are disabled on new installations of SQL Server 2000 on
computers running Windows Server 2003, Enterprise Edition. "
Best Regards,
Joe Sack
Author of "SQL Server 2000 Fast Answers..."
http://www.JoeSack.com
"nivek" wrote:

> I am trying to run a stored procedure on a remote server and insert the
> output into a table on the local machine.
> The two servers are linked and I am using the code as follows
> BEGIN DISTRIBUTED TRAN
> insert into ServerA.Database.dbo.drv_total
> exec ServerB.master.dbo.xp_fixeddrives
> COMMIT TRAN
> The MSDTC is started on both machines.
> I am getting this error:
> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB provider 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
>
> Any thoughts?
>
> TIA,
> nivek
>
>
sql

Remote stored procedure

I am trying to run a stored procedure on a remote server and insert the
output into a table on the local machine.
The two servers are linked and I am using the code as follows
BEGIN DISTRIBUTED TRAN
insert into ServerA.Database.dbo.drv_total
exec ServerB.master.dbo.xp_fixeddrives
COMMIT TRAN
The MSDTC is started on both machines.
I am getting this error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
Any thoughts?
TIA,
nivekHi Nivek,
Are you running on Windows 2003? If so, the following link may apply to you:
http://support.microsoft.com/default.aspx?scid=kb;en-us;329332
"The problem occurs because Microsoft Distributed Transaction Coordinator
(MS DTC) is not configured for network access. By default, the network access
settings of MS DTC are disabled on new installations of SQL Server 2000 on
computers running Windows Server 2003, Enterprise Edition. "
Best Regards,
Joe Sack
Author of "SQL Server 2000 Fast Answers..."
http://www.JoeSack.com
"nivek" wrote:
> I am trying to run a stored procedure on a remote server and insert the
> output into a table on the local machine.
> The two servers are linked and I am using the code as follows
> BEGIN DISTRIBUTED TRAN
> insert into ServerA.Database.dbo.drv_total
> exec ServerB.master.dbo.xp_fixeddrives
> COMMIT TRAN
> The MSDTC is started on both machines.
> I am getting this error:
> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB provider 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
>
> Any thoughts?
>
> TIA,
> nivek
>
>

Remote Stored Proc Call

I'm calling this from another sql server...
I created a linked server... and want to restore database backups on the other box...
The restore script runs fine when ran locally but fails with the message below when calling it remotely

Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.

CREATE PROCEDURE usp_restore_database_backups AS

RESTORE DATABASE BesMgmt
FROM DISK = 'D:\MSSQL\BACKUP\BesMgmt\BesMgmt_backup_device.bak '
WITH
--DBO_ONLY,
REPLACE,
--STANDBY = 'D:\MSSQL\Data\BesMgmt\undo_BesMgmt.ldf',
MOVE 'BesMgmt_data' TO 'D:\MSSQL\Data\BesMgmt.mdf',
MOVE 'BesMgmt_log' TO 'D:\MSSQL\Data\BesMgmt.ldf'

WAITFOR DELAY '00:00:05'

EXEC sp_dboption 'BesMgmt', 'single user', true
GO

I have set it to read only dbo only ... single user... still get the same message...
does anyone have any suggestions...try executing...

ALTER DATABASE <MyDB> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

or

ALTER DATABASE <MyDB> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

before the restore statement and drop the sp_dboption.|||thanks Thrasymachus
I'm still getting the same error:
Executed as user: NFCU\sqlsvc. RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013) Exclusive access could not be obtained because the database is in use. [SQLSTATE 42000] (Error 3101). The step failed.|||post the script you are currently using and try running sp_who when it fails to see what connections are in use. Also when you execute this script in the QA are you connected to the database you are trying to restore? The database selected from the dropdown should not be the database you are trying to restore. If everything is OK with your script I suspect this is the case.|||The error does not tally with this but just belt and braces.

If you run
EXEC sp_helpserver

do you see RPC & RPC out in the status field?|||When I set the database manually into single user mode I get a different error when trying to restore the database... this is the error that comes from when the database is in single user mode....
"Executed as user: NFCU\sqlsvc. Cannot open database requested in login 'BESMgmt'. Login fails. [SQLSTATE 42000] (Error 4060). The step failed."

CREATE PROCEDURE usp_restore_database_backups AS

/*
declare @.x varchar(255)
select @.x = @.x + ' kill ' + convert(varchar(5), spid)
from master.dbo.sysprocesses
where dbid = db_id ('BesMgmt')
exec (@.x)
*/

ALTER DATABASE BesMgmt SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE BesMgmt
FROM DISK = 'D:\MSSQL\BACKUP\BesMgmt\BesMgmt_backup_device.bak '
WITH
--DBO_ONLY,
REPLACE,
--STANDBY = 'D:\MSSQL\Data\BesMgmt\undo_BesMgmt.ldf',
MOVE 'BesMgmt_data' TO 'D:\MSSQL\Data\BesMgmt.mdf',
MOVE 'BesMgmt_log' TO 'D:\MSSQL\Data\BesMgmt.ldf'

WAITFOR DELAY '00:00:05'

--EXEC sp_dboption 'BesMgmt', 'single user', true
GO|||The command is within a job...
the sql agent service is ran under a domain user acount...

Remote SQL Debug: Server cannot connect to the debugger on my mach

Hello,
I am trying to debug a stored procedure on remote server. The debugger
starts and passes through without stopping.
No error messages are generated on the client.
On the server the following error appears in the Application Log:
Event Type: Error
Event Source: SQLDebugging98
Event Category: None
Event ID: 1
Date: 10/7/2005
Time: 4:41:47 PM
User: N/A
Computer: <server>
Description:
SQL Server is running as '<domain account>' and cannot connect to the
debugger on machine '<my machine>' (error = 0x80070005 Access is denied. ).
Use one of the following options to fix this error. 1) Run SQL Server as
"Local System", as a domain account, or as a local account with identical
usernames and passwords on both machine '<server>' and '<my machine>'. 2)
Verify that machine '<server>' can open files on machine '<my machine>'.
Debugging disabled for connection 55.
Server is Windows 2003 Standard running SQL 2000 SP4 with .NET remote
debugging support.
Client is Windows XP Pro SP2 running SQL 2000 SP4 and .NET 1.1 SP1.
Windows Firewall is disabled on both machines.
All DCOM permissions seems to be set OK on the server. I am a local admin on
the server and sys admin on SQL server and I can run sp_sdidebug without any
problems.
I can debug SQL on my local machine using Query Analyzer and VS.NET without
any problems. I can also debug SQL using Query Analyzer on the server if I
log there with my account.
What could prevent SQL debugger from connecting to my workstation? I tried
to add that SQL service account to my local admin group, but it did not help
.
I am not sure what is required to "2) Verify that machine '<server>' can ope
n
files on machine '<my machine>'." Does it mean that I have to trust my serve
r
machine? How can I do that?
What else could be wrong?Hello Serge,
You wrote in conference microsoft.public.sqlserver.programming on Fri, 7
Oct 2005 17:25:03 -0700:
SM> I am trying to debug a stored procedure on remote server. The debugger
SM> starts and passes through without stopping.
http://support.microsoft.com/defaul...kb;en-us;841249
and especially
http://support.microsoft.com/defaul...kb;en-us;839280
Vadm Rapp|||After spending few hours troubleshooting my local COM+ security and killing
my registry :) I found the settings that make remote SQL debugging work for
SQL Analyzer and VS.NET.
All these settings apply to the Default COM Security for Windows XP
Workstation from which debugging is initiated.
Access Permissions:
-- Limits:
* ANONYMOUS LOGON - Allow All (Required to debug from SQL Analyzer)
* Everyone - Allow All (Required to debug from VS.NET)
Launch and Activation Permissions:
Limits (add)
* ANONYMOUS LOGON - Allow All (Required to debug from VS.NET)
Default (add)
* ANONYMOUS LOGON - Allow All (Required to debug from VS.NET)
Huh!
Of course all settings on the server must be done first as described in
several knowlege articles. If firewall is anabled, then it should be adresse
d
as well.
"Serge Matsevilo" wrote:

> Hello,
> I am trying to debug a stored procedure on remote server. The debugger
> starts and passes through without stopping.
> No error messages are generated on the client.
> On the server the following error appears in the Application Log:
> Event Type: Error
> Event Source: SQLDebugging98
> Event Category: None
> Event ID: 1
> Date: 10/7/2005
> Time: 4:41:47 PM
> User: N/A
> Computer: <server>
> Description:
> SQL Server is running as '<domain account>' and cannot connect to the
> debugger on machine '<my machine>' (error = 0x80070005 Access is denied. )
.
> Use one of the following options to fix this error. 1) Run SQL Server as
> "Local System", as a domain account, or as a local account with identical
> usernames and passwords on both machine '<server>' and '<my machine>'. 2)
> Verify that machine '<server>' can open files on machine '<my machine>'.
> Debugging disabled for connection 55.
> Server is Windows 2003 Standard running SQL 2000 SP4 with .NET remote
> debugging support.
> Client is Windows XP Pro SP2 running SQL 2000 SP4 and .NET 1.1 SP1.
> Windows Firewall is disabled on both machines.
> All DCOM permissions seems to be set OK on the server. I am a local admin
on
> the server and sys admin on SQL server and I can run sp_sdidebug without a
ny
> problems.
> I can debug SQL on my local machine using Query Analyzer and VS.NET withou
t
> any problems. I can also debug SQL using Query Analyzer on the server if I
> log there with my account.
> What could prevent SQL debugger from connecting to my workstation? I tried
> to add that SQL service account to my local admin group, but it did not he
lp.
> I am not sure what is required to "2) Verify that machine '<server>' can o
pen
> files on machine '<my machine>'." Does it mean that I have to trust my ser
ver
> machine? How can I do that?
> What else could be wrong?

Remote SP debugging from VS 2003: error

I 'm still getting the error:
"Cannot debug stored procedures because the SQL Server database is not setup
correctly or user does not have permission to execute master.sp_sdidebug."
I have configured DCOM on the remote sql server according tothe MSDN
article.
I have added SQLDebugger account to default permissions in dcom.
I have also given full access to Master DB for the user
SQLDebugger and all stored procedures full access.
Does anybody have any idea why is it still giving the above error.
TIA
TSHi
Did you check out
http://support.microsoft.com/default.aspx?scid=kb;en-us;817178
John
"test" wrote:
> I 'm still getting the error:
> "Cannot debug stored procedures because the SQL Server database is not setup
> correctly or user does not have permission to execute master.sp_sdidebug."
> I have configured DCOM on the remote sql server according tothe MSDN
> article.
> I have added SQLDebugger account to default permissions in dcom.
> I have also given full access to Master DB for the user
> SQLDebugger and all stored procedures full access.
> Does anybody have any idea why is it still giving the above error.
> TIA
> TS
>
>|||Hi John,
Thanks for the reply.
According to the support article
I ran EXECUTE sp_sdidebug 'LEGACY_ON'
on the remote DB server on QA.
I am afraid it still gives me the same error.
In the system log file and the Application log file of the remote server
following errors were recorded respectively.
DCOM was unable to communicate with the computer WS06 using any of the
configured protocols.
Error: 504, Severity: 16, State: 1
Unable to connect to debugger on MELB (Error = 0x800706ba). Ensure that
client-side components, such as SQLDBREG.EXE, are installed and registered
on WS06. Debugging disabled for connection 58.
JFI: SQLDBREG.EXE is found on client machine and executed too.
thanks
TS
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:8A208FDB-FBC4-43C0-A94F-BC2792FCE9EB@.microsoft.com...
> Hi
> Did you check out
> http://support.microsoft.com/default.aspx?scid=kb;en-us;817178
> John
> "test" wrote:
>> I 'm still getting the error:
>> "Cannot debug stored procedures because the SQL Server database is not
>> setup
>> correctly or user does not have permission to execute
>> master.sp_sdidebug."
>> I have configured DCOM on the remote sql server according tothe MSDN
>> article.
>> I have added SQLDebugger account to default permissions in dcom.
>> I have also given full access to Master DB for the user
>> SQLDebugger and all stored procedures full access.
>> Does anybody have any idea why is it still giving the above error.
>> TIA
>> TS
>>
>>|||Hi
This looks like:
http://support.microsoft.com/default.aspx?scid=kb;en-us;839280
Failing that you may want to check RPC is running
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servtools_5cfm.asp
and if you can use Query Analyser for debugging from this machine and others
as well as on the server.
John
"test" wrote:
> Hi John,
> Thanks for the reply.
> According to the support article
> I ran EXECUTE sp_sdidebug 'LEGACY_ON'
> on the remote DB server on QA.
> I am afraid it still gives me the same error.
>
> In the system log file and the Application log file of the remote server
> following errors were recorded respectively.
> DCOM was unable to communicate with the computer WS06 using any of the
> configured protocols.
> Error: 504, Severity: 16, State: 1
> Unable to connect to debugger on MELB (Error = 0x800706ba). Ensure that
> client-side components, such as SQLDBREG.EXE, are installed and registered
> on WS06. Debugging disabled for connection 58.
> JFI: SQLDBREG.EXE is found on client machine and executed too.
>
> thanks
> TS
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:8A208FDB-FBC4-43C0-A94F-BC2792FCE9EB@.microsoft.com...
> > Hi
> >
> > Did you check out
> >
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;817178
> >
> > John
> >
> > "test" wrote:
> >
> >> I 'm still getting the error:
> >>
> >> "Cannot debug stored procedures because the SQL Server database is not
> >> setup
> >> correctly or user does not have permission to execute
> >> master.sp_sdidebug."
> >>
> >> I have configured DCOM on the remote sql server according tothe MSDN
> >> article.
> >> I have added SQLDebugger account to default permissions in dcom.
> >>
> >> I have also given full access to Master DB for the user
> >> SQLDebugger and all stored procedures full access.
> >>
> >> Does anybody have any idea why is it still giving the above error.
> >>
> >> TIA
> >> TS
> >>
> >>
> >>
> >>
>
>|||John,
By any chance can the problem that I am getting is due to the fact that we
are on two different domains, i.e. sql server and my working machine?
thanks
TS
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:58CD11E4-5320-4107-9384-15ECECC78C3D@.microsoft.com...
> Hi
> This looks like:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;839280
> Failing that you may want to check RPC is running
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servtools_5cfm.asp
> and if you can use Query Analyser for debugging from this machine and
> others
> as well as on the server.
> John
> "test" wrote:
>> Hi John,
>> Thanks for the reply.
>> According to the support article
>> I ran EXECUTE sp_sdidebug 'LEGACY_ON'
>> on the remote DB server on QA.
>> I am afraid it still gives me the same error.
>>
>> In the system log file and the Application log file of the remote server
>> following errors were recorded respectively.
>> DCOM was unable to communicate with the computer WS06 using any of the
>> configured protocols.
>> Error: 504, Severity: 16, State: 1
>> Unable to connect to debugger on MELB (Error = 0x800706ba). Ensure that
>> client-side components, such as SQLDBREG.EXE, are installed and
>> registered
>> on WS06. Debugging disabled for connection 58.
>> JFI: SQLDBREG.EXE is found on client machine and executed too.
>>
>> thanks
>> TS
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:8A208FDB-FBC4-43C0-A94F-BC2792FCE9EB@.microsoft.com...
>> > Hi
>> >
>> > Did you check out
>> >
>> > http://support.microsoft.com/default.aspx?scid=kb;en-us;817178
>> >
>> > John
>> >
>> > "test" wrote:
>> >
>> >> I 'm still getting the error:
>> >>
>> >> "Cannot debug stored procedures because the SQL Server database is not
>> >> setup
>> >> correctly or user does not have permission to execute
>> >> master.sp_sdidebug."
>> >>
>> >> I have configured DCOM on the remote sql server according tothe MSDN
>> >> article.
>> >> I have added SQLDebugger account to default permissions in dcom.
>> >>
>> >> I have also given full access to Master DB for the user
>> >> SQLDebugger and all stored procedures full access.
>> >>
>> >> Does anybody have any idea why is it still giving the above error.
>> >>
>> >> TIA
>> >> TS
>> >>
>> >>
>> >>
>> >>
>>|||Hi
I don't think that should be a problem expecially if you have a trusted
relationship between the two domains (which I assume you have!). The second
article points to a post SP3a hotfix. As you don't give you version numbe I
assume that you are still on SP3a, so you could try loading service pack 4 on
both client and server.
Did you try debugging from Query Analyser?
John
"test" wrote:
> John,
> By any chance can the problem that I am getting is due to the fact that we
> are on two different domains, i.e. sql server and my working machine?
> thanks
> TS
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:58CD11E4-5320-4107-9384-15ECECC78C3D@.microsoft.com...
> > Hi
> >
> > This looks like:
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;839280
> >
> > Failing that you may want to check RPC is running
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servtools_5cfm.asp
> >
> > and if you can use Query Analyser for debugging from this machine and
> > others
> > as well as on the server.
> >
> > John
> >
> > "test" wrote:
> >
> >> Hi John,
> >>
> >> Thanks for the reply.
> >>
> >> According to the support article
> >> I ran EXECUTE sp_sdidebug 'LEGACY_ON'
> >> on the remote DB server on QA.
> >> I am afraid it still gives me the same error.
> >>
> >>
> >> In the system log file and the Application log file of the remote server
> >> following errors were recorded respectively.
> >>
> >> DCOM was unable to communicate with the computer WS06 using any of the
> >> configured protocols.
> >>
> >> Error: 504, Severity: 16, State: 1
> >> Unable to connect to debugger on MELB (Error = 0x800706ba). Ensure that
> >> client-side components, such as SQLDBREG.EXE, are installed and
> >> registered
> >> on WS06. Debugging disabled for connection 58.
> >>
> >> JFI: SQLDBREG.EXE is found on client machine and executed too.
> >>
> >>
> >> thanks
> >> TS
> >>
> >>
> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> news:8A208FDB-FBC4-43C0-A94F-BC2792FCE9EB@.microsoft.com...
> >> > Hi
> >> >
> >> > Did you check out
> >> >
> >> > http://support.microsoft.com/default.aspx?scid=kb;en-us;817178
> >> >
> >> > John
> >> >
> >> > "test" wrote:
> >> >
> >> >> I 'm still getting the error:
> >> >>
> >> >> "Cannot debug stored procedures because the SQL Server database is not
> >> >> setup
> >> >> correctly or user does not have permission to execute
> >> >> master.sp_sdidebug."
> >> >>
> >> >> I have configured DCOM on the remote sql server according tothe MSDN
> >> >> article.
> >> >> I have added SQLDebugger account to default permissions in dcom.
> >> >>
> >> >> I have also given full access to Master DB for the user
> >> >> SQLDebugger and all stored procedures full access.
> >> >>
> >> >> Does anybody have any idea why is it still giving the above error.
> >> >>
> >> >> TIA
> >> >> TS
> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

Remote server connection

We have a database on a shared SQL Server stored on our ISP's server. I can
connect to this via Enterprise Manager from our office, but when I try the
same from Enterprise Manager on my PC at home I get the following message:

A connection could not be established to MFSERVER.

Reason: SQL Server is not running or access denied.

Any ideas?

ThanksHi

There is probably a firewall blocking port 1433.
Check with your network administrators.

Regards
----------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@.epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Mark Fisher" <mfisher@.uku.co.uk> wrote in message
news:%wGud.1214$uv1.177@.newsfe1-gui.ntli.net...
> We have a database on a shared SQL Server stored on our ISP's server. I
can
> connect to this via Enterprise Manager from our office, but when I try the
> same from Enterprise Manager on my PC at home I get the following message:
> A connection could not be established to MFSERVER.
> Reason: SQL Server is not running or access denied.
> Any ideas?
> Thanks

Remote Proc Restore Fails

I have set up a job to implement simple Log Shipping for SQL Server
2000. I have added a link on my primary server so I can execute a
remote stored proc on the standby server from the SQL Agent job on the
primary. The remote procedure restores the backup copied over from the
primary server.
When the remote procedure executes, the job reports success, but the
standby server is left in the Loading state or Loading/Suspect on some
executions. The procedure is:
RESTORE DATABASE MPR
FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
STATS = 5
When I execute the procedure on the standby using SQL Query Analyzer
after a failure, it restores successfully! It only fails when it runs
from the job on the primary server. But the job history says it was
successful.
Can anyone help me troubleshoot this problem? I don't know where to
begin.
TerryI should mention that I have 12 other databases using similar
procedures to implement log shipping and I have never seen this
problem before on any of them. The only apparent difference is that
this DB is much bigger 15GB and uses multiple file groups.
dontsendmecrud@.hotmail.com (Terry) wrote in message news:<e2c86606.0407080535.11f95f63@.posting.google.com>...
> I have set up a job to implement simple Log Shipping for SQL Server
> 2000. I have added a link on my primary server so I can execute a
> remote stored proc on the standby server from the SQL Agent job on the
> primary. The remote procedure restores the backup copied over from the
> primary server.
> When the remote procedure executes, the job reports success, but the
> standby server is left in the Loading state or Loading/Suspect on some
> executions. The procedure is:
> RESTORE DATABASE MPR
> FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
> WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
> MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
> MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
> MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
> MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
> STATS = 5
> When I execute the procedure on the standby using SQL Query Analyzer
> after a failure, it restores successfully! It only fails when it runs
> from the job on the primary server. But the job history says it was
> successful.
> Can anyone help me troubleshoot this problem? I don't know where to
> begin.
> Terry|||Hi - I have exactly the same problem, with the standby database in a
state of loading after the database restore - rather than in read only.
I can run each step in the job manually and it works fine - only when
run from the remote server does it seem to fail.
Any help would be appreciated.
Thanks,
Serena.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi - Found the issue - the linked server has a query timeout connection
setting - this was set to 0 which means it uses the remote query timeout
setting in sp_configure - this was set to 10 minutes...hence the failures.
"Serena Barker" wrote:
> Hi - I have exactly the same problem, with the standby database in a
> state of loading after the database restore - rather than in read only.
> I can run each step in the job manually and it works fine - only when
> run from the remote server does it seem to fail.
> Any help would be appreciated.
> Thanks,
> Serena.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
>

Remote Proc Restore Fails

I have set up a job to implement simple Log Shipping for SQL Server
2000. I have added a link on my primary server so I can execute a
remote stored proc on the standby server from the SQL Agent job on the
primary. The remote procedure restores the backup copied over from the
primary server.
When the remote procedure executes, the job reports success, but the
standby server is left in the Loading state or Loading/Suspect on some
executions. The procedure is:
RESTORE DATABASE MPR
FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
STATS = 5
When I execute the procedure on the standby using SQL Query Analyzer
after a failure, it restores successfully! It only fails when it runs
from the job on the primary server. But the job history says it was
successful.
Can anyone help me troubleshoot this problem? I don't know where to
begin.
TerryI should mention that I have 12 other databases using similar
procedures to implement log shipping and I have never seen this
problem before on any of them. The only apparent difference is that
this DB is much bigger 15GB and uses multiple file groups.
dontsendmecrud@.hotmail.com (Terry) wrote in message news:<e2c86606.0407080535.11f95f63@.posti
ng.google.com>...
> I have set up a job to implement simple Log Shipping for SQL Server
> 2000. I have added a link on my primary server so I can execute a
> remote stored proc on the standby server from the SQL Agent job on the
> primary. The remote procedure restores the backup copied over from the
> primary server.
> When the remote procedure executes, the job reports success, but the
> standby server is left in the Loading state or Loading/Suspect on some
> executions. The procedure is:
> RESTORE DATABASE MPR
> FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
> WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
> MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
> MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
> MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
> MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
> STATS = 5
> When I execute the procedure on the standby using SQL Query Analyzer
> after a failure, it restores successfully! It only fails when it runs
> from the job on the primary server. But the job history says it was
> successful.
> Can anyone help me troubleshoot this problem? I don't know where to
> begin.
> Terry|||Hi - I have exactly the same problem, with the standby database in a
state of loading after the database restore - rather than in read only.
I can run each step in the job manually and it works fine - only when
run from the remote server does it seem to fail.
Any help would be appreciated.
Thanks,
Serena.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Hi - Found the issue - the linked server has a query timeout connection
setting - this was set to 0 which means it uses the remote query timeout
setting in sp_configure - this was set to 10 minutes...hence the failures.
"Serena Barker" wrote:

> Hi - I have exactly the same problem, with the standby database in a
> state of loading after the database restore - rather than in read only.
> I can run each step in the job manually and it works fine - only when
> run from the remote server does it seem to fail.
> Any help would be appreciated.
> Thanks,
> Serena.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>

Remote Proc Restore Fails

I have set up a job to implement simple Log Shipping for SQL Server
2000. I have added a link on my primary server so I can execute a
remote stored proc on the standby server from the SQL Agent job on the
primary. The remote procedure restores the backup copied over from the
primary server.
When the remote procedure executes, the job reports success, but the
standby server is left in the Loading state or Loading/Suspect on some
executions. The procedure is:
RESTORE DATABASE MPR
FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
STATS = 5
When I execute the procedure on the standby using SQL Query Analyzer
after a failure, it restores successfully! It only fails when it runs
from the job on the primary server. But the job history says it was
successful.
Can anyone help me troubleshoot this problem? I don't know where to
begin.
Terry
I should mention that I have 12 other databases using similar
procedures to implement log shipping and I have never seen this
problem before on any of them. The only apparent difference is that
this DB is much bigger 15GB and uses multiple file groups.
dontsendmecrud@.hotmail.com (Terry) wrote in message news:<e2c86606.0407080535.11f95f63@.posting.google. com>...
> I have set up a job to implement simple Log Shipping for SQL Server
> 2000. I have added a link on my primary server so I can execute a
> remote stored proc on the standby server from the SQL Agent job on the
> primary. The remote procedure restores the backup copied over from the
> primary server.
> When the remote procedure executes, the job reports success, but the
> standby server is left in the Loading state or Loading/Suspect on some
> executions. The procedure is:
> RESTORE DATABASE MPR
> FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
> WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
> MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
> MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
> MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
> MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
> STATS = 5
> When I execute the procedure on the standby using SQL Query Analyzer
> after a failure, it restores successfully! It only fails when it runs
> from the job on the primary server. But the job history says it was
> successful.
> Can anyone help me troubleshoot this problem? I don't know where to
> begin.
> Terry
|||Hi - I have exactly the same problem, with the standby database in a
state of loading after the database restore - rather than in read only.
I can run each step in the job manually and it works fine - only when
run from the remote server does it seem to fail.
Any help would be appreciated.
Thanks,
Serena.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi - Found the issue - the linked server has a query timeout connection
setting - this was set to 0 which means it uses the remote query timeout
setting in sp_configure - this was set to 10 minutes...hence the failures.
"Serena Barker" wrote:

> Hi - I have exactly the same problem, with the standby database in a
> state of loading after the database restore - rather than in read only.
> I can run each step in the job manually and it works fine - only when
> run from the remote server does it seem to fail.
> Any help would be appreciated.
> Thanks,
> Serena.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>

Remote Executiong - SSIS

All,

Is it possible to run the ssis package from a remote box?

The SSIS package will be stored in a sql server (open for suggestions on this) . We have a seperate box which has a 3rd party scheduler application. All our current scheduled jobs are ran from this box. we want the ssis packages also to be called from this box, as it will be easier to maintain and keep track of the jobs running.

So, we want the SSIS package to be called from this scheduler box. Any ideas?

Thanks

You can create a job without schedule on SQL box, then invoke this job at the time defined by your own scheduler (e.g. start osql.exe to run sp_start_job).

http://blogs.msdn.com/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx

|||

If I install the client tools for Sql Server 2005 in the scheduler box, will i be able to run the package from that box itself using DTEXEC?

The problem I see with sp_start_job is that, it looks it will just start the job and report a success as long as the job gets started. If the job fails during execution we will still not know anything about it and I cannot have a dependency of jobs based on that.

|||

Karunakaran wrote:

If I install the client tools for Sql Server 2005 in the scheduler box, will i be able to run the package from that box itself using DTEXEC?

Yes, but that box will require a SQL Server license.|||

Another question on the same lines, let me know if I need to post this in a seperate thread.

I write a console application referencing dts runtime classes, and I invoke the package from the console app. Now If I deploy this console application in a box where ssis is not installed, but .NET framework is installed will this work? or is it against the licensing terms?

Thanks
Karunakaran

|||

Karunakaran wrote:

Another question on the same lines, let me know if I need to post this in a seperate thread.

I write a console application referencing dts runtime classes, and I invoke the package from the console app. Now If I deploy this console application in a box where ssis is not installed, but .NET framework is installed will this work? or is it against the licensing terms?

Thanks
Karunakaran

No. SSIS runtime components are not redistributable. You'll need to install SSIS on that box, and that will require a license.|||Thanks for the clarifications, Phil.