Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

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.
0
abbdan
Asked:
abbdan
  • 4
  • 3
1 Solution
 
ispalenyCommented:
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.
0
 
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.
0
 
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.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
ispalenyCommented:
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.
0
 
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?
0
 
ispalenyCommented:
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.
0
 
abbdanAuthor Commented:
I think you've given me enough info to help me solve the problem.  Thanks for your help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now