|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
sp_testlinkedserverI have a SQL2k5 server that I am setting up a linked server to a SQL2k server and have been able to successfully query against tables on the SQL2k server and also the sp_testlinkedserver returns a 0. So, all is good. However, I want to simulate a scenario where the server is not available, so I went out and stopped the SQL2k server that am linking to, then ran sp_testlinkedserver. Well, instead of what I was expecting, which a return code of 1, I received the error message below and it behaves just like it did in SQL2k, just killed everything. No catching the error, just aborted the process. Is there a way to test for a server you are linking to being down(for whatever reason, maintenance, etc.) and be able to catch/trap the error? I was under the impression that sp_testlinkedserver would do the trick, but I am missing something. Thanks in advance. ================================================== OLE DB provider "SQLNCLI" for linked server "Test2kServer" returned message "Login timeout expired". OLE DB provider "SQLNCLI" for linked server "Test2kServer" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". Msg 5, Level 16, State 1, Line 0 Named Pipes Provider: Could not open a connection to SQL Server [5]. ================================================== Here is what I use:
DECLARE @r INT, @ServerName SYSNAME; SET @ServerName = 'MyLinkedServer'; BEGIN TRY EXEC @r = sys.sp_testlinkedserver @ServerName; END TRY BEGIN CATCH RAISERROR('Unable to connect to %s.', 11, 1, @ServerName); RETURN -1; END CATCH Show quote "Dan" <D**@discussions.microsoft.com> wrote in message news:C04E8073-D5DA-4C86-904E-B1BA9A6C303E@microsoft.com... > Has anyone been able to get a return code of 1 using this new proc in > SQL2k5? > > I have a SQL2k5 server that I am setting up a linked server to a SQL2k > server and have been able to successfully query against tables on the > SQL2k > server and also the sp_testlinkedserver returns a 0. So, all is good. > > However, I want to simulate a scenario where the server is not available, > so > I went out and stopped the SQL2k server that am linking to, then ran > sp_testlinkedserver. Well, instead of what I was expecting, which a > return > code of 1, I received the error message below and it behaves just like it > did > in SQL2k, just killed everything. No catching the error, just aborted the > process. > > Is there a way to test for a server you are linking to being down(for > whatever reason, maintenance, etc.) and be able to catch/trap the error? > > I was under the impression that sp_testlinkedserver would do the trick, > but > I am missing something. > > Thanks in advance. > > ================================================== > OLE DB provider "SQLNCLI" for linked server "Test2kServer" returned > message > "Login timeout expired". > OLE DB provider "SQLNCLI" for linked server "Test2kServer" returned > message > "An error has occurred while establishing a connection to the server. When > connecting to SQL Server 2005, this failure may be caused by the fact that > under the default settings SQL Server does not allow remote connections.". > Msg 5, Level 16, State 1, Line 0 > Named Pipes Provider: Could not open a connection to SQL Server [5]. > ================================================== That will definitely work and I didn't realize that 2k5 had the TRY/CATCH
since I am just starting to work with the new version and have been on 2k for what seems like forever. Thanks a ton. Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > Here is what I use: > > DECLARE @r INT, @ServerName SYSNAME; > SET @ServerName = 'MyLinkedServer'; > BEGIN TRY > EXEC @r = sys.sp_testlinkedserver @ServerName; > END TRY > BEGIN CATCH > RAISERROR('Unable to connect to %s.', 11, 1, @ServerName); > RETURN -1; > END CATCH > > > "Dan" <D**@discussions.microsoft.com> wrote in message > news:C04E8073-D5DA-4C86-904E-B1BA9A6C303E@microsoft.com... > > Has anyone been able to get a return code of 1 using this new proc in > > SQL2k5? > > > > I have a SQL2k5 server that I am setting up a linked server to a SQL2k > > server and have been able to successfully query against tables on the > > SQL2k > > server and also the sp_testlinkedserver returns a 0. So, all is good. > > > > However, I want to simulate a scenario where the server is not available, > > so > > I went out and stopped the SQL2k server that am linking to, then ran > > sp_testlinkedserver. Well, instead of what I was expecting, which a > > return > > code of 1, I received the error message below and it behaves just like it > > did > > in SQL2k, just killed everything. No catching the error, just aborted the > > process. > > > > Is there a way to test for a server you are linking to being down(for > > whatever reason, maintenance, etc.) and be able to catch/trap the error? > > > > I was under the impression that sp_testlinkedserver would do the trick, > > but > > I am missing something. > > > > Thanks in advance. > > > > ================================================== > > OLE DB provider "SQLNCLI" for linked server "Test2kServer" returned > > message > > "Login timeout expired". > > OLE DB provider "SQLNCLI" for linked server "Test2kServer" returned > > message > > "An error has occurred while establishing a connection to the server. When > > connecting to SQL Server 2005, this failure may be caused by the fact that > > under the default settings SQL Server does not allow remote connections.". > > Msg 5, Level 16, State 1, Line 0 > > Named Pipes Provider: Could not open a connection to SQL Server [5]. > > ================================================== > > > All I get with this procedure is:
Msg 178, Level 15, State 1, Line 8 A RETURN statement with a return value cannot be used in this context. Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:%23OucFhasGHA.372@TK2MSFTNGP06.phx.gbl... > Here is what I use: > > DECLARE @r INT, @ServerName SYSNAME; > SET @ServerName = 'MyLinkedServer'; > BEGIN TRY > EXEC @r = sys.sp_testlinkedserver @ServerName; > END TRY > BEGIN CATCH > RAISERROR('Unable to connect to %s.', 11, 1, @ServerName); > RETURN -1; > END CATCH > > > "Dan" <D**@discussions.microsoft.com> wrote in message > news:C04E8073-D5DA-4C86-904E-B1BA9A6C303E@microsoft.com... >> Has anyone been able to get a return code of 1 using this new proc in >> SQL2k5? >> >> I have a SQL2k5 server that I am setting up a linked server to a SQL2k >> server and have been able to successfully query against tables on the >> SQL2k >> server and also the sp_testlinkedserver returns a 0. So, all is good. >> >> However, I want to simulate a scenario where the server is not available, >> so >> I went out and stopped the SQL2k server that am linking to, then ran >> sp_testlinkedserver. Well, instead of what I was expecting, which a >> return >> code of 1, I received the error message below and it behaves just like it >> did >> in SQL2k, just killed everything. No catching the error, just aborted the >> process. >> >> Is there a way to test for a server you are linking to being down(for >> whatever reason, maintenance, etc.) and be able to catch/trap the error? >> >> I was under the impression that sp_testlinkedserver would do the trick, >> but >> I am missing something. >> >> Thanks in advance. >> >> ================================================== >> OLE DB provider "SQLNCLI" for linked server "Test2kServer" returned >> message >> "Login timeout expired". >> OLE DB provider "SQLNCLI" for linked server "Test2kServer" returned >> message >> "An error has occurred while establishing a connection to the server. When >> connecting to SQL Server 2005, this failure may be caused by the fact that >> under the default settings SQL Server does not allow remote connections.". >> Msg 5, Level 16, State 1, Line 0 >> Named Pipes Provider: Could not open a connection to SQL Server [5]. >> ================================================== > > Well, you need to put it in a procedure. If you are just running it as is,
comment out the RETURN because it is both invalid and unnecessary. <rcul***@ranger-systems.com> wrote in message news:%23uCmB6asGHA.372@TK2MSFTNGP06.phx.gbl... All I get with this procedure is:Msg 178, Level 15, State 1, Line 8 A RETURN statement with a return value cannot be used in this context. Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:%23OucFhasGHA.372@TK2MSFTNGP06.phx.gbl... > Here is what I use: > > DECLARE @r INT, @ServerName SYSNAME; > SET @ServerName = 'MyLinkedServer'; > BEGIN TRY > EXEC @r = sys.sp_testlinkedserver @ServerName; > END TRY > BEGIN CATCH > RAISERROR('Unable to connect to %s.', 11, 1, @ServerName); > RETURN -1; > END CATCH > > > "Dan" <D**@discussions.microsoft.com> wrote in message > news:C04E8073-D5DA-4C86-904E-B1BA9A6C303E@microsoft.com... >> Has anyone been able to get a return code of 1 using this new proc in >> SQL2k5? >> >> I have a SQL2k5 server that I am setting up a linked server to a SQL2k >> server and have been able to successfully query against tables on the >> SQL2k >> server and also the sp_testlinkedserver returns a 0. So, all is good. >> >> However, I want to simulate a scenario where the server is not available, >> so >> I went out and stopped the SQL2k server that am linking to, then ran >> sp_testlinkedserver. Well, instead of what I was expecting, which a >> return >> code of 1, I received the error message below and it behaves just like it >> did >> in SQL2k, just killed everything. No catching the error, just aborted the >> process. >> >> Is there a way to test for a server you are linking to being down(for >> whatever reason, maintenance, etc.) and be able to catch/trap the error? >> >> I was under the impression that sp_testlinkedserver would do the trick, >> but >> I am missing something. >> >> Thanks in advance. >> >> ================================================== >> OLE DB provider "SQLNCLI" for linked server "Test2kServer" returned >> message >> "Login timeout expired". >> OLE DB provider "SQLNCLI" for linked server "Test2kServer" returned >> message >> "An error has occurred while establishing a connection to the server. >> When >> connecting to SQL Server 2005, this failure may be caused by the fact >> that >> under the default settings SQL Server does not allow remote >> connections.". >> Msg 5, Level 16, State 1, Line 0 >> Named Pipes Provider: Could not open a connection to SQL Server [5]. >> ================================================== > > Yes, that worked. By the way, what is "%s" and are there more of these
useful little tools? Also, as you are an MVP, could you briefly review my posting entitled TRUSTWORTHY? If you have any thoughts or insights, they would be greatly appreciated. Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:eTo028asGHA.4956@TK2MSFTNGP02.phx.gbl... > Well, you need to put it in a procedure. If you are just running it as > is, comment out the RETURN because it is both invalid and unnecessary. > > > <rcul***@ranger-systems.com> wrote in message > news:%23uCmB6asGHA.372@TK2MSFTNGP06.phx.gbl... > All I get with this procedure is: > > Msg 178, Level 15, State 1, Line 8 > > A RETURN statement with a return value cannot be used in this context. > > > > > > "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in > message news:%23OucFhasGHA.372@TK2MSFTNGP06.phx.gbl... >> Here is what I use: >> >> DECLARE @r INT, @ServerName SYSNAME; >> SET @ServerName = 'MyLinkedServer'; >> BEGIN TRY >> EXEC @r = sys.sp_testlinkedserver @ServerName; >> END TRY >> BEGIN CATCH >> RAISERROR('Unable to connect to %s.', 11, 1, @ServerName); >> RETURN -1; >> END CATCH >> >> >> "Dan" <D**@discussions.microsoft.com> wrote in message >> news:C04E8073-D5DA-4C86-904E-B1BA9A6C303E@microsoft.com... >>> Has anyone been able to get a return code of 1 using this new proc in >>> SQL2k5? >>> >>> I have a SQL2k5 server that I am setting up a linked server to a SQL2k >>> server and have been able to successfully query against tables on the >>> SQL2k >>> server and also the sp_testlinkedserver returns a 0. So, all is good. >>> >>> However, I want to simulate a scenario where the server is not >>> available, >>> so >>> I went out and stopped the SQL2k server that am linking to, then ran >>> sp_testlinkedserver. Well, instead of what I was expecting, which a >>> return >>> code of 1, I received the error message below and it behaves just like >>> it >>> did >>> in SQL2k, just killed everything. No catching the error, just aborted >>> the >>> process. >>> >>> Is there a way to test for a server you are linking to being down(for >>> whatever reason, maintenance, etc.) and be able to catch/trap the error? >>> >>> I was under the impression that sp_testlinkedserver would do the trick, >>> but >>> I am missing something. >>> >>> Thanks in advance. >>> >>> ================================================== >>> OLE DB provider "SQLNCLI" for linked server "Test2kServer" returned >>> message >>> "Login timeout expired". >>> OLE DB provider "SQLNCLI" for linked server "Test2kServer" returned >>> message >>> "An error has occurred while establishing a connection to the server. >>> When >>> connecting to SQL Server 2005, this failure may be caused by the fact >>> that >>> under the default settings SQL Server does not allow remote >>> connections.". >>> Msg 5, Level 16, State 1, Line 0 >>> Named Pipes Provider: Could not open a connection to SQL Server [5]. >>> ================================================== >> >> > > > Yes, that worked. By the way, what is "%s" and are there more of these %s is used to tokenize a string. %d is used to tokenize a number. I think > useful little tools? they can work interchangeably but I haven't experimented too much. > Also, as you are an MVP, could you briefly review my posting entitled Sorry, I know very little about TRUSTWORTHY, and even less about how it > TRUSTWORTHY? If you have any thoughts or insights, they would be greatly > appreciated. applies to 2000. A  |
|||||||||||||||||||||||
Other interesting topics