objects with linked servers

Brian Donahue

Hi Kevin,

The server on the other end of the linked server wouldn't happen to be
running Windows 2003, would it? You have to enable DTC for network access in
that case, because it's disabled by default.

You can enable DTC from the Control Panel->Add/Remove programs->Windows
Components->Application Server.

Regards,

Brian Donahue
Red Gate Technical Support

"Kevin Ott" <k.ott@terranovatrading.com> wrote in message
news:NBdiZmJLEHA.1528@server53...
> Hi Red Gate Support,
>
> We have been able to use the sql compare scripts for the migration of
> objects that contain references to another sql server that are setup as a
> linked server.
>
> Here is the error message that we receive.
>
> The operation could not be performed because the OLE DB provider
'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
>
> Please advise.
>
> Thanks.
>
>

Brian Donahue

This error message may appear because not all ODBC libraries support transaction isolation level serializable. When a query is run by SQL Server involving a linked server (distributed query), it requires that the linked server support the transaction isolation level.

The workaround is to save SQL Compare's script and change SET TRANSACTION ISOLATION LEVEL SERIALIZABLE to READ COMMITTED in place of serializable. This should allow the script to run in Query Analyzer.

SQL Compare is not going to be changed to accommodate these linked server issues because it is not recommended to run the schema synchronization script while allowing other users to modify the database at the same time. We would like to leave the transaction isolation level at serializable for this reason.