I have a slightly simple problem. Every day I run a stored procedure called DailySynchronisation, that connects to a linked server and selects some data, and then inserts/updates them in my database. Unfortunately, 2-3 times per month, this stored procedure stops working, because of many reasons:
(a) the ODBC driver is not behaving correctly
(b) the linked server is off
(c) people that load data in the linked server forget to upload them...
(d) many other reasons....
This SP runs once every day at 4am, and ends about 15minutes later. If it ends correctly, I get a statement in a table called DailyLog. If not, I do not get this statement. In any case, since this runs with a DTS, the DTS says that this job completed successfully, although it did not, because the SP ended at some point abnormaly. How can I capture such errors while the DTS runs, so that these can be reported???
Even though in the Job properties in the SQL Server Agent, it has options to report failure, it seems the system does not understand that a failure took place....
Thanks
Start Free Trial
