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 );
Overall, no, you did not do anything wrong with your .NET code. There is a minor technical issue, but we will get to that in a moment.
I was able to reproduce this (both error and non-error scenarios) in SQL Server 2008 R2 RTM. At first, in the "non-error" scenario, I was getting another error for:
specified cast is invalid
This error was a result of the query passing back an INT
and the C# code attempting to cast it to a bool
. I changed the query to be:
... then CONVERT(BIT, 1) else CONVERT(BIT, 0) end ...
That forces the datatype of the value to be returned to be something that can be cast into bool
. This was the "minor error" that I was referring to, and I consider it "minor" and not really an error because I suspect your code originally attempted to cast into int
and returned a SqlInt32
since you claim that passing in an empty string allowed it to return successfully. There is no way this would ever return successfully, even with an empty string instead of NULL
for the input value, if your code was attempting to cast to bool
and return SqlBoolean
. And yes, I could have changed the cast to be (int)
and the return type to be SqlInt32
, but this was less of a structural change.
I tested several types of changes: removing the execution, removing the @d
parameter and hard-coding the null
into the query but still having the SqlParameter
defined, removing the @d
parameter and the SqlParameter
, etc. What it finally came down to is this: merely the existence of a SqlParameter
of a LOB type (i.e. XML
, VARCHAR(MAX)
, NVARCHAR(MAX)
, VARBINARY(MAX)
), even if unused in the query, caused the "severe error" when passing in a NULL
. However, if any of those MAX
types were changed to be their maximum non-MAX lengths (i.e. 8000, 4000, and 8000 respectively), then there was no error when passing in a NULL
. Hence the following reduced code should be enough to reproduce the error when passing in a NULL
to the SQLCLR scalar function:
using (var cmd = new SqlCommand(@"SELECT 1;", c))
{
var p = cmd.Parameters.Add("@d", SqlDbType.NVarChar, (int)SqlMetaData.Max);
cmd.ExecuteScalar();
}
Now, I was not able to reproduce the error with the original code in SQL Server 2012 SP3 or SQL Server 2014 SP1 :-). Therefore, I suspect that this was an issue with CLR 2.0 and/or the .NET Framework versions associated with CLR 2.0 (i.e. "2.0", "3.0", and "3.5"). OR, another aspect to consider is that it with this failing ( declare @res bit; set @res = testing.TestTest(null);
) while this works ( declare @res bit; set @res = (select testing.TestTest(null));
) that could point to a problem with the Query Optimizer and not with the CLR version. Or maybe it is a combination of the two. Or perhaps it is Descartes's pesky Evil Demon. Either way. ( ** please see comment at the end )
If you are able to move up to SQL Server 2012 or newer, then the problem should "magically" disappear. BUT, if stuck on SQL Server 2008 / 2008 R2, I was able to work around the issue by declaring the parameter to be a non-MAX type if the input is NULL
, which should be fine since there won't be any risk of truncation when passing through a NULL
. And, in the case of the input parameter datatype of the T-SQL object being called being XML
, it still works to define the parameter datatype in the .NET code as NVARCHAR(4000)
as it will implicitly convert that into XML
when called. The following code worked for me in SQL Server 2008 R2 when passing in a NULL
:
SqlParameter _XmlData;
if (Data.IsNull)
{
// Parameter can't be a LOB type if input is NULL
_XmlData = new SqlParameter("@d", SqlDbType.NVarChar, 4000);
_XmlData.Value = DBNull.Value;
}
else
{
_XmlData = new SqlParameter("@d", SqlDbType.Xml);
_XmlData.Value = Data.Value;
}
cmd.Parameters.Add(_XmlData);
** @MartinSmith left this illuminating comment:
Specifically the error seems to be "Access violation reading location 0x0000000000000000" inside sqlservr.exe!CClrXvarProxy::EnsureBufferInit
so looks like a null pointer bug but googling those method names doesn't return any KB article.
Best Answer
From the
xp_logevent
topic in the documentation:So, I would change this:
To this:
Or possibly even non-Unicode (the docs aren't clear):
Also, severity should be delimited as a proper string (in spite of the terrible example in the docs), and in upper case (in case it matters on a case-sensitive collation, who knows what those cowboy XP coders might have done):
Your CLR procedure is not relevant here...