check link server availability in sp

I am changing a stored proc to use linked servers.
We have 5 linked sql servers, each in different physical locations and
from time to time the network connection at one of the locations may go
down. It is critical that if I can't get to a linked server, that my sp
doesn't fail and return an error code. I just need to run a different
sql statement against just the one sql server.
I am trying to simulate this in my lab by doing any of the following:
- physically disconnecting the linked sql server's cat5 cable from the
network
- pausing the linked server
- changing the name of linked server on my local server so the names
don't match.
In each case I get a message saying
Server: Msg 11, Level 16, State 1, Line 1
General network error. Check your network documentation.
I can't change the code, I really need to accomplish this in stored
procedure(s)
Here is my stored procedure.
SET XACT_ABORT OFF
SET @.sSQL = ' SELECT count(id) FROM LINKEDSVR1.dbName.dbo.tli '
EXEC @.iReturnCode = sp_executesql @.sSql
SET XACT_ABORT ON
PRINT CONVERT(CHAR(5), @.@.Error) + ' - @.@.ErrorCode'
PRINT CONVERT(CHAR(5), @.iReturnCode) + ' - @.iReturnCode'
IF @.iReturnCode = 1
BEGIN
PRINT 'can't connect to linked server'
-- run sql statement1 on local sql server
END
ELSE
BEGIN
PRINT 'no error'
-- run sql statement2 on local and linked sql server
END
Any ideas?I have written a small activeX dll that I use to ping the target servers
using the sp_OA methods for active X integration with transactSQL. I've
wrapped this up in a procedure IsHostAlive_sp @.hostName that will check.
The server itself must be able to resolve the address.
You can have the DLL if you want, its a VB6 project.
Phil
<rinfo@.mail.com> wrote in message
news:1130424229.400896.218240@.g14g2000cwa.googlegroups.com...
>I am changing a stored proc to use linked servers.
> We have 5 linked sql servers, each in different physical locations and
> from time to time the network connection at one of the locations may go
> down. It is critical that if I can't get to a linked server, that my sp
> doesn't fail and return an error code. I just need to run a different
> sql statement against just the one sql server.
> I am trying to simulate this in my lab by doing any of the following:
> - physically disconnecting the linked sql server's cat5 cable from the
> network
> - pausing the linked server
> - changing the name of linked server on my local server so the names
> don't match.
> In each case I get a message saying
> Server: Msg 11, Level 16, State 1, Line 1
> General network error. Check your network documentation.
> I can't change the code, I really need to accomplish this in stored
> procedure(s)
> Here is my stored procedure.
> SET XACT_ABORT OFF
> SET @.sSQL = ' SELECT count(id) FROM LINKEDSVR1.dbName.dbo.tli '
> EXEC @.iReturnCode = sp_executesql @.sSql
> SET XACT_ABORT ON
> PRINT CONVERT(CHAR(5), @.@.Error) + ' - @.@.ErrorCode'
> PRINT CONVERT(CHAR(5), @.iReturnCode) + ' - @.iReturnCode'
> IF @.iReturnCode = 1
> BEGIN
> PRINT 'can't connect to linked server'
> -- run sql statement1 on local sql server
> END
> ELSE
> BEGIN
> PRINT 'no error'
> -- run sql statement2 on local and linked sql server
> END
> Any ideas?
>|||Ping is probably a decent first step, but a small one.
If it's not on the same LAN, pings may get dropped on the floor due to
outright refusal of traffic, this is very common, e.g. ping
www.microsoft.com
Even a successful ping result does not mean everything is okay. Did you
check the actual port SQL Server is listening on (may be disabled or
blocked)? Are you sure that the specific SQL Server instance is running?
Are you sure that your login credentials are good?
Another approach might be to inspect the results of an osql call.
create table #foo( dbname sysname null )
insert #foo exec master..xp_cmdshell
'osql -S<linkedservername> -U<username> -P<password> -dMaster -Q"SELECT TOP
1 Name FROM sysdatabases"'
SELECT * FROM #foo
drop table #foo
This will take 10 seconds if <linkedservername> is not available. The
result will be:
(3 row(s) affected)
dbname
----
----
[DBNETLIB]SQL Server does not exist or access denied.
[DBNETLIB]ConnectionOpen (Connect()).
NULL
(3 row(s) affected)
"Phil Simpson" <phil.simpson@.nsdlsystems.com> wrote in message
news:uOu4tow2FHA.400@.TK2MSFTNGP09.phx.gbl...
>I have written a small activeX dll that I use to ping the target servers
>using the sp_OA methods for active X integration with transactSQL. I've
>wrapped this up in a procedure IsHostAlive_sp @.hostName that will check.
>The server itself must be able to resolve the address.
> You can have the DLL if you want, its a VB6 project.
> Phil|||You can use SQLDMO as in this example
http://www.sqldbatips.com/displaycode.asp?ID=38
In SQL2005 there is a bultin system procedure to do this
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<rinfo@.mail.com> wrote in message
news:1130424229.400896.218240@.g14g2000cwa.googlegroups.com...
>I am changing a stored proc to use linked servers.
> We have 5 linked sql servers, each in different physical locations and
> from time to time the network connection at one of the locations may go
> down. It is critical that if I can't get to a linked server, that my sp
> doesn't fail and return an error code. I just need to run a different
> sql statement against just the one sql server.
> I am trying to simulate this in my lab by doing any of the following:
> - physically disconnecting the linked sql server's cat5 cable from the
> network
> - pausing the linked server
> - changing the name of linked server on my local server so the names
> don't match.
> In each case I get a message saying
> Server: Msg 11, Level 16, State 1, Line 1
> General network error. Check your network documentation.
> I can't change the code, I really need to accomplish this in stored
> procedure(s)
> Here is my stored procedure.
> SET XACT_ABORT OFF
> SET @.sSQL = ' SELECT count(id) FROM LINKEDSVR1.dbName.dbo.tli '
> EXEC @.iReturnCode = sp_executesql @.sSql
> SET XACT_ABORT ON
> PRINT CONVERT(CHAR(5), @.@.Error) + ' - @.@.ErrorCode'
> PRINT CONVERT(CHAR(5), @.iReturnCode) + ' - @.iReturnCode'
> IF @.iReturnCode = 1
> BEGIN
> PRINT 'can't connect to linked server'
> -- run sql statement1 on local sql server
> END
> ELSE
> BEGIN
> PRINT 'no error'
> -- run sql statement2 on local and linked sql server
> END
> Any ideas?
>