I'm working with SQL Server 2012 Enterprise Edition SP2 on a Windows 7 32bits.

I have this code inside a stored procedure.

Begin try
    -- EXEC CLR Stored Procedure
    exec dbo.MyStoredProcedureCLR @baseUrl, @orderNumber
end try
begin catch
    Declare @message nvarchar(max);

    set @message = ERROR_MESSAGE()
    EXEC master..xp_logevent 60000, @message, informational
end catch

MyStoredProcedureCLR is a SQL CLR Stored Procedure that make a PUT to a RESTful web service.

Sometimes, I get a 404 Not Found exception, and I want to catch it and log it. This is the message I get:

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetNewCodesICODECLR": 
System.Net.WebException: Error en el servidor remoto: (404) No se encontró.
   en System.Net.HttpWebRequest.GetResponse()
   en StoredProcedures.GetNewCodesICODECLR(SqlString baseUrl, SqlString orderNumber, SqlString lineCode, Int64 quantity, Int64 codesPrinted, Int64 codesCleared)

But, when I get that exception, I get another exception in xp_logevent:

'Error executing extended stored procedure: Invalid Parameter Type'

How can I know if ERROR_MESSAGE() is a valid parameter? OR what's happening?

By the way, I'm running a that SQL CLR Stored Procedure inside a Service Broker Queue.

  • I see that you got the answer to the question in a recent post now deleted. It is also OK to add more details to the original post as you understand the problem better. In any case, you seem to have the answer. – RLF Oct 30 '14 at 14:57

From the xp_logevent topic in the documentation:

' message '
Is a character string with a maximum of 2048 characters.

So, I would change this:

Declare @message nvarchar(max);

To this:

Declare @message nvarchar(2048);

Or possibly even non-Unicode (the docs aren't clear):

Declare @message varchar(2048);

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):

EXEC master.sys.xp_logevent 60000, @message, 'INFORMATIONAL';

Your CLR procedure is not relevant here...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.