BUG: transaction always SERIALIZABLE despite setting

dbco_ew

Using SQL compare 10.5.0.611 I always get the line

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

in the migration script, despite the fact that I have set the Transaction Isolation Level to READ COMMITTED in Tools -> Application Options.
The used databases are all set to READ COMMITTED.

Normally, SERIALIZABLE is not a problem, but when I have a stored procedure that has a linked server queried in it, the transaction fails. It will fail with

[7391] The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "XXXXXX" was unable to begin a distributed transaction.
OLE DB provider "SQLNCLI11" for linked server "XXXXXX" returned message "The transaction manager has disabled its support for remote/network transactions.".

Turning on the "Do not use transactions in deployment scripts" option will not help. It will always fail for the stored procedure with the linked server in it and thus it will never actually deploy said stored procedure.

How to reproduce:
Make a simple stored procedure, like

CREATE PROC stp_simple_linked
AS
BEGIN
SELECT * FROM linked_server.database.dbo.table
END

And try to deploy this on a different server. Then the deployment will fail due to the setting as above. Copying the script to the clipboard, changing the line

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

to

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

and the deployment is successful!

RECAP:
The problem is that the transaction isolation level setting from the SQL Compare is ignored and that in the migration script the isolation level is always set to SERIALIZABLE. I think this is due to the fact that the setting from SQL Compare is ignored and this, I think, is a bug.

Chris Auckland

Thanks for your post.

It seems to respect the setting for me when I generate a script using SQL Compare 10.5.0.611.

The only thing I can think of is that for some reason you're unable to update the registry location where that setting is stored.

Can you check the 'TransactionIsolationLevel' string in:
HKEY_CURRENT_USER\Software\Red Gate\SQL Compare 10\UI

Can you make sure your account is able to modify this location, and that the string is set to 'READ COMMITTED'?

dbco_ew

I thought I missed something in the original post .

Yes, when I check in the registry, then I see READ COMMITTED at the variable TransactionIsolationLevel at HKCU\Software\Red Gate\SQL Compare 10\UI. That is something I did check before posting this to the topic.

Chris Auckland

Thanks for your reply.

Could you try uninstalling SQL Compare 10.5 and downgrade to 10.4?

There were a couple of problems with 10.5, so we pulled the release. This might be another example of it's brokenness.

Let me know how you get on.

dbco_ew

I cannot promise anything. It's on a production environment. We'll see what we can do. If we are going to downgrade, we'll let you know what the results are.

Chris Auckland

That's great. If you're unable to do that, would you be able to send me (through the support ticket we also have open) a SQL Compare snapshot of the source and target schema and I'll try and replicate the issue here?

dbco_ew

Fortunately, we've got a machine which has an older version of SQL Compare 10 on it (version 10.4.8.87; that's the version you recommended to downgrade to, right?), which has the exact same problem.

On that machine I cannot run regedit, but with the Powershell, I can confirm via

Get-ItemProperty 'HKCU:\Software\Red Gate\SQL Compare 10\UI\'

that the TransActionIsolationLevel is set to 'READ COMMITTED'.

Can you tell me exactly what you want/mean with a SQL Compare snapshot? I might be able to give you that information.

I think you can simply replicate it as follows. Create the following stored procedure:

CREATE PROC stp_problematic
AS
BEGIN
    SELECT * FROM linked_server.database.schema.table
END

That should be enough. Unless there are other settings you'd like to know.

Chris Auckland

Thanks for your post.

Digging a little deeper, I think there can be situations where SQL Compare will override the 'transaction isolation level' and default to 'serializable'.

It would be great to try it with your snapshots and see if it's doing by design in this situation, or if it's doing for the wrong reasons (bug).

You can create the snapshots through the SQLCompare UI. File > Save snapshot

dbco_ew

Made a snapshot, but I cannot get into the ticket.

Anu D

Thanks for your patience.

We have fixed the issue you reported in our latest version of SQL Compare 11

Let me know if this helps.