Linked Server checking.. ie.. how to check remote service is running

[Stacey Levine]

Does anyone out there have a script/stored proc... whatever that will tell
whether the SQL Services are running on a remote server? I need this to run
a check on a linked server before executing commands. Thanks.

Stacey

[Steve Thompson]

"Stacey Levine" <sta...@NOSPAMvcei.com> wrote in message
news:#hYfOV3pCHA.1644@TK2MSFTNGP10...

Here is a VB script that should help you determine that information (you'd
have to customize it for your use):

ComputerName = InputBox("Enter the name of the computer for which you want
service information")

winmgmt1 = "winmgmts:{impersonationLevel=impersonate}!//"& ComputerName &""

Set ServSet = GetObject( winmgmt1 ).InstancesOf ("Win32_service")

for each Serv in ServSet
IF Serv.Description = "MSSQLSERVER" THEN
GetObject("winmgmts:").InstancesOf ("win32_service")
WScript.Echo ""
WScript.Echo Serv.Description
WScript.Echo " Executable: ", Serv.PathName
WScript.Echo " Status: ", Serv.Status
WScript.Echo " State: ", Serv.State
WScript.Echo " Start Mode: ", Serv.StartMode
Wscript.Echo " Start Name: ", Serv.StartName
END IF
next

[Jasper Smith]

Here's a way using SQLDMO, you would call it like this

declare @up int

exec sp_serverup
@server='JASXP\TEST1',
@up=@up OUTPUT

If @up<>1
begin
raiserror('Server not responding',16,1)
end
else
begin
...........your code here
end

And here's the procedure code :

use master
go

CREATE PROCEDURE sp_serverup
(
@server sysname,
@up int OUTPUT
)
AS
/*
1 = Running
-1 = Down
*/
SET NOCOUNT ON
DECLARE @hr int
DECLARE @sql int
DECLARE @status int ; SET @status = 0

EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @sql OUTPUT
IF @hr<>0 EXEC sp_OAGetErrorInfo @sql

EXEC @hr = sp_OASetProperty @sql ,'LoginSecure','True'
EXEC @hr = sp_OASetProperty @sql ,'LoginTimeout',10
EXEC @hr = sp_OAMethod @sql,'Connect',null,@server
IF @hr<>0 EXEC sp_OAGetErrorInfo @sql

EXEC @hr = sp_OAGetProperty @sql ,'Status',@status OUTPUT
EXEC @hr = sp_OAMethod @sql,'DisConnect',null
EXEC @hr=sp_OADestroy @sql

SELECT @up = CASE WHEN @status = 1
THEN 1 ELSE -1 END

RETURN

--
HTH

Jasper Smith (SQL Server MVP)

I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org

"Stacey Levine" <sta...@NOSPAMvcei.com> wrote in message
news:#hYfOV3pCHA.1644@TK2MSFTNGP10...

[Stacey Levine]

Thanks.. I will give both of these a shot.

Stacey

"Jasper Smith" <jasper...@hotmail.com> wrote in message
news:#4ALGO6pCHA.2480@TK2MSFTNGP12...