Hi.
Is it possible to transmit custom data from SQL 2005 to the client via Exception.Data[key]=value?
The idea
I have a number of SPs that accept userId parameter which must be verified. I have written a simple CLR SP (check_userid_valid) which performs the verification of the userId parameter's value. If the parameter's value is considered invalid an exception is thrown. When the exception (System.Exception) is instantiated additional data is added via the Data property of the exception:
System.Exception ex = new System.Exception();
ex.Data["Source"] = "check_userid_valid"
So, any SP that calls the check_userid_valid with invalid userId is expected to "crash" and the exception with all the additional data is expected to be propagated back to the client which, in turn, could read the data.
Unfortunately, it seems that the ex.Data contains only entries put by the MS SQL 2005 server itself, eliminating the rest.
The question is: how can I supply additional exception data with the exception I do throw from my CLR code on the server-side, so that is can be consumed at the client-side.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CustomException()
{
try
{
//try code goes here
System.Exception firstEx = new System.Exception("first exception");
throw firstEx;
}
catch (System.Exception ex)
{
}
finally {
System.Exception secondEx = new System.Exception("check_userid_valid");
throw secondEx;
}
}
};
