The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

Hello!

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;

Query Output:

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.

asked 09 Nov '11, 07:51

Oleg45's gravatar image

Oleg45
16112
accept rate: 0%

edited 10 Nov '11, 03:13

Volker%20Barth's gravatar image

Volker Barth
30.9k311457668


Precisely which release and build of SQL Anywhere 11 are you using? Does your SAOLEDB.11 library have the same build number?

permanent link

answered 09 Nov '11, 08:01

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

Server

Server Version: 11.0.1.2331 (it's a Developer Edition)

BuildChange: 625781 (value taken from Database server properties list in Sybase Central)

Provider

SQL Anywhere OLE DB Provider 11 (dboledb11.dll)

Version: 11.0.1.2331

Last Modified: 10/14/‎2009.

SQL Anywhere OLE DB Provider Catalog Assist (dboledba11.dll)

Version: 11.0.1.2331

Last Modified: 10/14/‎2009.

Engine version and provider version look like same. Also I check (in Windows Registry) keys for SAOLEDB.11 provider - its point on correct files.

(09 Nov '11, 08:45) Oleg45
Replies hidden

While I can't say something concerning the specific problem I would recommend updating to a recent EBF. In the last 2 years many modifications and fixes have been made. So possibly that could fix this issue.

(09 Nov '11, 09:21) Reimer Pods

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.

permanent link

answered 09 Nov '11, 09:56

Volker%20Barth's gravatar image

Volker Barth
30.9k311457668
accept rate: 32%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×48
×28
×4

question asked: 09 Nov '11, 07:51

question was seen: 2,829 times

last updated: 10 Nov '11, 03:13