See if PING in a command window gives us a return value.
L:\>ping sqldev -n 1
Pinging sqldev.mylan.local [10.01.64.37] with 32 bytes of data:
Reply from 10.01.64.37: bytes=32 time<1ms TTL=128
Ping statistics for 10.01.64.37:
Packets: Sent = 1, Received = 1, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms
L:\>ECHO %ERRORLEVEL%
0
L:\>ping sqldevx -n 1
Ping request could not find host sqldevx. Please check the name and try again.
L:\>ECHO %ERRORLEVEL%
1
It does -- see if we can utilize that using T-SQL. I'm using some of my helper procedures e.g. sp__PRINT but you can replace it with just PRINT commands for debugging.
SET nocount ON
DECLARE @RC INT,
@bVERBOSE bit,
@vcCmd VARCHAR(1024),
@vcSQL NVARCHAR(1024),
@vcServerName VARCHAR(128)
SELECT @bVERBOSE = 1,
@vcServerName = 'SQLDEV',
@vcCmd = 'ping ' + LEFT(@vcServerName , CASE CHARINDEX('\',@vcServerName)
WHEN 0 THEN LEN(@vcServerName) ELSE CHARINDEX('\',@vcServerName) -1 END) + ' -n 1',
@vcSQL = 'IF NOT EXISTS (SELECT srvname FROM master.dbo.sysservers
WHERE quotename(srvname) = quotename(''' + @vcServerName + ''') ) RAISERROR(''ERROR: no linked server to test'',16,1)'
EXEC @RC = master.dbo.xp_cmdshell @vcCmd, no_output
IF @bVERBOSE = 1
EXEC sp__PRINT @RC
IF @RC <> 0
BEGIN
RAISERROR('ERROR: Server %s NOT FOUND on the network.', 16,1, @vcServerName)
END ELSE
BEGIN
IF @bVERBOSE = 1
BEGIN
EXEC sp__PRINT 'See if there is a linked server, locally'
PRINT @vcSQL
END
EXEC @RC = sp_executesql @vcSQL
IF @bVERBOSE = 1
BEGIN
EXEC sp__PRINT 'Run REMOTE QUERY - Test remote linked server connectivity'
PRINT @vcSQL
END
EXEC @RC = sp_executesql @vcSQL
IF @RC <> 0
BEGIN
RAISERROR('ERROR: connecting to linked server [%s].', 16,1,@vcServerName)
END
END ELSE
SELECT srvname FROM master.dbo.sysservers
END
GO
Here's some output:
IF NOT EXISTS (SELECT srvname FROM master.dbo.sysservers
WHERE quotename(srvname) = quotename('SQLDEV') ) RAISERROR('ERROR: no linked server to test',16,1)
********************************************************************************
2009-02-11 14:29:00.013 0
********************************************************************************
********************************************************************************
2009-02-11 14:29:00.013 See if there is a linked server, locally
********************************************************************************
IF NOT EXISTS (SELECT srvname FROM master.dbo.sysservers
WHERE quotename(srvname) = quotename('SQLDEV') ) RAISERROR('ERROR: no linked server to test',16,1)
********************************************************************************
2009-02-11 14:29:00.077 Run REMOTE QUERY – Test remote linked server connectivity
********************************************************************************
IF NOT EXISTS (SELECT srvname FROM [SQLDEV].master.dbo.sysservers
WHERE quotename(srvname) = quotename('SQLDEV') ) RAISERROR('ERROR: cannot connect to linked server',16,1)