Using a stored procedure in NLog against a target database

Question

I had a problem using a stored procedure instead of an SQL INSERT statement when using NLog in a C # web application. The Logger connection string is correctly configured in Web.config and works correctly when commandText is replaced with an SQL statement. I would appreciate a hint in the right direction. In this example, the stored procedure is in the Logs schema and is called LogError.

<targets> <target xsi:type="Database" name="dberrorlog" connectionStringName="Logger" keepConnection="true" commandText="[Logs].[LogError]" > <parameter name="@ProgName" layout="MyAppName"/> <parameter name="@CompName" layout="${machinename}"/> <parameter name="@LogLevel" layout="${level}"/> <parameter name="@UserName" layout="${identity}"/> <parameter name="@Error" layout="${exception:format=Message}"/> <parameter name="@SourceObj" layout="${exception:format=Method}"/> <parameter name="@StackTrace" layout="${exception:format=StackTrace}"/> </target> </targets> <rules> <logger name="*" minlevel="Error" writeTo="dberrorlog" /> </rules> 

Answer 1

From this NLog message, try using text to execute a stored procedure:

 commandtext="exec AddActivityLog @ApplicationName, @ApplicationTime, @Severity, @Logger, @SaxoID, @EventID, @Message, @URL, @URLReferrer, @RemoteAddress, @Callsite, @CurrentUICulture, @ThreadIdentity, @WindowsIdentity, @MachineName, @ProcessID, @ThreadID, @ThreadName, @Stacktrace, @Exception, @Cookie, @FormVariables, @QueryString, @HTTPUserAgent" 

Side note: Klaus Ratha's answer will not be displayed in my browser, so I had to look at the source of the page to see the configuration that he posted.

Answer 2

Please note that while the @JeffOgata solution works, it may not be the way you want to solve the problem.

Instead, you can do something like this:

 commandText="AddActivityLog" commandType="StoredProcedure" 

This way you don’t have to worry about correctly formatting the EXEC request.