What are the challenges you face when working across database platforms? Take the survey

Sync Script not working

Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
edited November 25, 2005 10:21AM in SQL Compare Previous Versions
Hi Dan,

First, let me say that's a really cool use for SQL Compare, effectively
turning it into Windows Domain Compare! I wish I'd have thought of it.

Can you get the script to run if you use the comparison option 'Do not
include plumbing for transactions'? That should turn the script into one big
query rather than chunking it into separate transactions.


Brian Donahue
Red Gate Technical Support

"Dan Avni" <danavni@officecore.com> wrote in message
> I am using version
> my DB has a linked server that is connected to Active Directory
> that was created using something like this SQL
> EXEC sp_addlinkedserver
> @server = 'ADSI',
> @provider = 'Active Directory Service Interfaces',
> @srvproduct = 'ADSDSOObject',
> @datasrc = 'adsdatasource'
> GO
> I have a sproc that uses the linked server in some way close to this SQL
> SELECT givenName
> FROM OpenQuery
> (ADSI, '<LDAP://directory.myserver.ca/ou=people,o=myserver
> .ca>;(objectClass=*);givenName;subtree')
> when trying to run a sync script between a DB that has this sproc and a
> that doesn't the sync script fails with an error that the ADSI server does
> not support transactions.
> this forces me to create a few sync scripts each for specific parts of the
> DB
> if possible, please allow me to specify for the sync script that the
> following object should be synchronized outside of a transaction scope or
> something similar
> Thanks
> Dan avni


  • Options
    I think that the problem was that SQL Compare's scripts always specify TRANSACTION ISOLATION LEVEL SERIALIZABLE to lock the schema while SQL Compare is modifying it.

    Since most database libraries do not support this isolation level, a query making any reference to a linked server will fail. For instance, a linked server connecting to an Access database cannot support the serializable isolation level.

    The workaround is to save the script and use a different isolation level by changing the line SET TRANSACTION ISOLATION LEVEL... to a different isolation level, such as READ COMMITTED.

    The opinion of our developers is that changing the isolation level in the off-chance that the synchronization will encounter a linked server will cause more trouble than it's worth, so for now at least, we'll need to stay with the workaround.
This discussion has been closed.