Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Have You Been Told Today

By Frank Loschiavo,

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.

Total article views: 6122 | Views in the last 30 days: 0
 
Related Articles
FORUM

SSIS ActiveX script task remote execution

Execute the ActiveX script on other server than the one the package is running.

FORUM

Need Solution for ActiveX script in SSIS

Convert activex script in DTS to SSIS

FORUM

Calling Windows service from ACTIVEX script in DTS Package

Calling Windows service from ACTIVEX script in DTS Package

FORUM

DTS - ActiveX Script concern

ActiveX concern regarding GetExecutionErrorInfo method/function

FORUM

DTS activeX time out.

DTS activeX time out.

Tags
dts    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones