Options

Controlling Transaction Isolation level for deployment script

When deploying from SQL Compare, the script is wrapped in a transaction with the isolation level set to SERIALIZABLE.

When altering or creating a proc that uses a linked server, SQL Server attempts to open a distributed transaction for some reason.  On a system with MSDTC disabled (such as Azure DBs or an Azure managed instance), this generates an error.  It works fine if the isolation level stays at READ COMMITTED.

I don't want to disable the transaction, so what are my options?

Example of code that generates the issue:
SET TRANSACTION ISOLATION LEVEL Serializable
go
begin transaction
go
create procedure abc 
as
begin
select top 10 * from database2.dbname.dbo.dbtable
end
commit <span>transaction</span>
Tagged:

Comments

Sign In or Register to comment.