xp_logevent Invalid Parameter Type

Question

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ó.
System.Net.WebException: 
   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.

Answer 1

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...