-
Linked server - transaction interface not supported
Have following script:
Code:
declare @JobNumber1 char(12)
set @JobNumber1 = ' 3505040'
CREATE TABLE #JDE_EqmTable
( jde_cost_code nvarchar(15) unique,
jde_sum_hrs decimal(10,2),
jde_uom char(2)
)
Insert into #JDE_EqmTable(jde_cost_code, jde_sum_hrs, jde_uom)
Exec ('Call QGPL.get_eqmqty(?)', @JobNumber1) AT AS400SRV_MSOLEDB
drop table #JDE_EqmTable
Getting error:
The requested operation could not be performed because OLE DB provider "DB2OLEDB" for linked server "AS400SRV_MSOLEDB" does not support the required transaction interface.
Connection string:
Provider=DB2OLEDB;User ID=VGWPRJA;Password=AMMT7584;Initial Catalog=VGSYS400;Network Transport Library=TCP;Host CCSID=37;PC Code Page=1252;Network Address=VGSYS400;Network Port=446;Package Collection=QGPL;Process Binary as Character=True;Connect Timeout=15;Units of Work=RUW;DBMS Platform=DB2/AS400;Defer Prepare=False;Rowset Cache Size=0;Max Pool Size=100;Persist Security Info=True;Connection Pooling=True;Derive Parameters=False;
The problem seems to be with the "Insert into....". If I comment out the "Insert into..." line query works fine. Any idea why the insert is not supported?
Last edited by snufse; 04-08-2009 at 08:33 AM.
-
Do you have MSDTC enabled?. You may try BEGIN DISTRIBUTED TRANSACTION and COMMIT to wrap INSERT statement.
-
MSDTC is enabled.
declare @JobNumber1 char(12)
set @JobNumber1 = ' 3505040'
CREATE TABLE #JDE_EqmTable
( jde_cost_code nvarchar(15) unique,
jde_sum_hrs decimal(10,2),
jde_uom char(2)
)
BEGIN DISTRIBUTED TRANSACTION
Insert into #JDE_EqmTable(jde_cost_code, jde_sum_hrs, jde_uom)
Exec ('Call QGPL.get_eqmqty(?)', @JobNumber1) AT AS400SRV_MSOLEDB
COMMIT
drop table #JDE_EqmTable
Still getting same error.
Last edited by snufse; 04-08-2009 at 11:17 AM.
-
Tried create linked server with Microsoft odbc provider?
-
It works with MSDASQL. I shall try.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|