StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Even if you start a new transaction, it will be nested within the outer transaction. SQL Server guarantees that a rollback will result in an unmodified database state. So there is no way you can insert a row inside an aborted transaction.</p> <p>Here's a way around it, it's a bit of a trick. Create a linked server with <code>rpc out = true</code> and <code>remote proc transaction promotion = false</code>. The linked server can point to the same server as your procedure is running on. Then, you can use <code>execte (<query>) at <server></code> to execute something in a new transaction.</p> <pre><code>if OBJECT_ID('logs') is not null drop table logs create table logs (id int primary key identity, msg varchar(max)) if OBJECT_ID('TestSp') is not null drop procedure TestSp go create procedure TestSp as execute ('insert into dbo.logs (msg) values (''test message'')') at LINKEDSERVER go begin transaction exec TestSp rollback transaction select top 10 * from logs </code></pre> <p>This will end with a row in the log table, even though the transaction was rolled back.</p> <p>Here's example code to create such a linked server:</p> <pre><code>IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'LINKEDSERVER') EXEC master.dbo.sp_dropserver @server=N'LINKEDSERVER', @droplogins='droplogins' EXEC master.dbo.sp_addlinkedserver @server = N'LINKEDSERVER', @srvproduct=N'LOCALHOST', @provider=N'SQLNCLI', @datasrc=N'LOCALHOST', @catalog=N'DatabaseName' EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVER', @optname=N'rpc out', @optvalue=N'true' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LINKEDSERVER', @useself=N'True', @locallogin=NULL,@rmtuser=NULL, @rmtpassword=NULL EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVER', @optname=N'remote proc transaction promotion', @optvalue=N'false' </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload