Writing to the history of a SQL Agent Job

Question

Having just created a SQL Agent Job I thought it would be handy to be able to write a little bit of information into the job's history, in case I wanted to check it later.

I was using VBScript in this instance, and noted that I could use the Error object to record info in the history, for example:

Err.Raise 999, "The code died"

If you right-click the job and choose 'View History' then the error number and description are there for all to see.

But what about successful completion? Can i Return a value somehow? It would be nice to say something like "Finished OK after actioning 34 files" or whatever.

I'd be interested in VBScript answers this time, but if you know how to do this with T-SQL or CmdExec that would be helpful to know, just in case. Thanks.

Answer 1

If you are using VBScript in a SQL job, it is probably better to save your VBScript as a .vbs file, and use a CmdExec job step to execute it, like so

CScript c:\test.vbs

Then, in your .vbs file, you can use the WScript.Echo command to display a message

WScript.Echo "This is a test"

If you were to double-click on your .vbs file, it would show a message box, but when executed using CScript.exe from the SQL job, the echo output should be written to the job's history for you to view.

Note that trying to use WScript.Echo directly in a SQL job with a job step of VBScript would not work.

If you were using a T-SQL step, you could simply use the PRINT statement, as this would get written to the history.