SQL JOB help
-
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?
-
Leonardo,
Check out the following link, http://www.databasejournal.com/features/mssql/article.php/3347241.
You can create this heartbeat checker and run it from your box (or another monitoring box). All you have to do is, schedule (using Windows Scheduler) the .exe to run every 15 minutes (or at whatever time interval you choose).
Note that you can configure it to run against multiple servers.
Hope that helps,
-
Thanks dude, i'll try this out.....tnx again..
-
Hi grambowk,
Correct me if i'm wrong but i tried the URL that you posted and followed all the instructions. However, everytime i execute the script it always shows this output:
5/19/2006 5:53:52 PM
ServerName: osgleny
ServiceName: MSSQLSERVR
it doesn't matter if the server is stopped or running it always displays the same output.
and with the Email script i only edited the following:
'Note: change the email ID to your email ID
NotificationEmail="labejarjr@smg.sanmiguel.com.ph"
SMTPServer = "170.1.103.10"
and still the status.txt stays the same and no emails are sent. The problem cannot be the SMTP server because i use it with the xp_SMTP_sendmail and it works just fine..
anyway, thanks again for helping me
-
Leonardo,
I just tried setting this myself and it worked fine. Note that I'm using the second method (on page 2). My ServiceList.txt file looks like this:
KARLG,MSSQLServer
KARLG,SQLSERVERAGENT
Where KARLG is the name of my SQL Server. If I misname the server name, I get the same output as you.
Here's my output:
Check Heart beat started
19/05/2006 11:12:34
ServerName: KARLG
ServiceName: MSSQLServer
Status: OK
State: Running
19/05/2006 11:12:37
ServerName: KARLG
ServiceName: SQLSERVERAGENT
Status: OK
State: Running
Check Heart beat Completed
-
Thanks!! i'll give it another shot...
-
Hi i gave it another shot and i received this kind of output at the status.txt
ServerName:
ServiceName:
Error: -2147217375
Error:
5/22/2006 5:58:36 PM
ServerName:
ServiceName:
Error: -2147217375
Error:
5/22/2006 5:58:36 PM
ServerName:
ServiceName:
Error: -2147217375
Error:
5/22/2006 5:58:36 PM
The file kept on increasing it's size nonstop, unless you stop wscript in the task manager.
here is what i did with my servicelist.txt:
OSGLENNY,MSSQLServer
i even tried using the server's IP address like so,
172.21.40.37, MSSQLServer
still same error....the server name can't be wrong because if you try pinging my hostname OSGLENNY its fine. Or if you try to register my OSGLENNY server into another PC it WORKS fine. Even if you tried OSQL onto another PC and tried to access OSGLENNY, still it works fine.
is there any software requirements before doing this? because i just reformatted my PC and all of my other applications are all uninstalled...the only applications in my PC are:
MS OFFICE 2003 Pro
SQL Server 2000 Standard Ed.
Mcafee Vscan
Adobe Acrobat Reader Standard
and Lotus Notes 7.0.
thanks again.......
-
Leonardo,
In that case I honestly don't know. All I know is the script works and runs for me so I suggest you contact the author of that script and see if he as any more insight. You can send him a message from the same site.
-
ok...i'll try and contact him...i hope he replies though....anyways thank you for your support....
-
Try executing the script from the command line or double click in Windows rather than the Task Manager. If it works then, you have a permissions problem. Check to see what persona/login the task is running under. I looked at the script quickly and can't be sure, but I think it's likely the error occurs on the GetObject().
>L<
-
I dont execute the program in "task manager"...i "KILL" the program in the Task manager. Actually, i am executing the program in commandline and through windows(double-click the CheckHeartbeat.vbs). And it works...however, it produces an error in the status.txt (as i posted above). The status.txt keeps on increasing and increasing AND increasing its size, so if i delete the status.txt it will generate an error like this:
"Cannot delete status: It is being used by another person or program. Close any other programs that might be using the file and try again"
Now, the only way to delete it is to kill the program that is using it which is wscript.exe which can be "KILLED" in the task manager.
The problem can't be permissions because i'm running the program as an administrator
Thanks for the help anyway..i appreciate it
-
> The problem can't be permissions because i'm running the program as an administrator
That's true. I thought you were running the task in the Task Scheduler, however, not by double-clicking the script -- and when you do that, the task can be running in a different context <s>.
I will take another quick look at the script. For one thing, it should be easy to modify it so that you can exit quickly after an error rather than getting into this situation. I will write back when I've checked it.
>L<
-
Here you go. As the first person who recommended this script uses the second method (from page 2 of the article), that is the method I have revised below. Search for my initials (LSN) for comments and changes. Note that I have changed the vars holding hte name of the source folder and files, which you should change to suit yourself.
The changes I've made are, first, to give you some more detailed information about what is going on, and, second, to make sure you don't get into an endless loop. Also, I've cleaned up the open files and objects at the end of the run, but I don't think this is involved with your problem, you're not getting that far in the script.
In looking over your error information, and reading this script, I'm wondering if the problem is here: Do until ifile.AtEndOfLine . I say this because your values in the error message are blank.
Your empty values may indicate that you aren't opening the file you think you are opening, for some reason.
You may be opening/creating an empty file instead of the input file you expect. My changes should help you see this -- if not, they should help you see whether the problem is your version of CDO on that box (in which case you need to patch), or what exactly is going wrong.
HTH,
>L<
' LSN added some debugging
Dim debug debug = True
' start the main sub Main
'Objective: TO check the Heartbeat of all SQL Server services and send email 'if any state of service is not running 'Author: MAK 'Date: April 2, 2004
Sub Main on error resume next Set iFSO = CreateObject("Scripting.FileSystemObject") Set oFSO = CreateObject("Scripting.FileSystemObject")If iFSO Is Nothing or oFSO Is Nothing Then LSNWrite "Could not create Scripting.FSO object" Exit Sub End If
'Note: change the email ID to your email ID NotificationEmail="x@yahoo.com" SMTPServer = "111.222.444.111"
' LSN: I changed the file and folder information here ' you will want to change it to suit yourself InputFile="c:\temp\h\S.txt" Outputfile="c:\temp\h\Status.txt"
If Not iFSO.FileExists(InputFile) Then LSNWrite "Does not exist: " & inputfile, Nothing Exit Sub End If
Set ifile = iFSO.OpenTextFile(inputfile)
if err.number<>0 or iFile Is Nothing then LSNWrite "Could not open " & inputfile, Nothing Exit Sub End If
Set ofile = ofso.createTextFile(OutputFile, True)
if err.number<>0 or iFile Is Nothing then LSNWrite "Could not create or write to " & outputfile, Nothing Exit Sub End If
' ofile.writeline "Check Heart beat started" LSNWrite "Check Heart beat started", oFile
Do until ifile.AtEndOfLine servicelist= ifile.ReadLine strcomputer=left(servicelist,instr(servicelist,",")-1) Service =right(servicelist,len(servicelist)-instr(servicelist,",")) ofile.writeline Now() ofile.writeline "ServerName: " & strcomputer ofile.writeline "ServiceName: " & Service
Set objWMIService =nothing Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")if err.number<>0 then ofile.writeline "Error: " & err.number ofile.writeline "Error: " & err.description LSNWrite "Could not GetObject winmgmts", oFile Set objMessage = CreateObject("CDO.Message") If objMessage Is Nothing Then LSNWrite "Could not CreateObject CDO", oFile Exit Do else objMessage.Subject = "SQL Server HeartBeat" objMessage.Sender = NotificationEmail objMessage.To = NotificationEmail objMessage.TextBody = "Server :" & strcomputer & " " & err.description 'msgbox y 'The line below shows how to send a webpage from a remote site objMessage.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'Name or IP of Remote SMTP Server objMessage.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer 'Server port (typically 25) objMessage.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 objMessage.Configuration.Fields.Update objMessage.Send if err.number<>0 then LSNWrite "Could not send message", oFile Exit Do end If end if else Set colItems = nothing query="" query = "Select * from Win32_Service where name = '" & Service & "'" LSNWrite query, oFile Set colItems = objWMIService.ExecQuery(query,,48) if err.number<>0 then LSNWrite "Error: " & err.number, oFile LSNWrite "Error: " & err.description, oFile Set objMessage = CreateObject("CDO.Message") If objMessage Is Nothing Then LSNWrite "Could not CreateObject CDO", oFile Exit Do ELSE objMessage.Subject = "SQL Server HeartBeat" objMessage.Sender = NotificationEmail objMessage.To = NotificationEmail objMessage.TextBody = "Server :" & strcomputer & " " & err.description 'msgbox y 'The line below shows how to send a webpage from a remote site objMessage.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'Name or IP of Remote SMTP Server objMessage.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer 'Server port (typically 25) objMessage.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 objMessage.Configuration.Fields.Update objMessage.Send if err.number<>0 then LSNWrite "Could not send message", oFile Exit Do end If end if else For Each objItem in colItems ofile.writeline "Status: " & objItem.Status ofile.writeline "State: " & objItem.State If objitem.status <>"OK" or objitem.state <>"Running" then Set objMessage = CreateObject("CDO.Message") If objMessage Is Nothing Then LSNWrite "Could not CreateObject CDO", oFile Exit Do ELSE objMessage.Subject = "SQL Server HeartBeat" objMessage.Sender = NotificationEmail objMessage.To = NotificationEmail objMessage.TextBody = "Server :" & strcomputer & ":" & Service & " Status: " _ & objitem.status & " Status: " & objitem.state 'msgbox y 'The line below shows how to send a webpage from a remote site objMessage.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'Name or IP of Remote SMTP Server objMessage.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer 'Server port (typically 25) objMessage.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 objMessage.Configuration.Fields.Update objMessage.Send if err.number<>0 then LSNWrite "Could not send message", oFile Exit Do end If end if end if next end if end if LoopLSNWrite "Check Heart beat Completed", oFile
' LSN: bad manners not to clean up: IF Not oFile Is Nothing then oFile.Close End If
IF Not iFile Is Nothing then iFile.Close End If
SET oFile = Nothing SET iFile = Nothing SET oFSO = Nothing SET iFSO = Nothing SET objMessage = Nothing
End Sub
Sub LSNWrite(tsWriteWhat, toWhere) If NOT (toWhere is Nothing) Then toWhere.writeline tsWriteWhat End If If debug Then msgbox(tsWriteWhat) End If End Sub
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply