Distrubuted Transactions with AS400

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.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
"Jane" wrote:
> 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.|||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.
"l" wrote:
> 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
>
> "Jane" wrote:
> > 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.|||OLEDB Errors in SQL Profiler shows ;
<hresult>-2147168246</hresult>
<inputs>
<punkTransactionCoord>0x624A0060</punkTransactionCoord>
<isoLevel>4096</isoLevel>
<isoFlags>0</isoFlags>
<pOtherOptions>0x00000000</pOtherOptions>
</inputs>|||> 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

distributed transaction error from CLR Trigger

I have a stored procedure in SQL2005 that queries and updates a linked oracle server. The sp runs fine from Management Studio, but when called by a CLR trigger I get the following error message:

Command attempted:

if @.@.trancount > 0 rollback tran
(Transaction sequence number: 0x000000000000000032DD00000000, Command ID: 1)

Error messages:

The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
Get help: http://help/7391

The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
Get help: http://help/7391

A .NET Framework error occurred during execution of user defined routine or aggregate 'PriorityTrigger':
System.Data.SqlClient.SqlException: The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction.
Changed database context to 'pims'.
OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" returned message "New transaction cannot enlist in the specified transaction coordinator. ".
System.Data.S (Source: MSSQLServer, Error number: 6549)
Get help: http://help/6549

Any thoughts or direction appreciated

Richard

Hi!

You may try to play with settings of OraOLEDB (DistribTX).

|||The procedure will probably fail the same way when run from a standard TSQL trigger. DTC is pulled in when you run the update within an existing local transaction. There should be an option for your linked server that will disable automatic enlistment if you don't need the transaction distributed to the remote machine. Otherwise you'll have to play with the Oledb provider & DTC setup to get distributed transactions working between the two.|||
--
HOW TO CONFIGURE DISTIBUTED TRANSACTION COORDINATION
--

First verify the "Distribute Transaction Coordinator" Service is
running on both database server computer and client computers
1. Go to "Administrative Tools > Services"
2. Turn on the "Distribute Transaction Coordinator" Service if it is not running

If it is running and client application is not on the same computer as
the database server, on the computer running database server
1. Go to "Administrative Tools > Component Services"
2. On the left navigation tree, go to "Component Services > Computers
> My Computer" (you may need to double click and wait as some nodes
need time to expand)
3. Right click on "My Computer", select "Properties"
4. Select "MSDTC" tab
5. Click "Security Configuration"
6. Make sure you check "Network DTC Access", "Allow Remote Client",
"Allow Inbound/Outbound", "Enable TIP" (Some option may not be
necessary, have a try to get your configuration)
7. The service will restart
8. BUT YOU MAY NEED TO REBOOT YOUR SERVER IF IT STILL DOESN'T WORK
(This is the thing drove me crazy before)

On your client computer use the same above procedure to open the
"Security Configuration" setting, make sure you check "Network DTC
Access", "Allow Inbound/Outbound" option, restart service and computer
if necessary.

On you SQL server service manager, click "Service" dropdown, select
"Distribute Transaction Coordinator", it should be also running on
your server computer.

distributed transaction error from CLR Trigger

I have a stored procedure in SQL2005 that queries and updates a linked oracle server. The sp runs fine from Management Studio, but when called by a CLR trigger I get the following error message:

Command attempted:

if @.@.trancount > 0 rollback tran
(Transaction sequence number: 0x000000000000000032DD00000000, Command ID: 1)

Error messages:

The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
Get help: http://help/7391

The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
Get help: http://help/7391

A .NET Framework error occurred during execution of user defined routine or aggregate 'PriorityTrigger':
System.Data.SqlClient.SqlException: The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction.
Changed database context to 'pims'.
OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" returned message "New transaction cannot enlist in the specified transaction coordinator. ".
System.Data.S (Source: MSSQLServer, Error number: 6549)
Get help: http://help/6549

Any thoughts or direction appreciated

Richard

Hi!

You may try to play with settings of OraOLEDB (DistribTX).

|||The procedure will probably fail the same way when run from a standard TSQL trigger. DTC is pulled in when you run the update within an existing local transaction. There should be an option for your linked server that will disable automatic enlistment if you don't need the transaction distributed to the remote machine. Otherwise you'll have to play with the Oledb provider & DTC setup to get distributed transactions working between the two.|||
--
HOW TO CONFIGURE DISTIBUTED TRANSACTION COORDINATION
--

First verify the "Distribute Transaction Coordinator" Service is
running on both database server computer and client computers
1. Go to "Administrative Tools > Services"
2. Turn on the "Distribute Transaction Coordinator" Service if it is not running

If it is running and client application is not on the same computer as
the database server, on the computer running database server
1. Go to "Administrative Tools > Component Services"
2. On the left navigation tree, go to "Component Services > Computers
> My Computer" (you may need to double click and wait as some nodes
need time to expand)
3. Right click on "My Computer", select "Properties"
4. Select "MSDTC" tab
5. Click "Security Configuration"
6. Make sure you check "Network DTC Access", "Allow Remote Client",
"Allow Inbound/Outbound", "Enable TIP" (Some option may not be
necessary, have a try to get your configuration)
7. The service will restart
8. BUT YOU MAY NEED TO REBOOT YOUR SERVER IF IT STILL DOESN'T WORK
(This is the thing drove me crazy before)

On your client computer use the same above procedure to open the
"Security Configuration" setting, make sure you check "Network DTC
Access", "Allow Inbound/Outbound" option, restart service and computer
if necessary.

On you SQL server service manager, click "Service" dropdown, select
"Distribute Transaction Coordinator", it should be also running on
your server computer.