Have You Been Told Today!!!!

Introduction

Have you ever ran a long query and been dragged away from your computer to return having forgotten all about it.

Or what about running a critical update overnight to then come back in the morning and find that it failed for some unforeseen reason?

Did you think to yourself, "if I only knew I could have done something about it". If you answered Yes, then you are not the only one. This article will demonstrate a very simple and effective method using VBScript of alerting you to when a query has completed and if the query execution status was successful or not.

The Solution

As a developer with basic skills in VBScript, the solution is very simple using DTS, which ships with SQL Server 7.0 onwards, the Execute SQL Task, and the ActiveX Script task. Lets first examine the ActiveX Scripts.

ActiveX Scripts

To keep things simple, we will create two ActiveX Script tasks. Either one will be executed depending on the result of the Execute SQL Task.

First, open DTS and create a new package. Add an ActiveX Script task which can be found on the left hand side Task panel or on the toolbar menu under Task. Right click the newly create task and view Properties. Provide a meaningful description such as "Successfully Completed" as this task it will generate the Successful notification.

Replace the Main() function with the following:

Function Main()
Dim objShell
Set objShell = CreateObject("WScript.Shell")
objShell.Run "net send  Query has successfully completed."
Main = DTSTaskExecResult_Success

End Function

For simplicity reasons, I have done away with error trapping and assuming the script will be successful.

Add another ActiveX Script task and this time the description will be "Completed With Errors".

Replace the Main() function with the following:

Function Main()
Dim objShell
Set objShell = CreateObject("WScript.Shell")
objShell.Run "net send  Query failed."
Main = DTSTaskExecResult_Success

End Function

Now, the next task will be to call your query from an Execute SQL Task.

Execute SQL Task

This task can be found from the same location as the ActiveX script. But before we do add this task,

we will need to create a connection to the database server. You can find the Connection types on the left hand side

Connection panel or on the toolbar menu under Connection. Be sure to specify the Username and Password that has rights

to execute the query.

Now, add the Execute SQL Task and provide a meaningful description for the task. Within Existing Connection, choose the

connection we just created. Under SQL Statement, paste your T-SQL statement to be executed. I prefer to turn my queries

into Stored Procedures as working within this text area can be a nuisance when debugging.

Making It Happen

Select the Execute SQL Task and holding the Control-key, select the ActiveX Script called "Successfully Completed". While the two items are highlighted, add a On Success Workflow connection

which can be found on the toolbar under Workflow.

To generate the Failed notification, select the Execute SQL Task and holding the Control-key, select the ActiveX Script called "Completed With Errors".

While the two items are highlighted, add a On Failure Workflow connection.

The final package should look something like this:

All that is left is to execute the package. You can now sit back and wait to be told.

Conclusion

While sending notifications from a scheduled job using the SQLServerAgent is a relatively simple step to create, as a developer

you may not be aware of all the issues involved such as the account that SQLServerAgent service runs under must be a member of the

Domain Users group, or would you know that the SQLServerAgent and EventLog services are even running.

This method does away with all the administration type issues that you as a developer do not need to be concerned about.

This example was a simple demonstration on how easy it is to plug a few DTS components together and include a few lines of VB code, but in my real world application, I have

extended this package to send notifications via SMS in the event that I am not sitting in front of my computer.