Post reply

SQL JOB Problem

  • May 18, 2006 at 8:24 pm

    #169520

    Is there any way to create a T-sql query/SQL JOB that checks the status of MSSQLSERVR.exe(in Control Panel>Administrative Tools>Services) whether if it is started or unexpectedly stopped due to power failure, etc.

    Here is my scenario, i have a SQL Server 2K Standard Edition on my box and another SQL Server 2k Standard Edition on a remote location. The box that is on the remote location is our PRODUCTION Server while my box is just for test restore, some query testings, etc.

    Now, i dont want to use the built-in feature of SQL Server 2k which is SQL Mail or sp_sendmail because of the tedious setup and our company doesn't use OUTLOOK or EXCHANGE Server.

    What i'm using is the extended procedure called xp_SMTP_sendmail downloadable at SQLDev.Net. Its a xtended procedure that doesn't need any MAPI Profile, Outlook client or even an Exchange Server. All it needs is a working SMTP Server and a valid SMTP Account. I'm already using this to check my daily backups.

    another question is, is there a way to have the job installed in my box? Because there is no way to send e-mail if the server is shutdown unexpectedly. So, is it possible to have it in my box?

  • May 19, 2006 at 5:06 am

    #638940

    This is my first thought and the may be a little indirect, but you could create a job on your local box that queries a table in the remote database.  If the query returns data, you know the database is up, therefore the service must be running.

    The table should be one you know will not be dropped or renamed, maybe a one row, one column table created just for this test.

    If the job/query fails, you won't know for sure that the service isn't running but you would be alerted and could check it manually.

    I'm sure there are ways to check the service status in C#, VB.NET, C++, etc. but it sounds as though you are looking for a pure SQL Server-based solution.

    Good luck.

  • May 19, 2006 at 6:58 am

    #638960

    I would suggest looking at VBScript and the WMI objects.  You can run a VBScript on one server to check objects (like services) on another server.  Check http://www.microsoft.com/scripting (and especially their tool on that site Script-o-matic) on how to do this.

    You can also run that VBScript from SQL proc or query using xp_cmdshell, or use a ActiveX script step in a SQL job.

    Hope this helps.



    Mark

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

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