Problems with setting the transaction level to serializeable

jstrain

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!

Brian Donahue

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.

jstrain

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 Donahue

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.