Options

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!
· Share on Twitter

Comments

  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
    · Share on Twitter
  • Options
    jstrainjstrain Posts: 3
    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.
    · Share on Twitter
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
    · Share on Twitter
This discussion has been closed.