abbdan
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
My goal is to use built in alert messages to show what exactly occured.. ie:
exec usp_send_cdontsmail myserver@myworkplace.com',
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.
exec usp_send_cdontsmail myserver@myworkplace.com',
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.
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.
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.
ASKER
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.
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.
ASKER
I think you've given me enough info to help me solve the problem. Thanks for your help.
ASKER