NLog - database configuration - exception on German SQL Server - any ideas how to work around?

Question

Background

I am using NLog version 2.0.1, which I believe is the latest version, from a .Net 4.0 Console application (written using Visual Studio 2012).

The application is simply:

namespace NLogConsoleApplication
{
    class Program
    {
        static void Main()
        {
            NLog.LogManager.GetCurrentClassLogger().Info("A testypoos");
        }
    }
}

I have the following NLog.config file:

<?xml version="1.0" encoding="utf-8" ?>
<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      throwExceptions="true"
      autoReload="true"  
      internalLogFile="C:\Users\Public\nlog.txt"
      internalLogLevel="Debug">
  <targets>    
    <target type="Database" name="tl" >
        <connectionString>data source=.\;initial catalog=NLogTest;integrated security=false;User ID=iwillnotfallforthisone;Password=goodtry</connectionString>
        <commandText>
          insert into TestLog2 ([LogDate], [LogLevel], [LogMessage]) values (@logDate, @logLevel, @logMessage);
        </commandText>
        <parameter name="@logDate" layout="${date}"/>
        <parameter name="@logLevel" layout="${level}"/>
        <parameter name="@logMessage" layout="${message}"/>
    </target>
  </targets>
  <rules>    
    <logger name="*" minlevel="Trace" writeTo="tl" />
  </rules>
</nlog>

The TestLog2 table is created like so:

CREATE TABLE [dbo].[TestLog2](
    [LogId] [bigint] IDENTITY(1,1) NOT NULL,
    [LogDate] [datetime] NOT NULL,
    [LogLevel] [nvarchar](20) NOT NULL,
    [LogMessage] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_TestLog2] PRIMARY KEY CLUSTERED 
(
    [LogId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

When I run the application I get this exception (I Google translated this - hopefully it is understandable):

Unhandled Exception: NLog.NLogRuntimeException: Exception occurred in NLog --- > System.Data.SqlClient.SqlException:During the conversion of a nvarchar data type to a datetime data type, the value is out of range.
The statement has been terminated.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   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.WriteEventToDatabase(LogEventInfo logEvent)
   at NLog.Targets.DatabaseTarget.Write(LogEventInfo logEvent)
   at NLog.Targets.Target.Write(AsyncLogEventInfo logEvent)
   --- End of inner exception stack trace ---
   at NLog.LoggerImpl.<>c__DisplayClass1.<Write>b__0(Exception ex)
   at NLog.Internal.SingleCallContinuation.Function(Exception exception)
   at NLog.Targets.Target.Write(AsyncLogEventInfo logEvent)
   at NLog.Targets.Target.WriteAsyncLogEvent(AsyncLogEventInfo logEvent)
   at NLog.LoggerImpl.WriteToTargetWithFilterChain(TargetWithFilterChain targetListHead, LogEventInfo logEvent, AsyncContinuation onException)
   at NLog.LoggerImpl.Write(Type loggerType, TargetWithFilterChain targets, LogEventInfo logEvent, LogFactory factory)
   at NLog.Logger.Info(String message)
   at NLogConsoleApplication.Program.Main() in c:\xxxxxxx\Program.cs:Line 7.

If I change the LogDate column to an nvarchar it works fine but I really do not want the column to be a string as I would like to be able to sort and search the table based on the date.

Notes

• I am running this on a German version of Windows Server 2008 R2 standard connecting to a German version of SQL Sever 2012.
• If I connect to an English SQL Server 2012 instance it works fine.
• I checked the nlog.txt file and there seems to be no further information in there (just the same exception trace)

Question

Maybe it is just a bug in NLog or maybe it is just a configuration that I can change or there is a clever workaround so, does anyone know how I will be able to get NLog to put a DateTime into my database for the timestamps?

Answer 1

I would guess that the format of the datetime from NLog is MM/dd/yyyy and the German SQL Server is expecting dd/MM/yyyy. You can specify the date format explicitly with:

<parameter name="@logDate" layout="${date:format=yyyy-MM-dd}"/>

SQL Server ought to be able to parse the date and insert it appropriately regardless of the locale expecting MM/dd or dd/MM.

A similar question

NLog Documentation