What are the challenges you face when working across database platforms? Take the survey
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.