BUG: transaction always SERIALIZABLE despite setting

dbco_ewdbco_ew Posts: 6
edited March 12, 2015 11:30AM in SQL Compare Previous Versions
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.

Comments

  • 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'?
    Chris
  • 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.
  • 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.
    Chris
  • 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.
  • 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?
    Chris
  • 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.
  • 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
    Chris
  • Made a snapshot, but I cannot get into the ticket.
  • Anu DAnu D Silver 3 Posts: 876 Silver 3
    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.
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: [email protected]
Sign In or Register to comment.