Problem with Linked Server from SQL to DB2

• Previous message: Chris Stolte: "Collation"
• Messages sorted by: [ date ] [ thread ]

Date: Fri, 22 Oct 2004 08:39:05 -0700

I originally posted this to sqlserver.server, however didn't get any
response. I thought maybe this would be a better forum, so please excuse the
cross-post...

I'm trying to setup a linked server from SQL to DB2 UDB (on AIX). We want
to be able to issue SQL statements (SELECT, INSERT, UPDATE, and DELETE) using
the four-part name (i.e. MyLS.DBName.Schema.TableName). I've tried both the
Microsoft provided OLE DB driver for DB2 (DB2OLEDB) and the IBM DB2 OLE DB
Driver (IBMDADB2). Each driver ended up with different results. The SQL
commands to setup both linked servers (one Microsft driver and one IBM
driver) are included at the bottom of this post (with IP, Port, User ID, and
password omitted of course). Also the SELECT commands and their results are
included.

The linked server using the IBM driver allows me to run SELECT queries using
the OPENQUERY method, however not using the 4-part name. The linked server
using the Microsoft driver does not appear to work at all.

I've looked through many newsgroup posts and Microsoft articles, but have
not been able to find a solution. Any help would be greatly appreciated.

Thanks.

Paul

--+++IBM
EXEC sp_addlinkedserver
        @server = 'LS_IBMUsingSP',
        @srvproduct = 'SAMPLE',
        @provider = 'IBMDADB2',
        @datasrc = 'SAMPLE',
        @location = 'xxx.xxx.xxx.xxx:xxxx',
         @provstr = 'Provider=IBMDADB2;Password=xxxxxx;Persist Security
Info=True;User ID=xxxxxx;Data
Source=SAMPLE;Location=xxx.xxx.xxx.xxx:xxxx;Extended Properties=""',
         @catalog = 'SAMPLE'

EXEC sp_addlinkedsrvlogin
        @rmtsrvname = 'LS_IBMUsingSP',
        @useself = false,
        @rmtuser = 'xxxxxx',
        @rmtpassword = 'xxxxxx'

EXEC sp_serveroption
        @server = 'LS_IBMUsingSP',
        @optname = 'data access',
        @optvalue = 'true'

SELECT *
FROM LS_IBMUsingSP.[SAMPLE].DB2TDBM.DEPARTMENT

-- Invalid schema or catalog specified for provider 'IBMDADB2'.
-- OLE DB error trace [Non-interface error: Invalid schema or catalog
specified for the provider.].

SELECT *
FROM OPENQUERY(LS_IBMUsingSP, 'SELECT * FROM DB2TDBM.DEPARTMENT')

-- This works fine.

-----IBM

--+++MICROSOFT
EXEC sp_addlinkedserver
        @server = 'LS_MicrosoftUsingSP',
        @srvproduct = 'SAMPLE',
        @provider = 'DB2OLEDB',
        @datasrc = 'SAMPLE',
        @location = 'xxx.xxx.xxx.xxx:xxxx',
         @provstr = 'Provider=DB2OLEDB;Password=xxxxxx;Persist Security
Info=True;User ID=xxxxxx;Initial Catalog=SAMPLE;Data Source=SAMPLE;Network
Transport Library=TCPIP;Network Address=xxx.xxx.xxx.xxx;Network
Port=xxxx;Package Collection=db2tdbm;Default Schema=db2tdbm;Units of
Work=DUW',
        @catalog = SAMPLE

EXEC sp_addlinkedsrvlogin
        @rmtsrvname = 'LS_MicrosoftUsingSP',
        @useself = false,
        @rmtuser = 'xxxxxx',
        @rmtpassword = 'xxxxxx'

EXEC sp_serveroption
        @server = 'LS_MicrosoftUsingSP',
        @optname = 'data access',
        @optvalue = 'true'

DBCC TRACEON(7300, 3604)

SELECT *
FROM LS_MicrosoftUsingSP.[SAMPLE].DB2TDBM.DEPARTMENT

-- Could not fetch a row from OLE DB provider 'DB2OLEDB'.
-- OLE DB error trace [Non-interface error: OLE DB provider DB2OLEDB
returned DBPROP_STRUCTUREDSTORAGE without DBPROPVAL_OO_BLOB being supported].
-- [OLE/DB provider returned message: One or more errors occurred during
processing of command.]
-- [OLE/DB provider returned message: A SQL error has occurred. Please
consult the documentation for your specific DB2 version for a description of
the associated Native Error and SQL State. SQLSTATE: 51002, SQLCODE: -805]
-- OLE DB error trace [OLE/DB Provider 'DB2OLEDB' IRowset::GetNextRows
returned 0x80040e14].

SELECT *
FROM OPENQUERY(LS_MicrosoftUsingSP, 'SELECT * FROM DB2TDBM.DEPARTMENT')

-- An error occurred while preparing a query for execution against OLE DB
provider 'DB2OLEDB'.
-- OLE DB error trace [Non-interface error: OLE DB provider DB2OLEDB
returned DBPROP_STRUCTUREDSTORAGE without DBPROPVAL_OO_BLOB being supported].
-- [OLE/DB provider returned message: A SQL error has occurred. Please
consult the documentation for your specific DB2 version for a description of
the associated Native Error and SQL State. SQLSTATE: 51002, SQLCODE: -805]
-- OLE DB error trace [OLE/DB Provider 'DB2OLEDB' ICommandPrepare::Prepare
returned 0x80040e14].
-----MICROSOFT

• Previous message: Chris Stolte: "Collation"
• Messages sorted by: [ date ] [ thread ]