So I have some SQLCLR stored procedures, that use some .NET classes. I have a table in the database for exceptions, and I want to log all exceptions (except SqlExceptions of course) to that table.
I have the following example:
public
partial class UserDefinedFunctions{
[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "FillRow2", DataAccess = DataAccessKind.None, SystemDataAccess = SystemDataAccessKind.Read,TableDefinition =
"fld_colname NVARCHAR(4000)")] public static IEnumerable ExceptionTest(){
// Some example from a website
List<string> names = new List<string>(); using (SqlConnection connection = new SqlConnection("context connection=true")){
connection.Open();
SqlCommand sqlCommand = connection.CreateCommand();sqlCommand.CommandText =
"select NAME from dbo.SYSCOLUMNS"; SqlDataReader sqlDataReader = sqlCommand.ExecuteReader(); // No exceptions while (sqlDataReader.Read()){
names.Add(sqlDataReader.GetValue(0).ToString()); // This works fine
}
Exception e = new Exception("foo");
MyExceptionLoggingClass.Log(e,connection); // This doesn't}
return names;}
public static void FillRow2(object row, out string str2){
str2 = (
string)row;}
}
public
class MyExceptionLoggingClass{
public
static void Log(Exception ex, SqlConnection conn){
LogException(ex, conn);
}
private static void LogException(Exception ex, SqlConnection connection){
SqlCommand cmd = new SqlCommand("LogException", connection);cmd.CommandType = System.Data.
CommandType.StoredProcedure; SqlParameter param = new SqlParameter("@message", ex.Message);cmd.Parameters.Add(param);
param =
new SqlParameter("@stackTrace", "test");cmd.Parameters.Add(param);
param =
new SqlParameter("@localtime", DateTime.UtcNow);cmd.Parameters.Add(param);
try{
cmd.ExecuteNonQuery(); // always throws IOE, as does SqlContext.Pipe.ExecuteAndSend(cmd);
}
catch (InvalidOperationException){
return;}
}
The exception is: _COMPlusExceptionCode = -532459699 (couldn't find anything useful on that)
Ideally I don't want to be passing the connection to my class all the time, which is why I wanted to have overloaded methods, some that take the connection, others that don't, and open their own. Neither scenario works unfortunately.
If I run the code under LogException in my caller class (ExceptionTest) it works fine (I mean doesn't throw this exception, but I can't call from a SqlFunction - different issue, SQLCLR restriction).
I've been trying to debug this for a while now, and I'm running out of ideas, so any suggestion(s) would be highly appreciated.
Thanks!
Want to log CLR exceptions in SQL table
Aun
Phillippa
AnoopKM
first thing I tried and it works, the error messages gets logged.
USE
[AdventureWorks]GO
/****** Object: Table [dbo].[Exceptions] Script Date: 07/24/2006 18:38:54 ******/
SET
ANSI_NULLS ONGO
SET
QUOTED_IDENTIFIER ONGO
SET
ANSI_PADDING ONGO
CREATE
TABLE [dbo].[Exceptions]([ID] [int]
IDENTITY(1,1) NOT NULL,[Msg] [varchar]
(500) COLLATE Latin1_General_CS_AS NULL,[TimeStamp] [datetime]
NULL DEFAULT (getdate()))
ON [PRIMARY]GO
SET
ANSI_PADDING ONC# Proc:
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 usp_LogExceptions(){
using (SqlConnection oConn = new SqlConnection("context connection=true")){
try{
oConn.Open();
string strSQLUpdateInv = "UPDATE Production.ProductInventory SET Quantity= 'a' WHERE ProductID=1"; SqlCommand writeCommand = new SqlCommand(strSQLUpdateInv, oConn);writeCommand.ExecuteNonQuery();
}
catch(SqlException ex){
SqlCommand writeException = new SqlCommand("INSERT dbo.Exceptions SELECT '" + ex.Message.ToString().Replace("'","") + "',GETDATE()", oConn);writeException.ExecuteNonQuery();
}
finally{
oConn.Close();
}
}
}
};
Kolf
have u tested...
tsql
begin try
select dbo.udf_throwsUpException()
end try
begin catch
insert error_number etc...
end catch
SBorch
Dave2UisDave2Me
Actually it won't if the exception is from a SQLCLR function because you can't write (or call stored procedures) from them.
And even worse, if some .NET code that was called from a SQLCLR function throws an exception, you still can't write to SQL because the top of your stack has a SQLCLR function.
:(
WN3335
If what I'm trying to do is too weird or incorrect, then here is what I want to do:
From SQLCLR functions/stored procedures, I want to catch exceptions, and then have a method that will call a SQL (not CLR) stored procedures for entering them in a table.
I haven't been able to do this yet, so if someone has a working example or suggestion, I'd love to hear/see it/them.
Thanks in advance,
Peter Peng
is this what you are getting to for the SqlFunctions...
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'udf_LogException':
System.Data.SqlClient.SqlException: Invalid use of side-effecting or time-dependent operator in 'INSERT' within a function.
System.Data.SqlClient.SqlException:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages)
at Microsoft.SqlServer.Server.SmiEventSink_Default.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at UserDefinedFunctions.udf_LogException()
BobInIndy
M. Vavilov
I will but I thought we were talking about procs...
"SQLCLR stored procedures" from your first post.
Deuce BOI
Julian V
Yup. It's because you can't do any writes in a SqlFunction. Which, in this case, kinda sucks. And even worse, if I create a custom .NET object in the SQLFunction, and/or call some method, and _it_ throws an exception, and I use the same code as above to log that exception, you'll get the same error because the stack looks something like:
System.Data.SqlClient.SqlException:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages)
at Microsoft.SqlServer.Server.SmiEventSink_Default.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at MyClass.Method()
at UserDefinedFunctions.udf_LogException()
so it's a no-no becaus it all started with a function.
Han Qiao
a0-0b
Good suggestion though, I'm curious if that will even work