Detect linked server down

[Dooza]

Hi there,
I am using 2 x SQL2000 for an intranet application (classic ASP). Some
of the data is coming from a linked server (thats the second SQL2000).

Very occasionally we lose our internet connection, which means our
linked server isn't accessible.

I need to create a stored procedure that does an update on both servers
at the same time. I want this to be aware of the linked server not being
accessible, do the local part of the update, but also mark it so that
when the link is back up, the remote part of the update can be carried out.

Is this at all possible? Can the status of a linked server be obtained?
The SQL user won't have loads of privileges. It can run stored
procedures on the linked server, but its not a sys admin, not sure if
that matters or not.

Any advice is welcome.

Cheers,

Dooza

[Uri Dimant]

Dooza

Ping the server

SET NOCOUNT ON

CREATE TABLE #t_ip (ip varchar(255))
DECLARE @PingSql varchar(1000)
SELECT @PingSql = 'ping ' + '10.10.5.2'
INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @PingSql
SELECT * FROM #t_ip
IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
BEGIN
DROP TABLE #t_ip
RETURN
END
DROP TABLE #t_ip

"Dooza" <ste...@SPAM.dooza.tv> wrote in message
news:uDphaxPb...@TK2MSFTNGP05.phx.gbl...

[Dooza]

Uri Dimant wrote:
> Dooza
>
> Ping the server
>
> SET NOCOUNT ON
>
> CREATE TABLE #t_ip (ip varchar(255))
> DECLARE @PingSql varchar(1000)
> SELECT @PingSql = 'ping ' + '10.10.5.2'
> INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @PingSql
> SELECT * FROM #t_ip
> IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
> BEGIN
> DROP TABLE #t_ip
> RETURN
> END
> DROP TABLE #t_ip

Hi Uri,
This looks good, I take it I can put it in a stored procedure and just
call that from stored procedures that use the linked servers?

Steve

[TheSQLGuru]

uri's solution may get you the KNOWLEDGE that the server is up or down, but
you also seem to need to be able to make the update happen AFTER the server
comes back up if it is down and updates need to be processed. This implies
the need for asynchronous processing. You can roll your own here (generate
dynamic update statements and store them in a driver table and iterate over
them with some scheduled or fired job when the linked server is available).
Or you can investigate the built-in SQL Server Service Broker to help do
this for you.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

"Dooza" <ste...@SPAM.dooza.tv> wrote in message
news:uDphaxPb...@TK2MSFTNGP05.phx.gbl...