How can we help you today? How can we help you today?

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
marclallen
0

Comments

2 comments

Sort by
  • 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.
    Matthew_Chappelow
    0
  • marclallen
    Oh... man... never occurred to look there... I assumed it was with all the other options.  Thanks!
    marclallen
    0

Add comment

Please sign in to leave a comment.