I have SQL Anywhere 11 database linked to MS SQL Server 2008 as a ‘linked server’. When I don’t use distributed transactions (via MSDTC) all is working fine (updates to remote SQL Anywhere committed without any problems). When I start using distributed transactions (and make only SELECTs inside distributed transaction) all is working fine again (I control transactions using @@trancount statement in MS SQL Server code). Such experiments bring two major results: SQL Anywhere as Linked Severs and MSDTC working correctly in these simple configurations.
But when I start using UPDATEs inside distributed transaction all completed with The OLE DB provider "SAOLEDB.11" for linked server "link" reported an error committing the current transaction error on line with UPDATE statement.
Note: when I link two MS SQL Servers any UPDATES in one distributed transactions working fine.
What should I do to make code below working correctly?
set implicit_transactions on; -- necessary for autocommit shootdown set xact_abort on; begin try begin distributed tran; select @@trancount TranCount; --select * from link..amos.address where addressid = 1000001; update link..amos.address set ad.addressstatus = 1 from link..amos.address ad where ad.addressid = 1000001; select @@trancount TRANCOUNT; commit tran; select @@trancount TRANCOUNT; end try begin catch rollback tran; select error_number() AS ErrorNumber, error_message() AS ErrorMessage, error_line () AS ErrorLine; end catch;
TranCount: 2 ErrorNumber: 7394 ErrorMessage: The OLE DB provider "SAOLEDB.11" for linked server "link" reported an error committing the current transaction. ErrorLine: 10
P.S. Sorry for my English.
Precisely which release and build of SQL Anywhere 11 are you using? Does your SAOLEDB.11 library have the same build number?
answered 09 Nov '11, 08:01
You may look at the following FAQ.
The fixes mentioned there were introduced in newer EBFs than you are using, so it might be worthwhile to test with a newer EBF. Note, I can't claim that these issues are related to the behaviour you are seeing.
answered 09 Nov '11, 09:56