Linked server to as400 - for the love of god please help.

From: Ian Boyd (ian.msnews010_at_avatopia.com)
Date: 08/21/04 

Date: Sat, 21 Aug 2004 19:12:41 -0400

i'm trying to setup a linked server between SQL Server and an AS400/DB2/IBM
pos database.

There is an ODBC DSN on the server machine. Creating a linked server using
MSDASQL, and then querying for data, i (sometimes) get one row of data, then
the error:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver
(32-bit)]Driver not capable.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetNextRows returned
0x80004005: ].

So i instead create a linked server using IBM's own OLEDB provider.

When i query using 4 part notation:
    select * from sass400...CSPCM

i get the error:

Server: Msg 7351, Level 16, State 2, Line 1
OLE DB provider 'IBMDA400' could not map ordinals for one or more columns of
object 'DBSCHEMA_TABLES'.
OLE DB error trace [OLE/DB Provider 'IBMDA400' IColumnsInfo::MapColumnIDs
returned 0x80040e21: [COLUMN_NAME=TABLE_CATALOG ORDINAL=-1],
[COLUMN_NAME=TABLE_SCHEMA ORDINAL=-1], [COLUMN_NAME=TABLE_NAME ORDINAL=-1],
[COLUMN_NAME=TABLE_TYPE ORDINAL=-1], [COLUMN_NAME=TABLE_GUID ORDINAL=-1]].

When i query using OPENQUERY notation
    select * from openquery(sass400, 'select * from cspcm')

i get the error

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'IBMDA400' reported an error.
[OLE/DB provider returned message: SQL0204: CSPCM in CMSODBC type *FILE not
found.
Cause . . . . . : CSPCM in CMSODBC type *FILE was not found. If this is an
ALTER TABLE statement and the type is *N, a constraint was not found. If
this is not an ALTER TABLE statement and the type is *N, a function,
procedure, or trigger was not found. Recovery . . . : Change the name and
try the request again. If the object is a node group, ensure that the DB2
Multisystem product is installed on your system and create a nodegroup with
the CRTNODGRP CL command.]
[OLE/DB provider returned message: CWBDB0036 - Server returned SQL error ]
[OLE/DB provider returned message: CWBDB0036 - Server returned SQL error ]
OLE DB error trace [OLE/DB Provider 'IBMDA400' ICommandPrepare::Prepare
returned 0x80004005: ].

i can view the tables and views of the ODBC linked server, and get things
like the following:
    Name: CSPCM
    Schema: CMS73DTA
    Catalog: WC400B

If i try to view the tables and views of the OLEDB linked server, Enterprise
Manager locks up.

So i fiddling with my openquery notation and 4 part notation, using the
values above (CMS73DTA, WC400B)

> select * from openquery(sass400, 'select * from cms73dta.cspcm')

Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'select * from cms73dta.cspcm'. The OLE DB provider
'IBMDA400' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process
object, since the object has no columnsProviderName='IBMDA400', Query=select
* from cms73dta.cspcm'].

> select * from openquery(sass400, 'select * from wc400b.cspcm')

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'IBMDA400' reported an error.
[OLE/DB provider returned message: SQL0204: CSPCM in WC400B type *FILE not
found.
Cause . . . . . : CSPCM in WC400B type *FILE was not found. If this is an
ALTER TABLE statement and the type is *N, a constraint was not found. If
this is not an ALTER TABLE statement and the type is *N, a function,
procedure, or trigger was not found. Recovery . . . : Change the name and
try the request again. If the object is a node group, ensure that the DB2
Multisystem product is installed on your system and create a nodegroup with
the CRTNODGRP CL command.]
[OLE/DB provider returned message: CWBDB0036 - Server returned SQL error ]
[OLE/DB provider returned message: CWBDB0036 - Server returned SQL error ]
OLE DB error trace [OLE/DB Provider 'IBMDA400' ICommandPrepare::Prepare
returned 0x80004005: ].

> select * from openquery(sass400, 'select * from wc400b.cms73dta.cspcm')

Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'select * from wc400b.cms73dta.cspcm'. The OLE DB
provider 'IBMDA400' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process
object, since the object has no columnsProviderName='IBMDA400', Query=select
* from wc400b.cms73dta.cspcm'].

> select * from openquery(sass400, 'select * from cms73dta.cspcm')

Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'select * from cms73dta.cspcm'. The OLE DB provider
'IBMDA400' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process
object, since the object has no columnsProviderName='IBMDA400', Query=select
* from cms73dta.cspcm'].

> select * from sass400.wc400b.cms73dta.cspcm

Server: Msg 7312, Level 16, State 1, Line 1
Invalid use of schema and/or catalog for OLE DB provider 'IBMDA400'. A
four-part name was supplied, but the provider does not expose the necessary
interfaces to use a catalog and/or schema.
OLE DB error trace [Non-interface error].

> select * from sass400..cms73dta.cspcm

Server: Msg 7351, Level 16, State 2, Line 1
OLE DB provider 'IBMDA400' could not map ordinals for one or more columns of
object 'DBSCHEMA_TABLES'.
OLE DB error trace [OLE/DB Provider 'IBMDA400' IColumnsInfo::MapColumnIDs
returned 0x80040e21: [COLUMN_NAME=TABLE_CATALOG ORDINAL=-1],
[COLUMN_NAME=TABLE_SCHEMA ORDINAL=-1], [COLUMN_NAME=TABLE_NAME ORDINAL=-1],
[COLUMN_NAME=TABLE_TYPE ORDINAL=-1], [COLUMN_NAME=TABLE_GUID ORDINAL=-1]].

> select * from sass400...cspcm

Server: Msg 7351, Level 16, State 2, Line 1
OLE DB provider 'IBMDA400' could not map ordinals for one or more columns of
object 'DBSCHEMA_TABLES'.
OLE DB error trace [OLE/DB Provider 'IBMDA400' IColumnsInfo::MapColumnIDs
returned 0x80040e21: [COLUMN_NAME=TABLE_CATALOG ORDINAL=-1],
[COLUMN_NAME=TABLE_SCHEMA ORDINAL=-1], [COLUMN_NAME=TABLE_NAME ORDINAL=-1],
[COLUMN_NAME=TABLE_TYPE ORDINAL=-1], [COLUMN_NAME=TABLE_GUID ORDINAL=-1]].

And now back to the odbc linked server:

> select * from ballycms...cspcm
[one row of data]
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver
(32-bit)]Driver not capable.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetNextRows returned
0x80004005: ].

And yet, using ADO and the existing DSN, i can query and retrieve data fine.
Why can't SQL Server using ODBC work as well as ADO using ODBC?

i've been fighting with this for 3 months now - i just want data from an
ODBC source.

And then it hits you, you're so tired of IBM.

Relevant Pages

  • Re: Linked server from SQL Server to Access db on network share?
    ... What it looks like is this: SQL can create a linked server to a file on a remote share, but you can't query against it using Query Analyzer from any system other than the one hosting SQL... ... EXEC sp_tables_ex 'OLDSQL' ... OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. ...
    (microsoft.public.sqlserver.msde)
  • Re: Linked server to as400 - for the love of god please help.
    ... > There is an ODBC DSN on the server machine. ... Creating a linked server using ... > OLE DB provider 'MSDASQL' reported an error. ... > So i instead create a linked server using IBM's own OLEDB provider. ...
    (microsoft.public.sqlserver.server)
  • OLE DB Provider for ODBC and Oracle
    ... I created a linked server for MS SQL Server 2005 Express to an Oracle ... database using the OLE DB Provider for ODBC. ... I'm using the OLE DB Provider for ODBC instead of the Oracle OLE DB ...
    (comp.databases.ms-sqlserver)
  • Re: Cant edit Linked Server General Properties after saving
    ... OLE DB Provider for Oracle. ... Server dialog after creating the Linked Server the General Properties ...
    (comp.databases.ms-sqlserver)
  • Re: Querying OLAP Cube through SQL server and a linked server
    ... SQL BI Product Unit ... > An error occurred while preparing a query for execution against OLE DB ... > OLE DB error trace [Non-interface error: OLE DB provider MSOLAP.2 ...
    (microsoft.public.sqlserver.olap)