Controlling Transaction Isolation level for deployment script

marclallen

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>

Matthew_Chappelow

In the Tools menu Application Options the isolation level can be changed. As you mentioned the one you will want to use is Read committed.

marclallen

Oh... man... never occurred to look there... I assumed it was with all the other options.  Thanks!