July 19, 2004 at 10:42 am
Hi.
I am trying to set-up an alert that will fire when a DTS package fails and a error message appears in the Windows Event Log. I have no problem getting the error to appear in the event logm but cannot get the alert to catch it.
I am at my first glance into setting up an alert. Books on-line doesn't help much.
Can anybody help me into this?
thanks!!
July 19, 2004 at 11:12 am
You could use a trigger on sysjobhistory to check for a job failure (if the dts is executed from there). You can then trap the raiserror as an alert.
CREATE trigger trg_stepfailures
on sysjobhistory
for insert
as
declare @strcmd varchar(1000),@strRecipient varchar(500),@strMsg varchar(3000), @subject varchar(100)
if exists (select * from inserted where run_status = 0 and step_name <> '(job outcome)')
begin
select @strMsg = @@servername + ' Job ' + sysjobs.name +char(13) + char(13) + 'Step ' +
inserted.step_name +char(13)+char(13)+ 'Message ' + inserted.message
from inserted
join sysjobs
on inserted.job_id = sysjobs.job_id
where inserted.run_status = 0
raiserror (@strMsg, 16, 10) with log
end
GO
Or you could put a trigger on the sysdtssteplog table, which would look for a failed step in a dts package.
CREATE trigger trg_dtsfailures
on sysdtssteplog
for insert
as
declare @strcmd varchar(1000),@strRecipient varchar(500),@strMsg varchar(3000), @subject varchar(100)
if exists (select * from inserted where errorcode != 0)
begin
select @strMsg = @@servername + ' DTS ' + d.name +char(13) + char(13) + 'Failed'
from inserted
join sysdtspackagelog d
on inserted.lineagefull = d.lineagefull
where inserted.errorcode != 0
raiserror (@strMsg, 16, 10) with log
end
GO
July 19, 2004 at 11:57 am
Thanks for the script.
I will try it, but is there a reason I can't trap the message already created in the event log? Is it because it is not present in the SQL error log?
Y
July 19, 2004 at 1:09 pm
Can't get the trigger to work. I found in BOL.
Note Because SQL Server does not support user-defined triggers on system tables, it is recommended that no user-defined triggers be created on system tables.
The sysdtssteplog is a system table. Can we still do the trigger?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy