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

From: Steve Kass (skass_at_drew.edu)
Date: 08/22/04 

Date: Sat, 21 Aug 2004 23:39:56 -0500

Ian,

  See if this is at all related:

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

Steve Kass
Drew University

"Ian Boyd" <ian.msnews010@avatopia.com> wrote in message
news:uURBdR9hEHA.632@TK2MSFTNGP12.phx.gbl...
> 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

  • Linked server to as400 - for the love of god please help.
    ... i'm trying to setup a linked server between SQL Server and an AS400/DB2/IBM ... OLE DB provider 'MSDASQL' reported an error. ... OLE DB error trace [OLE/DB Provider 'IBMDA400' IColumnsInfo::MapColumnIDs ...
    (microsoft.public.sqlserver.server)
  • Linked Server Query Fails
    ... I am working with a linked server (using MS OLE DB Provider for ODBC Drivers) ... performing select queries from the ODBC DB and inserting the results into ...
    (microsoft.public.data.oledb)
  • 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)
  • Linked server for ODBC data source
    ... I have an Alpha box running open VMS which has an ODBC client from easysoft ... set up as a system dsn in ODBC data sources. ... How can I set this up as a linked server in SQL 2k? ... "Microsoft OLE DB Provider for ODBC Drivers" with the data source as ...
    (microsoft.public.sqlserver.connect)
  • 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)