When BACKUP DATABASE
generates an error, it actually generates two. Unfortunately TRY/CATCH
is not capable of capturing the first error; it only captures the second error.
I suspect your best bet to capture the real reason behind a failed backup is to automate your backups through SQLCMD (with -o
to send output to a file), SSIS, C#, PowerShell etc. All of which will give you much greater control over capturing all of the errors.
The SO answer in the comment suggests using DBCC OUTPUTBUFFER
- while it's possible, this does not seem like child's play at all. Feel free to have fun with this procedure from Erland Sommarskog's site, but this still doesn't seem to work well in combination with TRY/CATCH
.
The only way I seemed to be able to capture the error message with spGET_LastErrorMessage
is if the actual error does get thrown. If you wrap it in a TRY/CATCH
the error gets swallowed and the stored procedure does nothing:
BEGIN TRY
EXEC sp_executesql N'backup that fails...';
END TRY
BEGIN CATCH
EXEC dbo.spGet_LastErrorMessage;
END CATCH
In SQL Server < 2012 you can't re-raise the error yourself, but you can in SQL Server 2012 and newer. So these two variations work:
CREATE PROCEDURE dbo.dothebackup
AS
BEGIN
SET NOCOUNT ON;
EXEC sp_executesql N'backup that fails...';
END
GO
EXEC dbo.dothebackup;
EXEC dbo.spGET_LastErrorMessage;
Or in 2012 and above, this works, but to a large degree defeats the purpose of TRY/CATCH
, since the original error still gets thrown:
CREATE PROCEDURE dbo.dothebackup2
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
EXEC sp_executesql N'backup that fails...';
END TRY
BEGIN CATCH
THROW;
END CATCH
END
GO
EXEC dbo.dothebackup2;
EXEC dbo.spGET_LastErrorMessage;
In both of these cases, the error is still thrown to the client, of course. So if you're using TRY/CATCH
to avoid that, unless there is some loophole I'm not thinking of, I'm afraid you'll have to make a choice... either give the user the error and be able to capture details about it, or suppress both the error and the actual reason.
The error in the EXEC
part of the INSERT-EXEC
statement is leaving your transaction in a doomed state.
If you PRINT
out XACT_STATE()
in the CATCH
block it is set to -1
.
Not all errors will set the state to this. The following check constraint error goes through to the catch block and the INSERT
succeeds.
ALTER PROCEDURE test -- or create
AS
BEGIN try
DECLARE @retval INT;
DECLARE @t TABLE(x INT CHECK (x = 0))
INSERT INTO @t
VALUES (1)
SET @retval = 0;
SELECT @retval;
RETURN( @retval );
END try
BEGIN catch
PRINT XACT_STATE()
PRINT ERROR_MESSAGE();
SET @retval = -1;
SELECT @retval;
RETURN( @retval );
END catch;
Adding this to the CATCH
block
IF (XACT_STATE()) = -1
BEGIN
ROLLBACK TRANSACTION;
END;
Doesn't help. It gives the error
Cannot use the ROLLBACK statement within an INSERT-EXEC statement.
I don't think there is any way of recovering from such an error once it has happened. For your specific use case you don't need INSERT ... EXEC
anyway though. You can assign the return value to a scalar variable then insert that in a separate statement.
DECLARE @RC INT;
EXEC sp_executesql
N'EXEC @RC = test',
N'@RC INT OUTPUT',
@RC = @RC OUTPUT;
INSERT INTO @t
VALUES (@RC)
Or of course you could restructure the called stored procedure so that it doesn't raise that error at all.
DECLARE @RetVal INT = -1
IF OBJECT_ID('PrintMax', 'P') IS NULL
BEGIN
EXEC('create procedure PrintMax as begin print ''hello world'' end;')
SET @RetVal = 0
END
SELECT @RetVal;
RETURN( @RetVal );
Best Answer
This is actually far more straightforward than I thought. Assuming that all of the variables used above are declared elsewhere, the simple answer as to why the catch isn't working is that it is working, but it has syntax errors internally that result in a return value of NULL.
Consider this line:
Unless you declared @errorstring like this:
Or assigned
@errorstring
as some value, the variable is currently set to NULL. And, by default, concatenating anything to NULL returns... NULL.Changing the code here to
Sets the variable to the correct value.