What's error 6522? It's the error that you always get from an unhandled exception in a SQLCLR stored procedure. If I write a SQLCLR error that throws an unhandled exception (let's write one on purpose):
[SqlProcedure]
public static void ErrorExecute()
{
SqlCommand cmd = SqlContext.GetCommand();
// everyone knows Bob can't type
cmd.CommandText = "select * from authorss";
SqlContext.GetPipe().Execute(cmd);
}
using it from T-SQL:
execute errorexecute
print @@error
Yields:
Msg 208, Level 16, State 1, Line 0
Invalid object name 'authorss'.
Msg 6522, Level 16, State 1, Procedure ErrorExecute, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'ErrorExecute':
System.Data.SqlServer.SqlException: Invalid object name 'authorss'.
at System.Data.SqlServer.Internal.StandardEventSink.HandleErrors()
at System.Data.SqlServer.Internal.RequestExecutor.HandleExecute(EventTranslator eventTranslator, SqlConnection conn, SqlTransaction tran, ClrLevelContext clrlvlctxtPipe, UrtExecutionType uetType, InternalResultSetOptions irsoOpts, CommandBehavior eBehavior, Object& objResult)
at System.Data.SqlServer.Internal.RequestExecutor.ExecuteToPipe(SqlConnection conn, SqlT.
6522
Not that @@ERROR returns 6522, NOT 208. If you write an equivalent bad T-SQL proc:
create procedure errortsql
as
— cant type any better in TSQL
select * from authorss
go
execute errortsql
print @@error
returns 208.
Why does this matter? Say that I have a T-SQL stored procedure (not this one, obviously) that I want to replace with a SQLCLR equivalent that say, runs faster. Say the procedure is used in 20 places in my application, each with semantics that check for specific values of @@ERROR. Won't work the same.
You can't catch the exception in SQLCLR and throw the "correct" one (using a SqlCommand with CommandText of RAISERROR(…)) either, because you can't throw a 208 error (or any other system error) with RAISERROR. So I'll have to change each one of my caller procs too when I switch to SQLCLR. The only current workaround is to change to the new T-SQL try-catch syntax
BEGIN TRY
EXECUTE errorexecute
END TRY
BEGIN CATCH
— prints 208, whew…
PRINT CONVERT(varchar(10), error_number())
END CATCH
Hopefully this will be changed or a workaround in SQLCLR will be available in the next beta. I want @@ERROR to somehow return 208, not 6522. Or this will be a great motivator for everyone to change to T-SQL TRY-CATCH.