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
SELECT
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#
FROM
#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?kbid=306212
http://support.microsoft.com/default.aspx?scid=kb;en-us;329332 Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>