| In *comp.databases.ibm-db2* there are always IBM guys
| from the Toronto labs on line.Post with the
| -for the love of god please help-
| line and I'm sure you'll get their attention.
| Their usually very good:)
So here's my transplanted post
==========================================
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.
====================================
postscript for the db2 folks
1. i don't know what version of ClientAccessExpress drivers i'm using. But
it doesn't matter, i cannot change them. Maybe, if i was guaranteed it to be
fixed, i could write up a thesis on why they have to get and install newer
drivers - but it's not going to happen.
2. If i query the AS400 using ODBC from 3rd party tools (WinSQL), it works
fine.
3. If i query the AS400 using ADO through ODBC, it works fine.
It fails when SQL Server is using the ODBC provider - presumably because the
IBM driver is not fully or properly implemented.
