Linked server "x" was unable to begin a distributed transaction. 1

[Qmoto]

Hello Everyone,

I’m creating a simple web application in php with SQL 2005 as the backend server. Within SQL I’ve created a linked server to an AS/400 which is supposed to be updated at the end of the stored procedure. However, everything works fine until the procedure tries to insert a record into the linked server; at that point I get the following error.


I’ve looked all over the place and I’ve found a fair amount of help with linked servers. Some of the posts/websites have gotten me closer, but so far I haven’t been able to solve this error, so any help would be appreciated!

ERROR RETRURNED BY SQL:

OLE DB provider "MSDASQL" for linked server "LS_AS400" returned message "[COLOR=red][IBM][iSeries Access ODBC Driver]Enlist with DTC phase failed. 2[/color]".

Msg 7391, Level 16, State 2, Procedure srgsp_LEAN_StatusCheck, Line 101
"[COLOR=red]The operation could not be performed because OLE DB provider "MSDASQL" for linked server "LS_AS400" was unable to begin a distributed transaction.[/color]"

Just as an fyi, here's the code that's throwing the error:

IF (@ReturnVal) = '106'
BEGIN
	EXEC (
	'INSERT INTO My400Lib.My400File
		VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)',
		@C1,
		@C2,
		@C3,
		@C4,
		@C5,
		@C6,
		@C7,
		@C8,
		@C9,
		@C10,
		@C11,
		@C12,
		@C13
	)
	AT LS_AS400
END

 

[mrdenny]

You'll need to see if the AS/400 has some sort of Distributed Transaction Cordinator like MSDTC for the SQL Server.

You also need to setup the Windows server to allow DTC to have network access. Control Pannel > Add Remove Programs > Windows Components > Application Servers (Or something like that). Put a check next to Enable Network DTC then next, next, ...

If MSDTC and the AS/400 version of DTC are not both working correctly you will get this message.

The other option you have is to not make the procedure transactional. In order works remove any BEGIN DISTRIBUTED TRANSACTION commands (and the corosponding COMMIT), or change the connection object to not be transactional.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Qmoto]

Sorry, I didn't get back to you sooner!

I appreciate the suggestions; however, I've not been able to solve the riddle. I have managed to find a way around it though, so for now I have a ‘band aid’ solution…

I noticed that I was able to manually insert records without a problem, but my stored procedure would fail with the above error(s), so through trial and error I found that by removing all references to a transaction the script works.

For example, my original procedure looked something like this:

BEGIN TRANSACTION
IF (@VAL1) = @STATUS
  BEGIN
  ...
  END
IF (@VAL2) = @STATUS
  BEGIN
  ...
  END

IF (@VAL3) = @STATUS
EXEC
  ('see code listed in original post')
AT LinkedServer
...
COMMIT TRANSACTION

In order to work around the error, I did something like this:

[COLOR=red][s]BEGIN TRANSACTION[/s][/color]
IF (@VAL1) = @STATUS
  BEGIN
  ...
  END
IF (@VAL2) = @STATUS
  BEGIN
  ...
  END

IF (@VAL3) = @STATUS
EXEC
  ('see code listed in original post')
AT LinkedServer
...
[COLOR=red][s]COMMIT TRANSACTION[/s][/color]

Once I removed the references to BEGIN & COMMIT TRANSACTION the procedure runs as expected. *shrug* I have no idea how to fix the problem. I'm going to build a clean test server and see if I can duplicate/fix the problem, but for now it's working, so I'll have to live with the way it is.

MrDenny, I appreciate the help & and your prompt reply, and again; I apologize for the slowness of my own reply!

BTW, for anyone else having this issue, there are a good number of posts on the web, but the ones I've found to be most useful are these:

[ul square]
[li]

http://www.mcse.ms/archive85-2005-9-1827275.html

[/li]
[li]

http://groups.google.com/group/micr.../127255ab5a3a1fe0?hl=en&lr=&ie=UTF-8&oe=UTF-8

[/li]
[li]

http://blogs.msdn.com/mab/archive/2005/12/30/508273.aspx

[/li]
[li]

http://sillydog.org/forum/sdt_9770.php

[/li]
[li]

http://support.microsoft.com/?kbid=873160

[/li]
[li]

http://support.microsoft.com/kb/839279

[/li]
[/ul]

 

[mrdenny]

Um, the fix that you used is the second one that I posted.

The other option you have is to not make the procedure transactional. In order works remove any BEGIN DISTRIBUTED TRANSACTION commands (and the corosponding COMMIT), or change the connection object to not be transactional.

The reason that this is happening is becuase is order to maintain the transactions integrity when talking to the AS/400 both servers need to have a Transaction Cordinator that can talk to each other. If they do not, then they will not be able to maintain the consistaincy of the transaction and the error message that you are getting will be returned.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Qmoto]

You're right mrdenny... at first I didn't catch the connection because my transactions were not "Distributed", but I guess the fact they were connecting to and updating a Linked Server makes it a distributed transaction.

Thanks for the answer! - Have a star! )