VB script for checking whether server is running or shutdown

Question

VB script for checking whether server is running or shutdown

arj

SSC-Addicted

Points: 444
More actions
November 16, 2007 at 5:35 am

#377078

Hi Everybody,
i wanna chek the opposite server status whether it is running or shutdown.
this task i wanted to do thru DTS package(SQL 2000). for that i have choosen Activex scripting where i can place the VB script or Java Script to check the status of the server.
according to my requirement if server is running DTS shud send successful msg. If not DTS shud send shutdown msg. Based on this task only i can do further.
Kindest Regards,
arj

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 1

I'd keep it simple... create a linked server to the "other" server. Then do a SELECT TOP 1 * FROM [linkedservername].Master.dbo.Sysobjects (or, in 2k5, I think it would be the sys.Objects view, not sure). If it doesn't error, the server is up and running 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lowell SSC Guru Points: 323518 More actions · Answer 2

there's so many levels you can check, it depends on what you want to do.

For example:

--you can ping a server via a script to see if DNS works/server exists on the network.

--then you can check to see if the service for SQL server is running (and you might need to check for multiple instances, or for a specific instance.

--from there, like Jeff Model exampled, you might need to check if the SQL Server is allowing connectivity at all,

--from there, you might need to confirm that a specific database exists on the server (master.dbo.sysdatabases)

--after that, you might need to check if the SQL Server is allowing connectivity to that specific database

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

I wrote a DTS package like this a long time ago (back in 2001). Basically I used ADO to attempt to make a connection. I then checked the status of the connection. It only took about 10 lines of code. That's probably the easiest way to do it in an ActiveX Scripting task. This also precludes the requirement to have hard-coded linked server connections.

K. Brian Kelley
@kbriankelley