method for testing whether server exists?
-
Hi there-
I have a linked server specified on my sql server. I have a daily import process to pull from the linkedserver however would like to write out to a log table when the linkedserver is found to be unavailable (for netwrok reasons or whatever). Is there a system sproc, or some other method by which I can programmtically make such a determination?
Thanks for any and all help!
Al
-
R u doing this in a DTS?
-
YES
-
This little proc will return a 1/0 = there/not there. Using the output you can then insert a record in a table based on the status of the flag. This can be done using an if statement.
drop proc usp_FindServer
go
create proc usp_FindServer (@Server varchar(20),@Flag int output)
as
create table #linked (
srv_name varchar(20),
provider varchar(20),
product varchar(20),
datasource varchar(20),
string varchar(20),
location varchar(20),
cat varchar(20))
set nocount on
insert #linked
exec sp_linkedservers
set @Flag = (select 1 from #linked where srv_name = @server)
if @Flag > 0
begin
set @Flag = 1
end
else
begin
set @Flag = 0
end
drop table #linked
print @Flag
go
/*
This is the call. Simply replace ServerName
with the name of the server you wish to check.
*/
exec usp_FindServer 'ServerName',0
-
That script will check if the linked server is defined, it won't test the network connectivity.
Most reliable method is to run a simple select statement on the remote server using OSQL.
--------------------
Colt 45 - the original point and click interface
-
A select statement will generate an error if the linked server isn't there, but because it'll most likely be classed as a distributed transaction then SET XACT_ABORT will be ON, thus aborting your batch without any "nice" error handling.
I had the same problem - what happens if my distributed query ran (frequently) when the linked server was rebooting, services stopped etc.
So I wrote this. This is not my actual, I made it more generic.
From DTS, maybe implement it as a stored proc:
CREATE FUNCTION dbo.ufn_CheckLinkedServerUp (@linkedsrvname varchar(30))
RETURNS int
AS
BEGIN
DECLARE @srvobj int, @netname varchar(30), @found int
SELECT @netname = datasource FROM master.dbo.sysservers WHERE srvname = @linkedsrvname
IF @netname IS NULL
SET @found = 0
ELSE IF @netname <> @@SERVERNAME
BEGIN
EXEC @found = master.dbo.sp_OACreate 'SQLDMO.SQLServer', @srvobj OUTPUT
IF @found = 0
BEGIN
EXEC master.dbo.sp_OASetProperty @srvobj, 'LoginTimeout', 2
EXEC master.dbo.sp_OASetProperty @srvobj, 'LoginSecure', 'TRUE'
EXEC @found = master.dbo.sp_OAMethod @srvobj, 'Connect', NULL, @netname
IF @found = 0
BEGIN
SET @found = 1
EXEC master.dbo.sp_OAMethod @srvobj, 'DisConnect', NULL
END
ELSE --error, @found <> 0
SET @found = 0
EXEC master.dbo.sp_OADestroy @srvobj
END
ELSE --error, @found <> 0
SET @found = 0
END
ELSE
SET @found = 1
RETURN @found
END
GO
-
If you run the SELECT statement using xp_cmdshell and OSQL you can have your cake and eat it to
Yes the batch will be aborted, but it will be the batch thats running in OSQL, not the batch that's calling OSQL.
That said, I do like your SQL-DMO method.
--------------------
Colt 45 - the original point and click interface -
Thanks.
I might have considered xp_cmdshell but maybe because I wanted to use a function rather than stored proc. I can't remember for sure now.
xp_cmdshell is simple, maybe better, solution though...
-
This function worked like a charm.
Many Thanks!
-
Just to add something that was pointed out to me by a colleague, the function won't work for SQL Server 2005 (need to use SMO instead of DMO), but the OSQL method will.
--------------------
Colt 45 - the original point and click interface
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply