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

Discussion:

(too old to reply)

Ian Boyd

2004-08-21 23:12:41 UTC

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')

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

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.

Pike

2004-08-22 02:29:09 UTC

Permalink

Besides MS kb, google comp.databases.ibm-db2.

Maybe check out:
StarSQL 5.1
http://www.starquest.com/Productfolder/starsql.html

There are other drivers out there too.

Good luck,

RAC v2.2 and QALite @
www.rac4sql.net

Ian Boyd

2004-08-22 02:53:52 UTC

Permalink

i need to beat someone in IBM to death, preferably with Mag-Lite flashlight.

i'm soooo tired of IBM.

Pike

2004-08-22 13:28:30 UTC

Permalink

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:)

Townie G

2004-09-09 15:23:52 UTC

Permalink

We've had similar nightmares here... and found out that depending on how
you are accessing your AS/400, it's randomly case sensitive and your
queries must be IN UPPERCASE!

God be with you as you fight the black beast!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Rob Sutton

2004-09-27 16:51:39 UTC

Permalink

I ran into same problem. I found instead of using linked server, just
use OPENROWSET with dsnless and linked serverless query, had all of the
same problems you had until I put this together with bits and pieces
gathered:

/*USES IBM CLIENT ACCESS ODBC DRIVER: */

SELECT * FROM OPENROWSET ('MSDASQL',
'Driver=Client Access ODBC Driver
(32-bit);Uid=youruserid;Pwd=yourpassword;System=ipaddressorsystemname;DB
Q=databaselibrary;CMT=0;NAM=0;
DFT=0;DSP=0;TFT=0;TSP=0;DEC=0;XDYNAMIC=1;RECBLOCK=1;BLOCKSIZE=8;SCROLLAB
LE=1;TRANSLATE=0;LAZYCLOSE=0;LIBVIEW=0;
REMARKS=0;CONNTYPE=0;SORTTYPE=0;PREFETCH=0;DFTPKGLIB=QGPL;LANGUAGEID=ENU
;SORTWEIGHT=0;MAXFIELDLEN=32;COMPRESSION=0;
ALLOWUNSCHAR=0;SEARCHPATTERN=1;MGDSN=0',

'
SELECT * FROM myas400_table
')

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Steve Kass

2004-08-22 04:39:56 UTC

Permalink

Ian,

See if this is at all related:

http://groups.google.com/groups?threadm=ukfmn4%23aEHA.2544%40TK2MSFTNGP10.phx.gbl

Steve Kass
Drew University

Post by Ian Boyd
i'm trying to setup a linked server between SQL Server and an

AS400/DB2/IBM

Post by Ian Boyd
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
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.
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],

Post by Ian Boyd
[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
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')

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

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

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

select * from sass400.wc400b.cms73dta.cspcm

select * from sass400..cms73dta.cspcm

ORDINAL=-1],

Post by Ian Boyd
[COLUMN_NAME=TABLE_TYPE ORDINAL=-1], [COLUMN_NAME=TABLE_GUID ORDINAL=-1]].

select * from sass400...cspcm

ORDINAL=-1],

Post by Ian Boyd
[COLUMN_NAME=TABLE_TYPE ORDINAL=-1], [COLUMN_NAME=TABLE_GUID ORDINAL=-1]].

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.

Ian Boyd

2004-08-22 11:14:37 UTC

Permalink

Nope.

And that's when it hit's you. You are SO tired of IBM.

Post by Steve Kass
Ian,

http://groups.google.com/groups?threadm=ukfmn4%23aEHA.2544%40TK2MSFTNGP10.phx.gbl

Post by Steve Kass
Steve Kass
Drew University

Post by Ian Boyd
i'm trying to setup a linked server between SQL Server and an

AS400/DB2/IBM

Post by Ian Boyd
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

Post by Ian Boyd
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

Post by Ian Boyd
0x80004005: ].
So i instead create a linked server using IBM's own OLEDB provider.
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

Post by Steve Kass
of

Post by Ian Boyd
object 'DBSCHEMA_TABLES'.
OLE DB error trace [OLE/DB Provider 'IBMDA400'

IColumnsInfo::MapColumnIDs