Why no stored procedure support for adding logs to SQL Server?

[ScottyMac52]

We have a current implementation in older .NET Framework applications that use the NLogger approach to executing a stored procedure to add log entries.

REF: NLog Schema

Without this ability in serilog-mssql I will be unable to use this and will instead have to inject a custom implementation.

[ckadluba]

Hi @ScottyMac52!

Thank you for getting in touch. Why do you need to call a stored procedure for logging? Can you tell us a little bit about your use case that requires this?

[ScottyMac52]

You are kidding right? It's far more efficient to use a stored procedure instead of table based inserts, especially for an on-premise database. My use case is that what I REALLY need is a way to plug an I LoggerProvider interface into SeriLog's I LoggerFactory implementation. This way I can inject whatever service implementation that talks to my database that I want. Anyways, it's clear I cannot use your Sink.

[ckadluba]

We had a similar discussion in the past where the outcome was that the BulkInsert of MSSQL sink is sufficiently performant and also the best suited implementation (https://github.com/serilog-mssql/serilog-sinks-mssqlserver/issues/132). I think in terms of efficiency BulkInsert is a solution that suits a broad spectrum of applications.

If you have a question regarding Serilog core (e.g. LoggerFactory), I recommend you ask at the specific repo: https://github.com/serilog

[anandthirumala]

I am having a similar issue, I need to invoke a Stored Procedure, we were using NLog and it's possible there. We are looking to use Serilog for the application. We can't change the DB, the permission is only granted for the Stored Procedure, so the direct insert will fail. Unless we go through a process for requesting direct access, which might take time, and also not sure we will be granted access.

But I am curious to know how BulkInsert works? Is there any sample or documentation you could point me to please?

Thanks

[ckadluba]

Hi @anandthirumala!

You can find the code of the sink where the bulk insert is done here: https://github.com/serilog-mssql/serilog-sinks-mssqlserver/blob/07a514b5ef1667cdc2385c14a3eb46eaf91242b7/src/Serilog.Sinks.MSSqlServer/Sinks/MSSqlServer/Platform/SqlBulkBatchWriter.cs#L35

But in the sink all of the Microsoft SqlClient calls are encapsulated in wrapper classes so you better look for samples which are easier to understand in the official documentation here: https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy?view=dotnet-plat-ext-6.0

I hope this helps you.

[walterhuang]

Same here. Our company policy only allows using stored procedures for "security" reasons 😥

[anandthirumala]

Same here. Our company policy only allows using stored procedures for "security" reasons 😥

We were forced to use NLog for this reason. I prefer Serilog any day, but this is a limitation.