SQL Server 2005 DB2 Linked Server Error When Inserting Result Set into a Temp Table

Question

We have a linked Server against DB2 on SQL Server 2005. When we tried putting a simple query result set from the linked sever into a temp table return this error.

OLE DB provider "MSDASQL" for linked server "SOMEDSN" returned message "[IBM][CLI Driver] SQL0998N Error occurred during transaction or heuristic processing. Reason Code = "16". Subcode = "2-8004D026". SQLSTATE=58005 ". Msg 7391, Level 16, State 2, Line 21 The operation could not be performed because OLE DB provider "MSDASQL" for linked server "SOMEDSN" was unable to begin a distributed transaction.

DECLARE @FilterCode varchar(20);
Set @FilterCode = '11122';

Create Table #TmpTable (
    Id Int
)

Insert Into #TmpTable
EXEC ('Select A.Id From SCM1.DB2TBL1 A Where A.FilterCode = ?', @FilterCode) At SOMEDSN;

The Exec works just fine with correct result set displayed, but as soon as we tried the result set into the temp table, then we get the error.

I searched here and many other places and tried the followings.

sp_configure 'remote proc trans', 0
On Linked Server Properties > Server Options set "Enable Promotion of Distributed Transactions" to false. I did not see this option.
On Linked Server Properties > RPC, RPC out and distributor are set to value TRUE
Used Openquery, but it cannot use dynamic SQL or pass parameters.

The table contains very many rows. We need to execute the query on the DB2 side to reduce the result set based on the parameter.

Any help would be greatly appropriated!

Mitch Wheat · Accepted Answer · 2013-02-26 00:14:03Z

0

Have you started the MSDTC service (DTC)?

Under Windows 2008:

From Administrative tools->Component services->Computers->My computer: Local DTC. Right click Properties, go to the MSDTC tab and select "Security Configuration".
Enable checkbox for "Network dtc access" and "Allow outbound".
Enable checkbox for "No Authentication Required"

If that does not work, try disabling connection pooling.

answered Feb 26, 2013 at 0:14

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2008421 Over a year ago

Thank you! We will try that tomorrow with our DBA.

Collectives™ on Stack Overflow

SQL Server 2005 DB2 Linked Server Error When Inserting Result Set into a Temp Table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related