You could create a job that checks the msdb.dbo.sysjobhistory table every minute (or however frequently you want). You might want to implement a queue table so you only ever send the message for any single instance failure once.
USE msdb;
GO
CREATE TABLE dbo.ReportServerJob_FailQueue
(
job_id UNIQUEIDENTIFIER,
run_date INT,
run_time INT, -- horrible schema, just matching sysjobhistory
sql_message_id INT,
sent BIT NOT NULL DEFAULT 0,
PRIMARY KEY (job_id, run_date, run_time)
);
So then your code, that you can schedule in a job, becomes:
INSERT dbo.ReportServerJob_FailQueue
(job_id, run_date, run_time, sql_message_id)
SELECT job_id, run_date, run_time, sql_message_id
FROM msdb.dbo.sysjobhistory AS h
WHERE step_id = 0
AND run_status = 0
AND EXISTS
(
SELECT 1 FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.syscategories AS c
ON j.category_id = c.category_id
WHERE j.job_id = h.job_id
AND c.name = 'Report Server'
)
AND NOT EXISTS
(
SELECT 1 FROM dbo.ReportServerJob_FailQueue
WHERE job_id = h.job_id
AND run_date = h.run_date
AND run_time = h.run_time
);
Now I assume you want to send an individual e-mail for each failure, so this could be part of the job as well (or part of a different job, though that isn't necessarily wise):
DECLARE
@subject NVARCHAR(4000),
@body NVARCHAR(4000),
@name SYSNAME,
@id UNIQUEIDENTIFIER,
@date INT,
@time INT,
@msg INT;
DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR SELECT q.job_id, q.run_date, q.run_time, q.sql_message_id, j.name
FROM dbo.ReportServerJob_FailQueue AS q
INNER JOIN msdb.dbo.sysjobs AS j
ON q.job_id = j.job_id
WHERE q.sent = 0;
OPEN c;
FETCH NEXT FROM c INTO @id, @date, @time, @msg, @name;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @subject = 'Report Server job ' + @name + ' failed.';
SET @body = 'Error number: ' + RTRIM(@msg);
BEGIN TRY
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'default', -- you may need to change this
@recipients = 'foo@bar.com', -- you will need to change this
@subject = @subject,
@body = @body;
UPDATE dbo.ReportServerJob_FailQueue
SET sent = 1
WHERE job_id = @id
AND run_date = @date
AND run_time = @time;
END TRY
BEGIN CATCH
PRINT 'Will have to try that one again later.';
END
FETCH NEXT FROM c INTO @id, @date, @time, @msg, @name;
END
CLOSE c; DEALLOCATE c;
There are some other options too:
- pull in sysjobhistory.message
- look at individual steps that failed
- only send a message for any job once in n minutes/hours even if there are multiple failures
- send a single e-mail with a list of all the jobs that have failed, instead of an e-mail for each failure
- you might want to include the run_date and run_time in the message, since the e-mail may not be sent or received quickly enough to be an accurate measure of when the job actually failed (I did not include it here because their horrible data type choices make formatting that stuff a royal PITA)
- you'll probably want to clean up old rows after some time, so a purge command may be desired as well
If Database Mail isn't already set up, please see this tutorial.
You could also use 3rd party tools (e.g. SQL Sentry) that will make a lot of this simpler. Full disclosure: I work for SQL Sentry.
Since I don't actually see any transactions created within your SP I can only guess. But I can tell you that transactions inside a stored procedure are .. tricky.
If you have nested transactions, for example one created outside a stored procedure and one created inside the stored procedure, SQL may have a transaction count of two but it's still really only one transaction. If you perform a rollback within the stored procedure it will rollback the entire transaction (even the part outside of the SP) and then when you leave the stored procedure you'll get an error similar to what you are seeing.
In order to avoid this you have to use a SAVE TRANSACTION
statement to create a savepoint in the transaction and then roll back to that point.
I've got a simple demo here. The SP part of it looks like this:
CREATE PROCEDURE usp_TransactionTest @Value int, @RollBack bit
AS
BEGIN
BEGIN TRANSACTION
SAVE TRANSACTION TranTest
INSERT INTO tb_TransactionTest VALUES (@Value)
IF @Rollback = 1
-- Roll back to the saved point.
-- The transaction is not closed and
-- @@TRANCONT is not changed.
ROLLBACK TRANSACTION TranTest
-- Close the transaction created at the beginning of the SP
COMMIT
END
GO
Best Answer
You are correct that is the best way for individual server.
Sample code for notification when a job fail.
You can send email for success/failure or both.
Books online has details.
There are large shops where instead of setting up notification for each SQL Agent jobs, all failed jobs will be collected at xx minute interval and send one email/raise ticket in the form of a report.