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 );
One thing you can try is to use sp_testlinkedserver
. You can also issue the OPENQUERY
using dynamic SQL (as Max correctly pointed out), to defer the parser validating the server name until runtime.
BEGIN TRY
EXEC sp_testlinkedserver N'server1';
EXEC sp_executesql N'SELECT * FROM OPENQUERY([server1],
''SELECT 1 AS c;'');';
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER(), ERROR_MESSAGE();
END CATCH;
PRINT 'We got past the Catch block!';
While this works equally well without sp_testlinkedserver
, that procedure can still be useful in preventing you from trying a whole bunch of code against that server...
Also, since if sp_testlinkedserver
fails it actually fails at compile time, you can defer that and still capture it by using dynamic SQL there, too:
BEGIN TRY
EXEC master.sys.sp_executesql N'EXEC sp_testlinkedserver N''server1'';';
...
END TRY
Best Answer
From the
CREATE PROCEDURE
documentation:If you need a try/catch, you'll need to execute the DDL using dynamic SQL so that it is in a separate batch: