NLog Database Target Not Finding Table

Question

I am using NLog 1.0 and have the following configuration file:

<?xml version="1.0"?>
<nlog autoReload="true" throwExceptions="true">

<targets>
    <!--<target name="console" xsi:type="ColoredConsole"
        layout="${date:format=HH\:mm\:ss}|${level}|${stacktrace}|${message}" />

    <target name="file" xsi:type="File" fileName="${basedir}/Logs/Site.log"
        layout="${date}: ${message}" />

    <target name="eventlog" xsi:type="EventLog" source="My App" log="Application"
        layout="${date}: ${message} ${stacktrace}" />-->

    <target name="database" type="Database" connectionStringName="xxxxxxx">

        <dbprovider>mssql</dbprovider>

        <commandText>
            INSERT INTO Logs (Message, Level, CreatedOn) VALUES (@message, @level, @createdOn);
        </commandText>

        <parameter name="@message" layout="${message}" />
        <parameter name="@level" layout="${level}" />
        <parameter name="@createdOn" layout="${date}" />
    </target>
</targets>

<rules>
    <logger name="*" minlevel="Debug" appendTo="database" />

    <!--<logger name="*" minlevel="Info" writeTo="file" />
    <logger name="*" minlevel="Fatal" writeTo="eventlog" />-->
</rules>

This is almost a copy paste from the codeplex example supplied with nlog. In my global.ascx I have it log "Application Started!" just to test logging is working however this is where the problems begin (by the way the file target works well it's just the DB target):

System.Data.SqlClient.SqlException: Invalid object name 'Logs'.

Seems pretty straight forward to me, must be a typo. I check SQL Profiler and get the following:

exec sp_executesql N'
            INSERT INTO Logs (Message, Level, CreatedOn) VALUES (@message, @level, @createdOn);
        ',N'@message nvarchar(20),@level nvarchar(4),@createdOn nvarchar(19)',@message=N'Application Started!',@level=N'Info',@createdOn=N'06/25/2010 23:59:30'

My table create code is below:

CREATE TABLE Logs (
Id INT IDENTITY(1, 1) NOT NULL,
[Level] VARCHAR(5) NOT NULL,
[Message] VARCHAR(4095) NOT NULL,
CreatedOn DATETIME NOT NULL,

CONSTRAINT PK_Logs PRIMARY KEY (Id)
)

Any ideas, surely I am missing something simple?

---

I managed to get this working and have a feeling my connection string is the problem. When the commandtext is changed to:

USE xxxx INSERT INTO dbo.Logs (Message, Level, CreatedOn) VALUES (@message, @level, @createdOn);

All works, I will try some connection strings if you know any that work with NLog let me know please!

Answer 1

Check the namespace of the database table. You can prefix the table name with "dbo" (for Database Owner) as follows:

CREATE TABLE dbo.Logs (
[Id] INT IDENTITY(1, 1) NOT NULL,
[Level] VARCHAR(5) NOT NULL,
[Message] VARCHAR(4095) NOT NULL,
[CreatedOn] DATETIME NOT NULL,

CONSTRAINT PK_Logs PRIMARY KEY (Id)
)

Your table might have a namespace of someName.logs when you created it. Then you can specify the full name in the XML file as follows:

INSERT INTO dbo.Logs (Message, Level, CreatedOn) VALUES (@message, @level, @createdOn);

Answer 2

To resolve the problem I added the "dbDatabase" parameter to the target like so:

<target name="database" type="Database" connectionStringName="MediaLibrary" dbDatabase="MediaLibrary">
        <commandText>
            INSERT INTO dbo.Logs (Message, Level, CreatedOn) VALUES (@message, @level, @createdOn);
        </commandText>
        <parameter name="@message" layout="${message}" />
        <parameter name="@level" layout="${level}" />
        <parameter name="@createdOn" layout="${date}" />
    </target>