Sync Script not working
Brian Donahue
Posts: 6,590 Bronze 1
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.
Regards,
Brian Donahue
Red Gate Technical Support
"Dan Avni" <danavni@officecore.com> wrote in message
news:%23T5VB2NBEHA.1220@server53...
> I am using version 3.1.0.151
> 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
DB
> 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
>
>
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.
Regards,
Brian Donahue
Red Gate Technical Support
"Dan Avni" <danavni@officecore.com> wrote in message
news:%23T5VB2NBEHA.1220@server53...
> I am using version 3.1.0.151
> 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
DB
> 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
>
>
This discussion has been closed.
Comments
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.