Event alert definition - which one do I use for a failed attempt to pull data from an access database?

I'm trying to determine the event alert definition to use for a failed attempt to pull data from an access database?

I use this alert to trigger a job that sends an SMTP email containing the error message.  I want it to cover a wide range of possible problems with DTSing an MSAccess database.  I need just one alert to perform this action.  Any help would be appreciated.
Who is Participating?
When you speak about alerts, you mean SQL Server Agent Notifications. Real SQL Server Alerts are used for general server monitoring. You can trigger special UD SQL errors, but you cannot monitor DTS errors only.

1. DTS is run in one SQL Server Agent job step, you can add one more step writting the error into a log table.
Actions on 1st step:
On success:Quit with success
On error:Continue on next step
Actions on 2nd step:
On success:Quit with success
On error:Quit with error

2. Schedule a new repeating SQL Server Agent job monitoring the log table and sending e-mails. It can be EXE,VBS,T-SQL,DTS, simply anything. E-mails can be sent using On error:Quit with error method, or using customized ActiveX messages.
abbdanAuthor Commented:
To make sure I understood... there is no way to trigger a job when a DTS error occurs other than posting an error to a table and monitoring that table for new contents?  Gosh I hope thats not true.
abbdanAuthor Commented:
My goal is to use built in alert messages to show what exactly occured.. ie:

exec usp_send_cdontsmail myserver@myworkplace.com', myemail@address.com', 'SQL Jobs Error Message', '

Error: [A-ERR]
Severity: [A-SEV]
Date: [STRTDT]
Time: [STRTTM]
Database: [A-DBN]
Message: [A-MSG]

Check the [SRVR] SQL Server ErrorLog and the Application event log on the server for additional details'

Thing is the bracket messages require that the error that occured actually be the trigger and not another step in the job after the error occured.  Theres bound to be a way to do this.  I'm hoping someone has an idea that will let me do this.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

1. DTS has built-in logging (SQL and/or TXT) and on-error branches.
2. But you run DTS from scheduler SQL Server Agent. If an error occurs BEFORE you run DTSRUN.EXE or in DTS ENGINE, it cannot catched by DTS.
3. So you can define multilevel monitoring or just monitor SQL Server Agent jobs.
abbdanAuthor Commented:
I'd be happy with monitoring SQL Server Agent Jobs... but I'd like to do it with an Alert??.. that way when the error occurs the alert messages will work.  I'm not concerned that it captures more than DTS jobs.. in fact that could be a plus.  What do I need to do to get this to work?
With Alerts, you are not able to trace error source. For example,you get "Division by zero" alert, the same for

1. DTS error
2. EM query error
3. Application error
4. SELECT 1/0 user testing
and so on

Alert can be used only for triggering defined action: For error of Severity 10, Message with "DTS" in it send every 1 hour e-mail to admin. But DTS has no "DTS" errors, there are indifferent SQL errors. You are able to use "DTS" alert triggering, if you generate user defined errors in "On error" branches of DTS packages.
abbdanAuthor Commented:
I think you've given me enough info to help me solve the problem.  Thanks for your help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.