Tesing If a linked server exists.
-
Does anybody know a way that when referencing a linked server you can check whether it exists frist.
I am firing an Insert trigger in one db that fires a sp which does an update on a linked server, however, if the conncetion has dropped to the linked server the trigger fails and likewise the Insert.
I would like to be able to test if the server exsists before I attempt the link server update or catch the error and stop the trigger failing.
Any Idea/Experience in this area?
Thanks
-
Trigger fire data to staging table.
A scheduled job try the linked server, if connected, delete the data from the staging table.
-
Try this stored procedure : it returns 1 if the server link is OK, 0 if not...
use master
go
if object_id('dbo.sp_VerifLinkedServer') is not null
drop procedure dbo.sp_VerifLinkedServer
go
create procedure dbo.sp_VerifLinkedServer
(
@RemoteServer sysname
)
as
begin
set nocount on
declare @cmd nvarchar(512)
set @cmd = 'set ansi_nulls on set ansi_warnings on insert ##tmp select top 1 spid = '
+ str(@@spid) + ' from '
+ @RemoteServer
+ '.master.dbo.sysprocesses'
set @cmd = 'osql -S ' + @@servername + ' -E -Q "' + @cmd + '" '
if object_id('tempdb..##tmp') is null
create table ##tmp(spid int)
else
delete ##tmp where spid = @@spid
exec master.dbo.xp_cmdshell @cmd,no_output
if exists ( select 1 from ##tmp where spid = @@spid )
return 1 -- Server OK
else
return 0 -- Server KO
end
go
grant exec on dbo.sp_VerifLinkedServer to public
go
declare @rc int
exec @rc = dbo.sp_VerifLinkedServer 'QUIX'
print @rc
-
You could perform the same function without using OSQL and xp_cmdshell.
CREATE PROCEDURE dbo.usp_TestLinkedServer @SrvrNm nvarchar(128) AS BEGIN SET NOCOUNT ON
DECLARE @sql nvarchar(500)
SET @sql = N'SELECT TOP 1 [spid] FROM ' + @SrvrNm + '.master.dbo.sysprocesses' EXEC (@sql) RETURN(@@ERROR)
END
GO
DECLARE @rc int EXEC @rc = dbo.usp_TestLinkedServer'TEST' PRINT @rc
--------------------
Colt 45 - the original point and click interface
-
I used osql
1. to avoid the error message. Without osql you get this for an unknown server :
Test :
DECLARE @rc int
EXEC @rc = dbo.usp_TestLinkedServer 'SQLTRIX'
PRINT @rc
print 'Batch is here'
Result :
Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'TEST' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
7202
Batch is here
2. when a server is not reachable, the error level is 16, and the batch will not continue :
Test :
DECLARE @rc int
EXEC @rc = dbo.usp_TestLinkedServer 'SQLTRIX'
PRINT @rc
print 'Batch is here'
Result :
Server: Msg 17, Level 16, State 1, Line 1
De SQL-server bestaat niet of de toegang tot de server is geweigerd.
So, as you can see, exec(@sql) will not solve the problem ... using osql does!
Bert
-
Well I must have a special server
I use this exact routine in my custom log shipping routine that runs every 5 minutes on one server and every 20 mins on another. It correctly reports any problems with the linked servers.
Having a server shutdown would make it unreachable wouldn't it?
--------------------
Colt 45 - the original point and click interface -
Thanks for your replies guys, however, I am having problems getting triggers and SP's to work even when there IS a connection. The following senario seems to get the system in an infinate loop, with no error messages returned, each of the components run perfectly fine individually but not together. Have ended up using a 10 min batch job to catch any updates.
update on server1.db.dbo.table1
|
update trigger
|
|[linked server]
|
stored proc
|
update server2.db.dbo.table2
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply