CODEWITHSUNDEEP
sql-serverstored-procedurestransactions
GuidoG
GuidoG

Reputation: 12014

Sql Server: logging in stored procedure

I have a stored procedure that is called from a c# application.
The transaction is started and commited/rolledback from this c# application.

The stored procedure can do some inserts/updates in various tables, which will all be commited or rolledback by the calling application.

The problem is that the stored procedure also insert records into a logtable, which must survive the rollback.

What is the best way of doing this ?

I think I remember from a company I worked for long ago they had solved this by creating a stored procedure for the logging, and this stored procedure had some exotic statements that made it work outside the transaction, something like that. As I said, long time ago I could remember this wrong.

Upvotes: 1

Views: 361

Answers (1)

Khonsort
Khonsort

Reputation: 483

Some times ago, I've develop a tools that logged stored procedure execution in a databases table. The tools was written as a C# assembly compiled into the Database Server and based on differents SQL procedures and functions linked to its C# entry points.

To allow a rollback without the lost of all events allready logged, the C# assembly SHOULD used a full defined connectionString to connect to its database server (SERVERNAME\INSTANCE server param instead of local).

This is perhaps the solution used by your previous company.

Meanwhile, there are some disadvantages:

  • thoses connections was qualified as "external" and the "truthfully" databases parameters should be set to true to allow code execution if not signed
  • this solution is not supported on clouded databases (AWS RDS or Azure)
  • A new connection is created by C# methods

For this last reason and a customer need, I've rewrite a toolbox based on 100% T-SQL source code.

I've just write a response which can be usefull see: https://stackoverflow.com/a/32988757/1183297

Upvotes: 1

Related Questions