| Author |
Is there a way to check if a linked server is live? thanks
|
|
| davidw 2005-07-16, 8:23 pm |
| I want to have two linked servers, and call one of them to execute some
queries. If one is down, I need a way to find it out and call another linked
server, is it possible?
thanks!
| |
| Uri Dimant 2005-07-17, 3:23 am |
| Hi
Ping it and see you don't have a timeout expired
CREATE TABLE #t_ip (ip varchar(255))
DECLARE @PingSql varchar(1000)
SELECT @PingSql = 'ping ' + '00.00.0.0'
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
"davidw" <davidw@affinisys.com> wrote in message
news:umrQf1liFHA.3328@TK2MSFTNGP10.phx.gbl...
> I want to have two linked servers, and call one of them to execute some
> queries. If one is down, I need a way to find it out and call another
linked
> server, is it possible?
>
> thanks!
>
>
| |
| davidw 2005-07-17, 8:23 pm |
| Quit a special solution, thanks.
But I want a real-time check, that can be done in milliseconds. One way I
can think is let my two linked server regularly update a table in my main
db. But that is not robust enough.
What I want to do is put some SPs in my linked server. But the two servers
are not that reliable, I want a way to switch between them.
"Uri Dimant" <urid@iscar.co.il> wrote in message
news:up3wIRqiFHA.3256@TK2MSFTNGP12.phx.gbl...
> Hi
> Ping it and see you don't have a timeout expired
>
> CREATE TABLE #t_ip (ip varchar(255))
> DECLARE @PingSql varchar(1000)
> SELECT @PingSql = 'ping ' + '00.00.0.0'
> 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
>
>
>
> "davidw" <davidw@affinisys.com> wrote in message
> news:umrQf1liFHA.3328@TK2MSFTNGP10.phx.gbl...
> linked
>
>
| |
| Jasper Smith 2005-07-17, 8:23 pm |
| usp_serverup
http://www.sqldbatips.com/showcode.asp?ID=38
--
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
"davidw" <davidw@affinisys.com> wrote in message
news:umrQf1liFHA.3328@TK2MSFTNGP10.phx.gbl...
>I want to have two linked servers, and call one of them to execute some
> queries. If one is down, I need a way to find it out and call another
> linked
> server, is it possible?
>
> thanks!
>
>
| |
| davidw 2005-07-18, 3:23 am |
| Nice solution.
But I got this error :
You must use SQL Server Management Studio or SQL Server Management Objects
(SMO) to connect to this server
Any idea?
thanks
"Jasper Smith" < jasper_smith9@hotmai
l.com> wrote in message
news:uXxrcbyiFHA.2852@TK2MSFTNGP14.phx.gbl...
> usp_serverup
> http://www.sqldbatips.com/showcode.asp?ID=38
>
> --
> 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
>
> "davidw" <davidw@affinisys.com> wrote in message
> news:umrQf1liFHA.3328@TK2MSFTNGP10.phx.gbl...
>
>
| |
| Jasper Smith 2005-07-18, 1:23 pm |
| Are you using SQL2005? If so I think there's a new built in system stored
procedure to do this but the name escapes me. I'll have a look and see if I
remember :-)
--
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
"davidw" <davidw@affinisys.com> wrote in message
news:ubFeme1iFHA.3704@TK2MSFTNGP10.phx.gbl...
> Nice solution.
>
> But I got this error :
>
> You must use SQL Server Management Studio or SQL Server Management Objects
> (SMO) to connect to this server
>
> Any idea?
>
> thanks
>
>
> "Jasper Smith" < jasper_smith9@hotmai
l.com> wrote in message
> news:uXxrcbyiFHA.2852@TK2MSFTNGP14.phx.gbl...
>
>
| |
| Jasper Smith 2005-07-18, 1:23 pm |
| If using SQL2005 you can use sp_testlinkedserver. See BOL for details (not
that there are many<g> )
--
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
"davidw" <davidw@affinisys.com> wrote in message
news:ubFeme1iFHA.3704@TK2MSFTNGP10.phx.gbl...
> Nice solution.
>
> But I got this error :
>
> You must use SQL Server Management Studio or SQL Server Management Objects
> (SMO) to connect to this server
>
> Any idea?
>
> thanks
>
>
> "Jasper Smith" < jasper_smith9@hotmai
l.com> wrote in message
> news:uXxrcbyiFHA.2852@TK2MSFTNGP14.phx.gbl...
>
>
|
|
|
|