ERROR: Serilog.Core.Sinks.Batching.BatchingSink: failed emitting a batch The given ColumnMapping does not match up with any column in the source or destination.

[Eruoghene]

Please clearly describe what the SQL Sink is doing incorrectly:
Unable to insert log data into Custom MSSQL Columns

Please clearly describe the expected behavior:
Serilog SQL Sink should insert log into specified MsSql Columns

List the names and versions of all Serilog packages used in the project:
Serilog 4.2.0
Serilog.AspNetCore 9.0.0
Serilog.Enrichers.ClientInfo 2.1.2
Serilog.Sinks.MSSqlServer 8.1.0

Target framework and operating system:
.Net 8
OS: Windows 10 Pro, Version 21H2

Provide a simple reproduction of your Serilog configuration code:
var columnOptions = new ColumnOptions
{
AdditionalColumns = new Collection
{
new SqlColumn { ColumnName = "SessionId", DataType = System.Data.SqlDbType.VarChar, DataLength = 50, AllowNull = true },
new SqlColumn { ColumnName = "MethodName", DataType = System.Data.SqlDbType.VarChar, DataLength = 50, AllowNull = true },
new SqlColumn { ColumnName = "Severity", DataType = System.Data.SqlDbType.VarChar, DataLength = 15, AllowNull = true },
new SqlColumn { ColumnName = "MessageObject", DataType = System.Data.SqlDbType.VarChar, DataLength = -1, AllowNull = true },
new SqlColumn { ColumnName = "ExceptionObject", DataType = System.Data.SqlDbType.VarChar, DataLength = -1, AllowNull = true },
new SqlColumn { ColumnName = "ClientIP", DataType = System.Data.SqlDbType.VarChar, DataLength = 20, AllowNull = true }
},
Store = new List() // Remove standard columns like Message, Level, etc.
};

// Configure MSSqlServerSinkOptions
var sinkOptions = new MSSqlServerSinkOptions
{
TableName = "Logs", // Replace with your table name
AutoCreateSqlTable = false, // Automatically create the table if it doesn't exist
BatchPostingLimit = 1, // Post each log individually for debugging
BatchPeriod = TimeSpan.FromSeconds(5)
};

// log SelfLog to a file
var selfLogFile = Path.Combine(Directory.GetCurrentDirectory(), "serilog_selflog.txt");
SelfLog.Enable(File.CreateText(selfLogFile));

Log.Logger = new LoggerConfiguration()
.MinimumLevel.Debug()
.Enrich.FromLogContext()
.WriteTo.MSSqlServer(
connectionString: Configuration.GetSection("AppSettings").GetValue("DDSLDbConnection"),
sinkOptions: sinkOptions,
columnOptions: columnOptions)
.CreateLogger();

Provide a simple reproduction of your Serilog configuration file, if any:
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
},
"AllowedHosts": "*",
"AppSettings": {
"DDSLDbConnection": "Data Source=;Max Pool Size = 1000;Initial Catalog=;Persist Security Info=True;User ID=;Password=;Encrypt=False;",
},
"Serilog": {
"Using": [ "Serilog.Sinks.MSSqlServer" ],
"MinimumLevel": {
"Default": "Information",
"Override": {
"Microsoft": "Warning",
"System": "Warning"
}
},
"Enrich": [ "FromLogContext" ],
"WriteTo": [
{
"Name": "MSSqlServer",
"Args": {
"connectionString": "--redacted--",
"sinkOptionsSection": {
"tableName": "Logs",
"autoCreateSqlTable": false,
"batchPostingLimit": 1,
"period": "0.00:00:30"
},
"restrictedToMinimumLevel": "Debug"
}
}
]
}
}

Provide a simple reproduction of your application code:
using (LogContext.PushProperty("SessionId", requestId ?? "DefaultSessionId"))
using (LogContext.PushProperty("MethodName", methodName ?? "UnknownMethod"))
using (LogContext.PushProperty("Severity", "Information"))
using (LogContext.PushProperty("MessageObject", message ?? "No message"))
using (LogContext.PushProperty("ClientIP", "127.0.0.1"))
{
Log.Information("Custom column logging test.");
}

[ckadluba]

Hello @Eruoghene!

Thank you for reporting this.

Can you please provide the SQL definition of your log table?

Thank you,
Christian

[Eruoghene]

CREATE TABLE [dbo].[Logs](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[SessionId] varchar NOT NULL,
[MethodName] varchar NOT NULL,
[Severity] varchar NOT NULL,
[MessageObject] varchar NULL,
[ExceptionObject] varchar NULL,
[ClientIP] varchar NULL,
CONSTRAINT [PK_Logs] PRIMARY KEY CLUSTERED
(
[Id] ASC
)

[ckadluba]

Perhaps the problem is, that you do not have most of the sinks standard columns (Message, Level, TimeStamp, etc.) in your table. You have to explicitly remove them in your ColumnOptions definition (check out our documentation and sample apps for how to do that).

[Eruoghene]

Hi,

I implemented the above recommendations as shown below

Column Options definition was updated to below

var columnOptions = new ColumnOptions
{
AdditionalColumns = new Collection
{
new SqlColumn { ColumnName = "SessionId", DataType = System.Data.SqlDbType.NVarChar, DataLength = 50, AllowNull = true },
new SqlColumn { ColumnName = "MethodName", DataType = System.Data.SqlDbType.NVarChar, DataLength = 50, AllowNull = true },
new SqlColumn { ColumnName = "ClientIP", DataType = System.Data.SqlDbType.NVarChar, DataLength = 20, AllowNull = true }
}
//Store = null //new List() // Remove standard columns like Message, Level, etc.
};

columnOptions.Store.Remove(StandardColumn.LogEvent);
columnOptions.Store.Remove(StandardColumn.Level);
columnOptions.Store.Remove(StandardColumn.MessageTemplate);
columnOptions.Store.Remove(StandardColumn.SpanId);
columnOptions.Store.Remove(StandardColumn.Id);
columnOptions.Store.Remove(StandardColumn.Exception);
columnOptions.Store.Remove(StandardColumn.Message);
columnOptions.Store.Remove(StandardColumn.Properties);
columnOptions.Store.Remove(StandardColumn.TraceId);

Custom Table was updated to the below script (note that it has the standard columns)

CREATE TABLE [dbo].[Logs](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[SessionId] nvarchar NULL,
[MethodName] nvarchar NULL,
[Severity] nvarchar NULL,
[Message] nvarchar NULL,
[Exception] nvarchar NULL,
[Properties] nvarchar NULL,
[TimeStamp] datetime NULL,
[ClientIP] varchar NULL,
CONSTRAINT [PK_Logs] PRIMARY KEY CLUSTERED
(
[Id] ASC
)

Then the application code was updated to the code sample below (note there was no sample in the documentation for this application)

using (_logger.BeginScope("{SessionId}", requestId ?? "DefaultSessionId"))
using (_logger.BeginScope("{MethodName}", methodName ?? "UnknownMethod"))
using (_logger.BeginScope("{ClientIP}", _ip.GetClientComputerIpAddress().Result ?? "UnknownIP"))
{
_logger.LogInformation($"{message}");
}

I used the below documentation

https://github.com/serilog-mssql/serilog-sinks-mssqlserver?tab=readme-ov-file#columnoptions-object

And I got the error below

Serilog.Core.Sinks.Batching.BatchingSink: failed emitting a batch (Temporary, 1 events)
System.InvalidOperationException: The given ColumnMapping does not match up with any column in the source or > destination.
at Microsoft.Data.SqlClient.SqlBulkCopy.AnalyzeTargetAndCreateUpdateBulkCommand(BulkCopySimpleResultSet internalResults)
at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource1 source) --- End of stack trace from previous location --- at Serilog.Sinks.MSSqlServer.Platform.SqlBulkBatchWriter.WriteBatch(IEnumerable1 events)
at Serilog.Core.Sinks.Batching.BatchingSink.LoopAsync()

[ckadluba]

Maybe that was a bit too much. I noticed that you also removed the Id standard column but your table has that column.

[Eruoghene]

Lolzzz,

What was a bit too much?

For the Custom columns, when Sink creates the ID column, looking at the table definition, its created without the Identity Constraint. Else why I created it

[ckadluba]

I think the problem is as follows. Your table has a column named "Id" which is not nullable

CREATE TABLE [dbo].[Logs](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
...

In your code you removed the standard column "Id".

columnOptions.Store.Remove(StandardColumn.Id);

You also do not define a custom column with the name "Id".

AdditionalColumns = new Collection
{
new SqlColumn { ColumnName = "SessionId", DataType = System.Data.SqlDbType.NVarChar, DataLength = 50, AllowNull = true },
new SqlColumn { ColumnName = "MethodName", DataType = System.Data.SqlDbType.NVarChar, DataLength = 50, AllowNull = true },
new SqlColumn { ColumnName = "ClientIP", DataType = System.Data.SqlDbType.NVarChar, DataLength = 20, AllowNull = true }
}

As a consequence the sink issues an insert operation to the table where the Id column is missing and the SqlClient library throws an exception.

I would suggest to not remove the standard column "Id".

[Eruoghene]

Hi,

Kindly find a list of scenarios I ran:

1. I added the Id as a custom column. I got an error saying Id is already present.
2. I removed Id as a custom column, and modified my column list as shown below

var columnOptions = new ColumnOptions
{
AdditionalColumns = new Collection
{
new SqlColumn { ColumnName = "SessionId", DataType = System.Data.SqlDbType.NVarChar, DataLength = 50, AllowNull = true },
new SqlColumn { ColumnName = "MethodName", DataType = System.Data.SqlDbType.NVarChar, DataLength = 50, AllowNull = true },
new SqlColumn { ColumnName = "ClientIP", DataType = System.Data.SqlDbType.NVarChar, DataLength = 20, AllowNull = true }
}
};
columnOptions.Store.Remove(StandardColumn.SpanId);
columnOptions.Store.Remove(StandardColumn.TraceId);

I got database entries, but data did not enter my custom columns.

In the screenshot below, all custom column have null values, even though I am passing values in the application code as shown below

using (_logger.BeginScope("{SessionId}", requestId ?? "DefaultSessionId"))
using (_logger.BeginScope("{MethodName}", methodName ?? "UnknownMethod"))
using (_logger.BeginScope("{ClientIP}", _ip.GetClientComputerIpAddress().Result ?? "UnknownIP"))
{
_logger.LogInformation($"{message}");
}

The Message and Message template columns of serilog have exactly the same values which are string concatenation of items I would Like to be in individual columns for easy data search.

The whole Idea was for me to be able to define specific columns and insert data into those columns via serilog.

Do you have a working example?

[ckadluba]

Yes. there is a sample on how to work with custom columns. Have you checked out https://github.com/serilog-mssql/serilog-sinks-mssqlserver/tree/dev/sample/WorkerServiceDemo?

Be aware that the column options definition is in appsettings.json of this project.