Using 4-part naming to do insert to DB2 from SQL Server 2005?

Question

Hello,

We're using DB2 9.1 on Linux and SQL Server 2005, and the IBM db2 odbc driver. We have a linked server set up to DB2. From SQL Server, I can do the following:

-- I can create the new table fine
exec ('create table dev.TestSylviaB (field1 int) in TS_DEV_USER_XXXX')
at LinkDB2

-- I can use 4 part naming to select from it, as long as I skip the second part and put everything in upper case
select * from LINKDB2..DEV.TESTSYLVIAB

-- I can insert using Exec (which does pass through)
exec ('insert into DEV.TestSylviaB (field1 ) values (1)') at LinkDB2

-- HOWEVER I cannot insert via the standard 4 part naming, like this:
insert into LINKDB2..DEV.TESTSYLVIAB values (1)

I get a "driver not capable" error message:

OLE DB provider "MSDASQL" for linked server "LINKDB2" returned message "[IBM][CLI Driver] CLI0150E Driver not capable. SQLSTATE=S1C00". Msg 7343, Level 16, State 2, Line 1 The OLE DB provider "MSDASQL" for linked server "LINKDB2" could not INSERT INTO table "[LINKDB2]..[DEV].[TESTSYLVIAB]".

Any thoughts?

thanks! Sylvia

Answer 1

The 4 part name insert is a distributed transaction and the DB2 driver needs to enroll into it. See Registering the IBM DB2 Driver for ODBC and CLI with the Microsoft DTC.