Using a stored procedure for the DB Target

[BardMorgan]

I'm trying to use NLog for the first time and having problems getting it to work.  I've turned on the tracing and the exception being thrown is as follows:

2009-04-11 12:42:22.4191 Error Target exception: System.Data.SqlClient.SqlException: Incorrect syntax near 'AddActivityLog'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at NLog.Targets.DatabaseTarget.DoAppend(LogEventInfo logEvent) in d:\Work\NLog\src\NLog\Targets\Database.cs:line 377
   at NLog.Targets.DatabaseTarget.Write(LogEventInfo logEvent) in d:\Work\NLog\src\NLog\Targets\Database.cs:line 316
   at NLog.LoggerImpl.Write(Type loggerType, TargetWithFilterChain targets, LogEventInfo logEvent, LogFactory factory) in d:\Work\NLog\src\NLog\LoggerImpl.cs:line 128


The "Incorrect syntax" message, in my experience, comes when the SqlCommand object doesn't have the CommandType property set to StoredProcedure when calling a stored procedure.  How do I tell NLog that the CommandText is a stored procedure name and not an SQL command?

Thanks all,

Morgan

[Claus Rathje]

Hi,

My target looks like this and works like a charm:

        <target xsi:type="Database"
                connectionString="${ConnectionString}"
                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">          
          <parameter name="@ApplicationName" layout="${ApplicationName}" />
          <parameter name="@ApplicationTime" layout="${date:format=yyyy-MM-dd HH\:mm\:ss.fff}" />
          <parameter name="@Severity" layout="${level}" />
          <parameter name="@Logger" layout="${logger}" />
          <parameter name="@SaxoID" layout="${aspnet-user-identity}" />
          <parameter name="@EventID" layout="${event-context:EventID}" />
          <parameter name="@Message" layout="${message}" />
          <parameter name="@URL" layout="${aspnet-request:item=URL}" />
          <parameter name="@URLReferrer" layout="${aspnet-request:item=HTTP_REFERRER}" />
          <parameter name="@RemoteAddress" layout="${aspnet-request:item=REMOTE_ADDR}" />
          <parameter name="@Callsite" layout="${callsite}" />
          <parameter name="@CurrentUICulture" layout="${currentUICulture}" />
          <parameter name="@ThreadIdentity" layout="${identity}" />
          <parameter name="@WindowsIdentity" layout="${windows-identity}" />
          <parameter name="@MachineName" layout="${machinename}" />
          <parameter name="@ProcessID" layout="${processid}" />
          <parameter name="@ThreadID" layout="${threadid}" />
          <parameter name="@ThreadName" layout="${threadname}" />
          <parameter name="@Stacktrace" layout="${stacktrace:format=Raw}" />
          <parameter name="@Exception" layout="${exception:format=ToString}" />
          <parameter name="@HTTPUserAgent" layout="${aspnet-request:item=HTTP_USER_AGENT}" />
        </target>

Hope that it helps.


Claus