Distrubuted Transactions with AS400

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jane - 29 Jan 2008 13:41 GMT

Hi All

I have created stored procs that update SQL and AS400 (Linked server)
tables. I want to wrap both updates in a transaction, however when I do, I
get the error
"The operation could not be performed because OLE DB provider "MSDASQL" for
linked server "LinkedServerName" was unable to begin a distributed
transaction".
We are using Client Access ODBC driver (V5R2, SI06631).
All of the documents that I have read talk about MTS and MS DTC.
MS DTC is running on the SQL Server.

Is there any way of using Distributed Transaction processing without
requiring MTS ?

Thanks in advance.

Reply to this Message

l - 29 Jan 2008 17:21 GMT

not enough info on the error.
here:
http://support.microsoft.com/kb/306212

you may want to look at the full error when you talk to DB2. like so

DBCC TRACEON (3604, 7300)

then run your sp and seewhat it does.

did you try just issue
BEGIN DISTRIBUTED TRAN
and see if the 2 data sources you are working with can be accessed?
and you the same authentication your application is going to call your sp
with.

Thanks, Liliya

> Hi All
>
[quoted text clipped - 12 lines]
>
> Thanks in advance.

Reply to this Message

Jane - 30 Jan 2008 10:36 GMT

Thanks for the response.

Yes - used the same auth as the app will use (windows auth)
Yes - just used BEGIN DISTRIBUTED TRAN

Used DBCC TRACEON (3604, 7300) & SET XACT_ABORT ON as per the link in a
Query Window then ran the updates again.
Ran DBCC TRACESTATUS (3604,7300) to check that the flags for the session
were set.
Still failing & still no extra error information.

Under "Configuration Issues" the link states ;
"Start the Distributed Transaction Coordinator (DTC or MSDTC) on all servers
that are involved in the distributed transaction".

I am not sure what needs to running on the AS400 for this to work.

> not enough info on the error.
> here:
[quoted text clipped - 30 lines]
> >
> > Thanks in advance.

Reply to this Message

Jane - 30 Jan 2008 10:50 GMT

OLEDB Errors in SQL Profiler shows ;

<hresult>-2147168246</hresult>
<inputs>
    <punkTransactionCoord>0x624A0060</punkTransactionCoord>
    <isoLevel>4096</isoLevel>
    <isoFlags>0</isoFlags>
    <pOtherOptions>0x00000000</pOtherOptions>
</inputs>

Reply to this Message

l - 31 Jan 2008 15:28 GMT

> I am not sure what needs to running on the AS400 for this to work.

depend on what are you running on AS400. DB2 version. Here. how to set up
mts (0you have mentioned this one earlier in this thread)
http://www-03.ibm.com/servers/enable/site/db2/mts/mts.pdf

as about ms sql server side, it does not hurt to test if your sql side is
ok. Easy enough as long as you have couple ms sql's on the same domain etc.
if not, can install one on your ws, configure it and see if you can issue
distributed transactions. After all, it will give you a 'lab rat' s well to
experiment if you in need of one.
Thanks, Liliya

Reply to this Message