Problems with setting the transaction level to serializeable

jstrainjstrain Posts: 3
edited August 15, 2006 1:07PM in SQL Compare Previous Versions
We use DTS packages to compile scripts needed to upgrade our databases. This has worked fine until today so I believe this is related to all of the MS released security patches this week. I can isolate the problem to the SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement that SQL Compare places in the migration script. If I comment it out then all works fine. I assume this more restrictive setting is preferred, so are there any ideas how to get that isolation level to work again?
Thanks!

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hello,

    If you have distributed queries in your database that reference other ODBC drivers (such as the ADS provider) that don't support transaction isolation level serializable, then the only way you can get SQL Compare's script to run is to save it to disk, and replace the line 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE' to another isolation level, such as READ COMMITTED, and then run the script in another tool such as Query Analyzer.
  • So I guess this part of one of the procs being altered is the source of the problem:
    -- get Active directory listing
    insert #ADSI_USERS
    select  UserId      = sAMAccountName
           ,UserAccount = cn  
           ,FirstName   = givenname
           ,LastName    = sn
           ,Path        = adspath        
    from OpenQuery
    (LS_ADSI,'<LDAP://ourdomain.org>;(&(objectClass=user)(displayName=*)(givenName=*)(sn=*));
           sAMAccountName,cn,givenname,sn,adspath')
    
    Thanks for looking into it.
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    It very well could be. The ADSI ODBC drivers have different capabilities than SQL Server. This happens on linked servers and open queries to ADSI, Access, and Excel files as well.
This discussion has been closed.