Transaction Error

[aljubicic]

Hi All,

I have a few triggers on a SQL2000 table that does inserts to other servers via linked servers. 2 of the linked servers are SQL7 while the other is an Informix Dynamic Server 9.3. All three triggers fire together due to an insert on the SQL2000 table. I use a insert query to fire these triggers via query analyser. When I run the insert the first time it errors with the following...

Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a].


This only happens with the trigger that points to the Informix Server. However when I run the insert the second time it runs without errors. Does anyone know why this occurs?? And how can I fix this??

The linked Server to the the Informix Server is fine as I can do select queries to it via query analyser.

Is there some sort of setting that needs to be invoked??

Any ideas into this would be great.

Thanks Anthony

 

[osjohnm]

Hi

You can try using XACT_ABORT in your procedure an set its value to ON.


SET XACT_ABORT ON
.....

Have a look at the following article at Microsoft's support site.

http://support.microsoft.com/default.aspx?scid=kb;en-us;329332

Hope this helps

John

 

[osjohnm]

Hi

This link is more appropriate I think.

http://support.microsoft.com/default.aspx?scid=kb;en-us;306212

John

 

[aljubicic]

Hi guys,

Thanks for your help, however it is not an issue with the trigger firing order. As I have tested this and it is not related to this. The problem is with the communication with the SQL2000 server and the Informix Server. When I run the following query in query analyser, it will always work a couple of times and then fail with the error message.

Begin distributed Transaction
set xact_abort on

Select authtest.authority.auth.aunrmast where nar_num = 23

IF @@error <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION


Now when I create a linked Server from a SQL7 Server to the Informix Server the above query works fine, no problem.
I also tried this on another SQL7 server and it errored meaning this error does not belong to SQL2000 only. It seems like more of a configuration error between servers.

Anyone??

 

[evaleah]

I figured it out.

You cannot put more than one statement with a temp table in a proc. So, even though I am using an IF it will not let me create the proc inserting into a temp table more than once.

Thanks for looking at it

 

[aljubicic]

What do you mean that you have figured it out?? And if you have please explain..

 

[LogicalmanUS]

aljubicic,

Don't get your hopes up, unfortunately I think evaleah posted to the wrong thread. This is the thread I believe it should have been posted to:

thread222-648315