I need to log actions into log table in my stored procedure, which is called in context of some transaction. The records in the log table I need no matter what happens (no, it's even more important to get them there if operation fails).
begin transaction...execute some_proc...if (...)commit transactionelserollback transaction
...insert into log...insert ...update ...insert into log......
How to do this?
I've found two approaches:
The second way is more reliable, however not the fastest one. The idea is to execute query on the same sever as if it's a linked server.
Suppose you have a log table:
create table System.Log( ID int identity(1,1) not null, Date datetime not null default getdate(), Type int null, Value nvarchar(max) null);
To add log record you shall define a stored procedure:
create procedure System.WriteLog( @type int, @message nvarchar(max))asbegin set nocount on; execute( 'insert into dbname.System.Log(Type, Value) values(?, ?)', @type, @message) as user = 'user_name' at same_server_name;end
Whenever you're calling System.WriteLog in context of local transaction the records are inserted into the System.Log table in a separate transaction.
a@href@title, b, blockquote@cite, em, i, strike, strong, sub, super, u