Remote SQL Servers 1

cybermud · May 4, 2004

I have several remote SQL Servers which I get data from on a daily basis via a VPN link over the internet. The method that I use is a SQL Server Agent job that executes a stored procedure which connects to each remote server (sp_addlinkedserver), sets up a logon (sp_addlinkedsrvlogin), and copies the new data. This works fine, unless one of my remote sites data lines are down, at which point the whole job fails. I would like to check to see if the line is up within the stored procedure and skip that location if its down, but I can't find anything remotely similar to what I am trying to accomplish within BOL or on MSDN. Has anybody here ever done anything similar to this, or know of any documentation that they can point me at?

Thanks,
Erik

TJRTech · May 4, 2004

How about writing your own stored procedure that uses xp_cmdshell to "ping" the server. The SP could return 1 if pinged without loss, else 0.

Here is the code for the SP:

Code:

CREATE PROCEDURE spPingServer
   @server varchar(50),
   @okay int OUTPUT 
AS
  DECLARE @cmd nvarchar(255)

  -- Create temp table for output
  CREATE TABLE #cmdout (line nvarchar(255))

  -- Insert output from ping into temp table
  SET @cmd = N'ping ' + @server
  INSERT INTO #cmdout (line)
  EXEC master..xp_cmdshell @cmd

  -- Set return param based on whether or not ping worked without loss
  IF EXISTS ( SELECT OK_STAT_ROW =  line FROM #cmdout WHERE line like '%0%% loss%')
    SET @okay=1
  ELSE
    SET @okay=0

  DROP TABLE #cmdout
RETURN(0)

And, testing it is as simple as:

Code:

DECLARE @okay int
exec spPingServer 'tomr', @okay OUTPUT
select @okay

cybermud · May 4, 2004

Thanks, this is just what I was looking for!

TJRTech · May 6, 2004

I got to thinking, and actually had to build my own SPs that had to recover from linked servers that were down, and these SPs were ultra-critical. As I thought about it, I was considered with the TCP ping approach. A better approach would be to shell out to ISQL to see if the server is available.

Here is THAT code:

Code:

CREATE PROCEDURE spIsSQLServerOkay
   @server varchar(50),
   @userid varchar(50),
   @password varchar(50),
   @is_okay int OUTPUT 
AS
  DECLARE @cmd nvarchar(255)

  -- Create temp table for output
  CREATE TABLE #cmdout (line nvarchar(255))

  -- Insert output from ISQL into temp table
  SET @cmd = N'isql -S '+@server+' -U '+@userid+' -P '+@password+' -Q "select ''OK''"'
  INSERT INTO #cmdout (line)
  EXEC master..xp_cmdshell @cmd

  -- Set return param based on whether or not ISQL worked without loss
  IF EXISTS ( SELECT OK_STAT_ROW =  line FROM #cmdout WHERE line like '%OK%')
    SET @is_okay=1
  ELSE
    SET @is_okay=0

  DROP TABLE #cmdout
RETURN(0)

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Remote SQL Servers 1

cybermud

Programmer

TJRTech

Programmer

cybermud

Programmer

TJRTech

Programmer

Similar threads

Part and Inventory Search

Sponsor