server to Oracle

Hi, I am developing DTS that extract data from Oracle via
Linked server. I'm using MS OLE DB for Oracle.
The following error message prompted when I query to
selected tables (12 tables out of 40+ tables) in Oracle.
Server: Msg 7317, Level 16, State 1, Line 1 OLE DB
provider 'MSDAORA' returned an invalid schema definition.
OLE DB error trace [Non-interface error: OLE/DB provider
returned an invalid schema definition.].
One scenario where you can get the error is with data types
that aren't supported by the OLE DB provider or by SQL
Server.
Can you query the tables using just an Openquery in Query
Analyzer?
You would probably want to check the documentation for the
driver to determine what data types are supported. Also,
make sure you are using the latest provider, Oracle client.
-Sue
On Mon, 23 Aug 2004 10:10:22 -0700, "James"
<james@.silverglobe.com> wrote:

>Hi, I am developing DTS that extract data from Oracle via
>Linked server. I'm using MS OLE DB for Oracle.
>The following error message prompted when I query to
>selected tables (12 tables out of 40+ tables) in Oracle.
>Server: Msg 7317, Level 16, State 1, Line 1 OLE DB
>provider 'MSDAORA' returned an invalid schema definition.
>OLE DB error trace [Non-interface error: OLE/DB provider
>returned an invalid schema definition.].
|||Hi James,
Thanks for using MSDN Managed Newsgroup!
Thanks the perfect answer from Sue!
From your descriptions, I understood you meet the error 7317 when using DTS
push data from Oracle. Have I understood you? If there is anything I
misunderstood, please feel free to let me know.
Bbased on my knowledge, you may have encounter an known issue in MDAC. The
provider asks Oracle for a list of the column names in an Oracle index. For
most indexes the internal name Oracle has for the index columns is the same
as the actual column names. However, if the Oracle index is descending or
is a function_based index, it does not return actual column names, it
returns a generated name of some sort. Our Oracle provider does not
distinguish between the results and it tries to use the returned values as
actual column names, resulting in the "invalid schema definition". I cannot
tell for sure from the internal documentation, but this behavior may have
changed between versions on the Oracle side. Our Oracle provider has not
been updated recently, to take advantage of newer Oracle functionality you
need to use Oracle's provider instead of ours.
You'd better use four-part name syntax correct this issue. Use the query
like this
SELECT * FROM OPENQUERY(WACRPPRD, 'select
LAST_NAME,FIRST_NAME,PH_NUM,PAGER,DIRECT_PHONE,EXT ENSION,EMAIL_ADDR,TITLE,DE
PT_DESC,
OFFICE_SITE,SUPV_PH_NUM from PH.QBS_PH_PEOPLE where PH_NUM is not null and
ACTIVE_FLAG = ''Y'' order by QPE_EMP_NUM')
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Sue and Mingqing,
Thanks for the promptly reply.
I tried using open query and it works. Thus, I assume is
the version incompatibility issue. Will check it out
tomorrow and post another message for the benefit of the
others.
rgrds,
James Gan HJ

>--Original Message--
>Hi James,
>Thanks for using MSDN Managed Newsgroup!
>Thanks the perfect answer from Sue!
>From your descriptions, I understood you meet the error
7317 when using DTS
>push data from Oracle. Have I understood you? If there is
anything I
>misunderstood, please feel free to let me know.
>Bbased on my knowledge, you may have encounter an known
issue in MDAC. The
>provider asks Oracle for a list of the column names in an
Oracle index. For
>most indexes the internal name Oracle has for the index
columns is the same
>as the actual column names. However, if the Oracle index
is descending or
>is a function_based index, it does not return actual
column names, it
>returns a generated name of some sort. Our Oracle
provider does not
>distinguish between the results and it tries to use the
returned values as
>actual column names, resulting in the "invalid schema
definition". I cannot
>tell for sure from the internal documentation, but this
behavior may have
>changed between versions on the Oracle side. Our Oracle
provider has not
>been updated recently, to take advantage of newer Oracle
functionality you
>need to use Oracle's provider instead of ours.
>You'd better use four-part name syntax correct this
issue. Use the query
>like this
>SELECT * FROM OPENQUERY(WACRPPRD, 'select
>LAST_NAME,FIRST_NAME,PH_NUM,PAGER,DIRECT_PHONE,EX TENSION,E
MAIL_ADDR,TITLE,DE
>PT_DESC,
>OFFICE_SITE,SUPV_PH_NUM from PH.QBS_PH_PEOPLE where
PH_NUM is not null and
>ACTIVE_FLAG = ''Y'' order by QPE_EMP_NUM')
>Thank you for your patience and cooperation. If you have
any questions or
>concerns, don't hesitate to let me know. We are here to
be of assistance!
>
>Sincerely yours,
>Mingqing Cheng
>Microsoft Developer Community Support
>----
--
>Introduction to Yukon! -
http://www.microsoft.com/sql/yukon
>This posting is provided "as is" with no warranties and
confers no rights.
>Please reply to newsgroups only, many thanks!
>
>
>
>
>.
>
|||Hi, here's my spec.
Oracle server = Oracle Server 8i release 8.1.7.4
SQL Server which I need to establish a linked server connection=
OLE DB or Oracle version 2.71.9030
Oracle SQL Plus Release 9.2.0.1.0
Does this mean that I need to use the Oracle SQL client v 8?
I tried the latest MDAC with OLE DB for Oracle 2.8, it doesn't work as well.
rgrds,
James Gan HJ
""Mingqing Cheng [MSFT]"" wrote:

> Hi James,
> Thanks for using MSDN Managed Newsgroup!
> Thanks the perfect answer from Sue!
> From your descriptions, I understood you meet the error 7317 when using DTS
> push data from Oracle. Have I understood you? If there is anything I
> misunderstood, please feel free to let me know.
> Bbased on my knowledge, you may have encounter an known issue in MDAC. The
> provider asks Oracle for a list of the column names in an Oracle index. For
> most indexes the internal name Oracle has for the index columns is the same
> as the actual column names. However, if the Oracle index is descending or
> is a function_based index, it does not return actual column names, it
> returns a generated name of some sort. Our Oracle provider does not
> distinguish between the results and it tries to use the returned values as
> actual column names, resulting in the "invalid schema definition". I cannot
> tell for sure from the internal documentation, but this behavior may have
> changed between versions on the Oracle side. Our Oracle provider has not
> been updated recently, to take advantage of newer Oracle functionality you
> need to use Oracle's provider instead of ours.
> You'd better use four-part name syntax correct this issue. Use the query
> like this
> SELECT * FROM OPENQUERY(WACRPPRD, 'select
> LAST_NAME,FIRST_NAME,PH_NUM,PAGER,DIRECT_PHONE,EXT ENSION,EMAIL_ADDR,TITLE,DE
> PT_DESC,
> OFFICE_SITE,SUPV_PH_NUM from PH.QBS_PH_PEOPLE where PH_NUM is not null and
> ACTIVE_FLAG = ''Y'' order by QPE_EMP_NUM')
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>
>
>
>
|||No, you don't necessarily need to go back to the v 8 Client.
What task are you using and how are you querying the Oracle
database in the task? When you hit data type issues,
sometimes it's better to just use pass-through queries with
Openquery as long as that works. It's generally faster
against an Oracle linked server anyway.
-Sue
On Mon, 30 Aug 2004 00:29:02 -0700, "James"
<James@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Hi, here's my spec.
>Oracle server = Oracle Server 8i release 8.1.7.4
>SQL Server which I need to establish a linked server connection=
>OLE DB or Oracle version 2.71.9030
>Oracle SQL Plus Release 9.2.0.1.0
>Does this mean that I need to use the Oracle SQL client v 8?
>I tried the latest MDAC with OLE DB for Oracle 2.8, it doesn't work as well.
>rgrds,
>James Gan HJ
>
>""Mingqing Cheng [MSFT]"" wrote:
|||Yep, open query works. Thanks.
So, the rule of thumb is to always use open query?
"Sue Hoegemeier" wrote:

> No, you don't necessarily need to go back to the v 8 Client.
> What task are you using and how are you querying the Oracle
> database in the task? When you hit data type issues,
> sometimes it's better to just use pass-through queries with
> Openquery as long as that works. It's generally faster
> against an Oracle linked server anyway.
> -Sue
> On Mon, 30 Aug 2004 00:29:02 -0700, "James"
> <James@.discussions.microsoft.com> wrote:
>
>
|||Not necessarily but in your case it seems appropriate and
openquery will generally be faster - especially with Oracle.
-Sue
On Tue, 31 Aug 2004 03:35:08 -0700, "James"
<James@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Yep, open query works. Thanks.
>So, the rule of thumb is to always use open query?
>
>"Sue Hoegemeier" wrote: