By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,501 Members | 1,660 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,501 IT Pros & Developers. It's quick & easy.

SQL Server inked server to as400 - for the love of god please help.

P: n/a
From a guy in Microsoft newsgroups:
| 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.

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"Amanda" <am***************@zunblvlda1.dyndns.org.spamsucks > wrote in
message news:cg*********@enews2.newsguy.com...
From a guy in Microsoft newsgroups:
| 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:)

DB2 for AS/400 is written in Rochester MN (I believe), sot the Toronto guys
may not be able to help.
Nov 12 '05 #2

P: n/a
The IBM OLE DB driver is somewhat problematic - it has improved a little in
UDB V8, but is still very troublesome. I strongly recommend you stick to the
DB2 UDB Connect ODBC driver and use the MS OLE DB over ODBC driver.

As far as I can make out from your posts, you are not using that driver -
you're using the "[IBM][Client Access Express ODBC Driver".

"Amanda" <am***************@zunblvlda1.dyndns.org.spamsucks > wrote in
message news:cg*********@enews2.newsguy.com...
From a guy in Microsoft newsgroups:
| 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.

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.