472,958 Members | 2,006 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,958 software developers and data experts.

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

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
2 17600
"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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Steve McDonald | last post by:
Can anyone help me??! What is the best way to import data from One World into SQL Server? If it's possible to use DTS then should I use the OLEDB connection object? Many thanks, Steve
3
by: fn | last post by:
Can anyone help me understand what it takes to define a Linked Server connection to an IBM eSeries (AS400)? Do I need Microsoft's SNA Server or some other product or can I simply do it with the...
0
by: Noor | last post by:
Hi, I'm trying to dug this issue since couple of days but couldn't find any solution. I have SQL server 2000 SP 3 and Host Integration Server 2000 SP 1 running. I'm able to download data from...
0
by: Daniel Adam | last post by:
Hi, I configured a linked server in MS SQL Server v8 pointing to a remote AS/400 DB2 database. I used Client Access ODBC driver (v7), installed it from a Client Access Express v4.5. ...
4
by: Scott Holland | last post by:
HELP - Need to connect to DB2 database on AIX from NT server. Also AS/400 from NT Server -- I am experienced in ORACLE and a novice at DB2. What tools would be the equivalent of Net*8 or...
13
by: Sehboo | last post by:
Hello, we have data sitting on AS400 (V4R5M0) - DB2-400. I need to access that from my vb.net application. I don't know anything about AS400. Is it possible to get data from tables and stored...
3
by: Mohan | last post by:
I had tried OLEDB (IBMDA400) with .net for Transactions. Transactions are not supported at all with OLEDB and AS400. Now I am trying ODBC.NET for transaction. Rollback does not work with ODBC.NE ...
0
by: sunrt | last post by:
We have an As400 system that has program which executes batch files which resides on the SQL server 7.0 computer . Those batch files run SQL server jobs using isql. The whole idea about this is...
0
by: fahrvergnuugen | last post by:
Here's the situation: I'm trying to get DB2 Connect, running on a linux web server, to connect to a backend DB2 database, running on an iSeries AS400. I am 100% green when it comes to AS400s...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.