Unable to begin a distributed transaction | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Unable to begin a distributed transaction

I am getting the following error when using Begin/Commit Transaction within a T-sql script. Server: Msg 7391, Level 16, State 1, Line 324
The operation could not be performed because the OLE DB provider ‘MSDASQL’ was unable to begin a distributed transaction.
[OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver (32-bit)]Option type out of range.]
OLE DB error trace [OLE/DB Provider ‘MSDASQL’ ITransactionJoin::JoinTransaction returned 0x8004d00a]. The script runs fine without the Begin/Commit transaction code. Here is the code were the failure occurs. (Line 324 is the "insert #As400" line) Create Table #As400
NumUp char(10) not null
, NumPages char(10) not null
, TTWebPlate char(10) not null
, TBWebPlate char(10) not null
, BTWebPlate char(10)
, BBWebPlate char(10)
, FormNum char(10) not null
, JobNum char(10) not null
, sYear int
, sMonth int
, Magno int
insert #As400
Convert( char(10), OFNOUP )
, Convert( char(10), OF#PAG )
, Convert( char(10), OFP#TT )
, Convert( char(10), OFP#TB )
, Convert( char(10), OFP#BT )
, Convert( char(10), OFP#BB )
, Convert( char(10), OFFRM# )
, Convert( char(10), OFJOB# )
, ofyear
, ofmnth
, ofmag#
#prscol prs Join
OPENQUERY(SHEPB70, ‘SELECT distinct OFNOUP, OF#PAG, OFP#TT, OFP#TB, OFP#BT, OFP#BB, OFFRM#, OFJOB#, ofyear, ofmnth, ofmag# FROM files.OEFORM1 ‘) as tmp
ON Convert( char(10), OFJOB# ) = prs.prjob
AND Convert( char(10), OFFRM# ) = prs.prfrm
AND tmp.ofmag# = prs.prmag
What is really confusing is the error is on line 324, but the Begin transaction is on line 632. The linked_server (SHEPB70) in the insert statement is an AS400. I’m running Win2k adv. server sp3 and Sql2k sp3 on the server. Does anyone know what the problem is?

KBAs to review and resolve:
http://support.microsoft.com/default.aspx?scid=kb;en-us;329332 Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.