Is it possible to link a Microsft Access table in Sql? What I am hoping to d
o
is to update an Access table using a DTS package. I need to join a SQL table
and a Access table and use the results to update anouther Access table. Is
this possible.
Thank you in advanceBob,
Sure - see:
sp_addlinkedserver
http://msdn.microsoft.com/library/d... />
a_8gqa.asp
and
sp_addlinkedsrvlogin
http://msdn.microsoft.com/library/d... />
a_6e26.asp
HTH
Jerry
"bob at zachys" <bobatzachys@.discussions.microsoft.com> wrote in message
news:8B29F201-D4C7-4981-8E6B-26CAA6C7E855@.microsoft.com...
> Is it possible to link a Microsft Access table in Sql? What I am hoping to
> do
> is to update an Access table using a DTS package. I need to join a SQL
> table
> and a Access table and use the results to update anouther Access table. Is
> this possible.
> Thank you in advance
Showing posts with label dts. Show all posts
Showing posts with label dts. Show all posts
Friday, March 30, 2012
Linking from SQL server to MySQL
Hi all, hoping someone can help.
I have created an ODBC connection to a remote MySQL database.
I can then go into DTS wizard and specify this ODBC connection, it all goes through fine and I can see the tables on the remote server and import data.
The problem arises when I try and create a linked server using the same ODBC connection. I get the following error, when I try to view the tables.
I am so stuck. Any help would be very much appreciated.My first guess would be a permissions issue in the MySQL login used within the DSN, or an IP permission problem in the MySQL login. I don't have any easy way to set up a test case this week, but those would be where I'd start looking.
-PatP|||Really don't know a lot about MySQL permissions so any more help would be great, so I can go back to are website designers with suggestions of what the problem might be on there end.|||The security and permissions for MySQL work a lot different than they do for SQL Server.
The IP address figures prominently into MySQL permissions. A login might have one set of permissions at one ip address or range (say inside the firewall), and drastically different permissions at others. If you ran your DTS package from a machine with an IP address that was "blessed", you could have one set of permissions, but when making a linked server connection on the SQL Server (which would have another address), the same MySQL id might have much less permission.
Another possibility is that the ODBC driver on the SQL Server might not be current. It has been relatively recent that the MySQL team has put any effort into making the ODBC drivers compatible with Windows 2003 Server.
The biggest problem at this point is that there are so many ways for the connection to go bad, and the MySQL ODBC drivers still aren't very good at giving usable diagnostics (as you've noticed!). The problem is almost certainly solvable, but without help from someone pretty technically knowledgable that understands both your NT and your MySQL configuration, it may take many tries to work out the problem and its solution.
-PatP
I have created an ODBC connection to a remote MySQL database.
I can then go into DTS wizard and specify this ODBC connection, it all goes through fine and I can see the tables on the remote server and import data.
The problem arises when I try and create a linked server using the same ODBC connection. I get the following error, when I try to view the tables.
I am so stuck. Any help would be very much appreciated.My first guess would be a permissions issue in the MySQL login used within the DSN, or an IP permission problem in the MySQL login. I don't have any easy way to set up a test case this week, but those would be where I'd start looking.
-PatP|||Really don't know a lot about MySQL permissions so any more help would be great, so I can go back to are website designers with suggestions of what the problem might be on there end.|||The security and permissions for MySQL work a lot different than they do for SQL Server.
The IP address figures prominently into MySQL permissions. A login might have one set of permissions at one ip address or range (say inside the firewall), and drastically different permissions at others. If you ran your DTS package from a machine with an IP address that was "blessed", you could have one set of permissions, but when making a linked server connection on the SQL Server (which would have another address), the same MySQL id might have much less permission.
Another possibility is that the ODBC driver on the SQL Server might not be current. It has been relatively recent that the MySQL team has put any effort into making the ODBC drivers compatible with Windows 2003 Server.
The biggest problem at this point is that there are so many ways for the connection to go bad, and the MySQL ODBC drivers still aren't very good at giving usable diagnostics (as you've noticed!). The problem is almost certainly solvable, but without help from someone pretty technically knowledgable that understands both your NT and your MySQL configuration, it may take many tries to work out the problem and its solution.
-PatP
Friday, February 24, 2012
server to Oracle
I'm trying to pass a query from sql server 2000 to Oracle using linked
servers. I don't want to use DTS. While it's easy enough to use OPENQUERY
to pass thru a query that returns a dataset, I can't seem to pass thru a
query that doesn't return a dataset eg a create table query or a drop table
query. If I try, I get an error saying that the query returns no columns.
Is it possible to pass thru a query that returns no columns using a linked
server?
For example, the following query:
select *
from OPENQUERY(ORA8I,'CREATE TABLE MYTABLE AS SELECT * FROM EMP')
returns the error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'CREATE TABLE MYTABLE AS SELECT * FROM EMP'. The
OLE DB provider 'MSDAORA' 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='MSDAORA', Query=CREATE
TABLE MYTABLE AS SELECT * FROM EMP'].
arch (Sorry , cannot test it right now)
SELECT *
FROM
OPENQUERY(ORA8I,'SELECT * INTO MYTABLE FROM EMP')
If it does not work you may want to try
CREATE FUNCTION dbo.fn_getdata()
AS
RETURNS TABLE
AS
BEGIN
RETURN(
SELECT *
FROM OPENQUERY(
[server_name],
'SET NOCOUNT ON;
SELECT * INTO database.MyTable FROM DataBase.EMP;') AS O)
END
"arch" <archangel@.arach.net.au> wrote in message
news:newscache$rzf9ui$m9c$1@.phantom.amnet.net.au.. .
> I'm trying to pass a query from sql server 2000 to Oracle using linked
> servers. I don't want to use DTS. While it's easy enough to use
> OPENQUERY
> to pass thru a query that returns a dataset, I can't seem to pass thru a
> query that doesn't return a dataset eg a create table query or a drop
> table
> query. If I try, I get an error saying that the query returns no columns.
> Is it possible to pass thru a query that returns no columns using a linked
> server?
> For example, the following query:
> select *
> from OPENQUERY(ORA8I,'CREATE TABLE MYTABLE AS SELECT * FROM EMP')
> returns the error:
> Server: Msg 7357, Level 16, State 2, Line 1
> Could not process object 'CREATE TABLE MYTABLE AS SELECT * FROM EMP'. The
> OLE DB provider 'MSDAORA' 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='MSDAORA',
> Query=CREATE
> TABLE MYTABLE AS SELECT * FROM EMP'].
>
>
|||No luck with any of that.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e%23A$6awKGHA.2628@.TK2MSFTNGP15.phx.gbl...
> arch (Sorry , cannot test it right now)
> SELECT *
> FROM
> OPENQUERY(ORA8I,'SELECT * INTO MYTABLE FROM EMP')
> If it does not work you may want to try
> CREATE FUNCTION dbo.fn_getdata()
> AS
> RETURNS TABLE
> AS
> BEGIN
> RETURN(
> SELECT *
> FROM OPENQUERY(
> [server_name],
> 'SET NOCOUNT ON;
> SELECT * INTO database.MyTable FROM DataBase.EMP;') AS O)
> END
>
>
> "arch" <archangel@.arach.net.au> wrote in message
> news:newscache$rzf9ui$m9c$1@.phantom.amnet.net.au.. .
>
servers. I don't want to use DTS. While it's easy enough to use OPENQUERY
to pass thru a query that returns a dataset, I can't seem to pass thru a
query that doesn't return a dataset eg a create table query or a drop table
query. If I try, I get an error saying that the query returns no columns.
Is it possible to pass thru a query that returns no columns using a linked
server?
For example, the following query:
select *
from OPENQUERY(ORA8I,'CREATE TABLE MYTABLE AS SELECT * FROM EMP')
returns the error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'CREATE TABLE MYTABLE AS SELECT * FROM EMP'. The
OLE DB provider 'MSDAORA' 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='MSDAORA', Query=CREATE
TABLE MYTABLE AS SELECT * FROM EMP'].
arch (Sorry , cannot test it right now)
SELECT *
FROM
OPENQUERY(ORA8I,'SELECT * INTO MYTABLE FROM EMP')
If it does not work you may want to try
CREATE FUNCTION dbo.fn_getdata()
AS
RETURNS TABLE
AS
BEGIN
RETURN(
SELECT *
FROM OPENQUERY(
[server_name],
'SET NOCOUNT ON;
SELECT * INTO database.MyTable FROM DataBase.EMP;') AS O)
END
"arch" <archangel@.arach.net.au> wrote in message
news:newscache$rzf9ui$m9c$1@.phantom.amnet.net.au.. .
> I'm trying to pass a query from sql server 2000 to Oracle using linked
> servers. I don't want to use DTS. While it's easy enough to use
> OPENQUERY
> to pass thru a query that returns a dataset, I can't seem to pass thru a
> query that doesn't return a dataset eg a create table query or a drop
> table
> query. If I try, I get an error saying that the query returns no columns.
> Is it possible to pass thru a query that returns no columns using a linked
> server?
> For example, the following query:
> select *
> from OPENQUERY(ORA8I,'CREATE TABLE MYTABLE AS SELECT * FROM EMP')
> returns the error:
> Server: Msg 7357, Level 16, State 2, Line 1
> Could not process object 'CREATE TABLE MYTABLE AS SELECT * FROM EMP'. The
> OLE DB provider 'MSDAORA' 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='MSDAORA',
> Query=CREATE
> TABLE MYTABLE AS SELECT * FROM EMP'].
>
>
|||No luck with any of that.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e%23A$6awKGHA.2628@.TK2MSFTNGP15.phx.gbl...
> arch (Sorry , cannot test it right now)
> SELECT *
> FROM
> OPENQUERY(ORA8I,'SELECT * INTO MYTABLE FROM EMP')
> If it does not work you may want to try
> CREATE FUNCTION dbo.fn_getdata()
> AS
> RETURNS TABLE
> AS
> BEGIN
> RETURN(
> SELECT *
> FROM OPENQUERY(
> [server_name],
> 'SET NOCOUNT ON;
> SELECT * INTO database.MyTable FROM DataBase.EMP;') AS O)
> END
>
>
> "arch" <archangel@.arach.net.au> wrote in message
> news:newscache$rzf9ui$m9c$1@.phantom.amnet.net.au.. .
>
server to Oracle
I'm trying to pass a query from sql server 2000 to Oracle using linked
servers. I don't want to use DTS. While it's easy enough to use OPENQUERY
to pass thru a query that returns a dataset, I can't seem to pass thru a
query that doesn't return a dataset eg a create table query or a drop table
query. If I try, I get an error saying that the query returns no columns.
Is it possible to pass thru a query that returns no columns using a linked
server?
For example, the following query:
select *
from OPENQUERY(ORA8I,'CREATE TABLE MYTABLE AS SELECT * FROM EMP')
returns the error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'CREATE TABLE MYTABLE AS SELECT * FROM EMP'. The
OLE DB provider 'MSDAORA' 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='MSDAORA', Query=CREATE
TABLE MYTABLE AS SELECT * FROM EMP'].arch (Sorry , cannot test it right now)
SELECT *
FROM
OPENQUERY(ORA8I,'SELECT * INTO MYTABLE FROM EMP')
If it does not work you may want to try
CREATE FUNCTION dbo.fn_getdata()
AS
RETURNS TABLE
AS
BEGIN
RETURN(
SELECT *
FROM OPENQUERY(
[server_name],
'SET NOCOUNT ON;
SELECT * INTO database.MyTable FROM DataBase.EMP;') AS O)
END
"arch" <archangel@.arach.net.au> wrote in message
news:newscache$rzf9ui$m9c$1@.phantom.amnet.net.au...
> I'm trying to pass a query from sql server 2000 to Oracle using linked
> servers. I don't want to use DTS. While it's easy enough to use
> OPENQUERY
> to pass thru a query that returns a dataset, I can't seem to pass thru a
> query that doesn't return a dataset eg a create table query or a drop
> table
> query. If I try, I get an error saying that the query returns no columns.
> Is it possible to pass thru a query that returns no columns using a linked
> server?
> For example, the following query:
> select *
> from OPENQUERY(ORA8I,'CREATE TABLE MYTABLE AS SELECT * FROM EMP')
> returns the error:
> Server: Msg 7357, Level 16, State 2, Line 1
> Could not process object 'CREATE TABLE MYTABLE AS SELECT * FROM EMP'. The
> OLE DB provider 'MSDAORA' 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='MSDAORA',
> Query=CREATE
> TABLE MYTABLE AS SELECT * FROM EMP'].
>
>|||No luck with any of that.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e%23A$6awKGHA.2628@.TK2MSFTNGP15.phx.gbl...
> arch (Sorry , cannot test it right now)
> SELECT *
> FROM
> OPENQUERY(ORA8I,'SELECT * INTO MYTABLE FROM EMP')
> If it does not work you may want to try
> CREATE FUNCTION dbo.fn_getdata()
> AS
> RETURNS TABLE
> AS
> BEGIN
> RETURN(
> SELECT *
> FROM OPENQUERY(
> [server_name],
> 'SET NOCOUNT ON;
> SELECT * INTO database.MyTable FROM DataBase.EMP;') AS O)
> END
>
>
> "arch" <archangel@.arach.net.au> wrote in message
> news:newscache$rzf9ui$m9c$1@.phantom.amnet.net.au...
>> I'm trying to pass a query from sql server 2000 to Oracle using linked
>> servers. I don't want to use DTS. While it's easy enough to use
>> OPENQUERY
>> to pass thru a query that returns a dataset, I can't seem to pass thru a
>> query that doesn't return a dataset eg a create table query or a drop
>> table
>> query. If I try, I get an error saying that the query returns no
>> columns.
>> Is it possible to pass thru a query that returns no columns using a
>> linked
>> server?
>> For example, the following query:
>> select *
>> from OPENQUERY(ORA8I,'CREATE TABLE MYTABLE AS SELECT * FROM EMP')
>> returns the error:
>> Server: Msg 7357, Level 16, State 2, Line 1
>> Could not process object 'CREATE TABLE MYTABLE AS SELECT * FROM EMP'. The
>> OLE DB provider 'MSDAORA' 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='MSDAORA',
>> Query=CREATE
>> TABLE MYTABLE AS SELECT * FROM EMP'].
>>
>
servers. I don't want to use DTS. While it's easy enough to use OPENQUERY
to pass thru a query that returns a dataset, I can't seem to pass thru a
query that doesn't return a dataset eg a create table query or a drop table
query. If I try, I get an error saying that the query returns no columns.
Is it possible to pass thru a query that returns no columns using a linked
server?
For example, the following query:
select *
from OPENQUERY(ORA8I,'CREATE TABLE MYTABLE AS SELECT * FROM EMP')
returns the error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'CREATE TABLE MYTABLE AS SELECT * FROM EMP'. The
OLE DB provider 'MSDAORA' 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='MSDAORA', Query=CREATE
TABLE MYTABLE AS SELECT * FROM EMP'].arch (Sorry , cannot test it right now)
SELECT *
FROM
OPENQUERY(ORA8I,'SELECT * INTO MYTABLE FROM EMP')
If it does not work you may want to try
CREATE FUNCTION dbo.fn_getdata()
AS
RETURNS TABLE
AS
BEGIN
RETURN(
SELECT *
FROM OPENQUERY(
[server_name],
'SET NOCOUNT ON;
SELECT * INTO database.MyTable FROM DataBase.EMP;') AS O)
END
"arch" <archangel@.arach.net.au> wrote in message
news:newscache$rzf9ui$m9c$1@.phantom.amnet.net.au...
> I'm trying to pass a query from sql server 2000 to Oracle using linked
> servers. I don't want to use DTS. While it's easy enough to use
> OPENQUERY
> to pass thru a query that returns a dataset, I can't seem to pass thru a
> query that doesn't return a dataset eg a create table query or a drop
> table
> query. If I try, I get an error saying that the query returns no columns.
> Is it possible to pass thru a query that returns no columns using a linked
> server?
> For example, the following query:
> select *
> from OPENQUERY(ORA8I,'CREATE TABLE MYTABLE AS SELECT * FROM EMP')
> returns the error:
> Server: Msg 7357, Level 16, State 2, Line 1
> Could not process object 'CREATE TABLE MYTABLE AS SELECT * FROM EMP'. The
> OLE DB provider 'MSDAORA' 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='MSDAORA',
> Query=CREATE
> TABLE MYTABLE AS SELECT * FROM EMP'].
>
>|||No luck with any of that.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e%23A$6awKGHA.2628@.TK2MSFTNGP15.phx.gbl...
> arch (Sorry , cannot test it right now)
> SELECT *
> FROM
> OPENQUERY(ORA8I,'SELECT * INTO MYTABLE FROM EMP')
> If it does not work you may want to try
> CREATE FUNCTION dbo.fn_getdata()
> AS
> RETURNS TABLE
> AS
> BEGIN
> RETURN(
> SELECT *
> FROM OPENQUERY(
> [server_name],
> 'SET NOCOUNT ON;
> SELECT * INTO database.MyTable FROM DataBase.EMP;') AS O)
> END
>
>
> "arch" <archangel@.arach.net.au> wrote in message
> news:newscache$rzf9ui$m9c$1@.phantom.amnet.net.au...
>> I'm trying to pass a query from sql server 2000 to Oracle using linked
>> servers. I don't want to use DTS. While it's easy enough to use
>> OPENQUERY
>> to pass thru a query that returns a dataset, I can't seem to pass thru a
>> query that doesn't return a dataset eg a create table query or a drop
>> table
>> query. If I try, I get an error saying that the query returns no
>> columns.
>> Is it possible to pass thru a query that returns no columns using a
>> linked
>> server?
>> For example, the following query:
>> select *
>> from OPENQUERY(ORA8I,'CREATE TABLE MYTABLE AS SELECT * FROM EMP')
>> returns the error:
>> Server: Msg 7357, Level 16, State 2, Line 1
>> Could not process object 'CREATE TABLE MYTABLE AS SELECT * FROM EMP'. The
>> OLE DB provider 'MSDAORA' 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='MSDAORA',
>> Query=CREATE
>> TABLE MYTABLE AS SELECT * FROM EMP'].
>>
>
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,EXTENSION,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,EXTENSION,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 DT
S
> 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. Fo
r
> most indexes the internal name Oracle has for the index columns is the sam
e
> 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 canno
t
> 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,EXTENSION,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:
>
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,EXTENSION,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,EXTENSION,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 DT
S
> 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. Fo
r
> most indexes the internal name Oracle has for the index columns is the sam
e
> 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 canno
t
> 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,EXTENSION,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:
>
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.].Hi James,
I have noticed you make another new thread in
microsoft.public.sqlserver.connect. Community member Sue and I have all add
a reply in that thread. I will also follow up in that thread if you have
any further questions on this issue.
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 !
I have the same problem and cannot find the post on microsoft.public.sqlserv
er.connect could you please repost it here ?
thanx
regards
max
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,
I have noticed you make another new thread in
microsoft.public.sqlserver.connect. Community member Sue and I have all add
a reply in that thread. I will also follow up in that thread if you have
any further questions on this issue.
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 !
I have the same problem and cannot find the post on microsoft.public.sqlserv
er.connect could you please repost it here ?
thanx
regards
max
quote:
Originally posted by Mingqing Cheng [MSFT]
Hi James,
I have noticed you make another new thread in
microsoft.public.sqlserver.connect. Community member Sue and I have all add
a reply in that thread. I will also follow up in that thread if you have
any further questions on this issue.
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!
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.].
Hi James,
I have noticed you make another new thread in
microsoft.public.sqlserver.connect. Community member Sue and I have all add
a reply in that thread. I will also follow up in that thread if you have
any further questions on this issue.
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!
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,
I have noticed you make another new thread in
microsoft.public.sqlserver.connect. Community member Sue and I have all add
a reply in that thread. I will also follow up in that thread if you have
any further questions on this issue.
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!
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:
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:
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.].
Hi James,
I have noticed you make another new thread in
microsoft.public.sqlserver.connect. Community member Sue and I have all add
a reply in that thread. I will also follow up in that thread if you have
any further questions on this issue.
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!
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,
I have noticed you make another new thread in
microsoft.public.sqlserver.connect. Community member Sue and I have all add
a reply in that thread. I will also follow up in that thread if you have
any further questions on this issue.
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!
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.].
Hi James,
I have noticed you make another new thread in
microsoft.public.sqlserver.connect. Community member Sue and I have all add
a reply in that thread. I will also follow up in that thread if you have
any further questions on this issue.
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!
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,
I have noticed you make another new thread in
microsoft.public.sqlserver.connect. Community member Sue and I have all add
a reply in that thread. I will also follow up in that thread if you have
any further questions on this issue.
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!
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.].Hi James,
I have noticed you make another new thread in
microsoft.public.sqlserver.connect. Community member Sue and I have all add
a reply in that thread. I will also follow up in that thread if you have
any further questions on this issue.
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!
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,
I have noticed you make another new thread in
microsoft.public.sqlserver.connect. Community member Sue and I have all add
a reply in that thread. I will also follow up in that thread if you have
any further questions on this issue.
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!
server to Oracle
I'm trying to pass a query from sql server 2000 to Oracle using linked
servers. I don't want to use DTS. While it's easy enough to use OPENQUERY
to pass thru a query that returns a dataset, I can't seem to pass thru a
query that doesn't return a dataset eg a create table query or a drop table
query. If I try, I get an error saying that the query returns no columns.
Is it possible to pass thru a query that returns no columns using a linked
server?
For example, the following query:
select *
from OPENQUERY(ORA8I,'CREATE TABLE MYTABLE AS SELECT * FROM EMP')
returns the error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'CREATE TABLE MYTABLE AS SELECT * FROM EMP'. The
OLE DB provider 'MSDAORA' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to proc
ess
object, since the object has no columnsProviderName='MSDAORA', Query=CREATE
TABLE MYTABLE AS SELECT * FROM EMP'].arch (Sorry , cannot test it right now)
SELECT *
FROM
OPENQUERY(ORA8I,'SELECT * INTO MYTABLE FROM EMP')
If it does not work you may want to try
CREATE FUNCTION dbo.fn_getdata()
AS
RETURNS TABLE
AS
BEGIN
RETURN(
SELECT *
FROM OPENQUERY(
[server_name],
'SET NOCOUNT ON;
SELECT * INTO database.MyTable FROM DataBase.EMP;') AS O)
END
"arch" <archangel@.arach.net.au> wrote in message
news:newscache$rzf9ui$m9c$1@.phantom.amnet.net.au...
> I'm trying to pass a query from sql server 2000 to Oracle using linked
> servers. I don't want to use DTS. While it's easy enough to use
> OPENQUERY
> to pass thru a query that returns a dataset, I can't seem to pass thru a
> query that doesn't return a dataset eg a create table query or a drop
> table
> query. If I try, I get an error saying that the query returns no columns.
> Is it possible to pass thru a query that returns no columns using a linked
> server?
> For example, the following query:
> select *
> from OPENQUERY(ORA8I,'CREATE TABLE MYTABLE AS SELECT * FROM EMP')
> returns the error:
> Server: Msg 7357, Level 16, State 2, Line 1
> Could not process object 'CREATE TABLE MYTABLE AS SELECT * FROM EMP'. The
> OLE DB provider 'MSDAORA' 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='MSDAORA',
> Query=CREATE
> TABLE MYTABLE AS SELECT * FROM EMP'].
>
>|||No luck with any of that.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e%23A$6awKGHA.2628@.TK2MSFTNGP15.phx.gbl...
> arch (Sorry , cannot test it right now)
> SELECT *
> FROM
> OPENQUERY(ORA8I,'SELECT * INTO MYTABLE FROM EMP')
> If it does not work you may want to try
> CREATE FUNCTION dbo.fn_getdata()
> AS
> RETURNS TABLE
> AS
> BEGIN
> RETURN(
> SELECT *
> FROM OPENQUERY(
> [server_name],
> 'SET NOCOUNT ON;
> SELECT * INTO database.MyTable FROM DataBase.EMP;') AS O)
> END
>
>
> "arch" <archangel@.arach.net.au> wrote in message
> news:newscache$rzf9ui$m9c$1@.phantom.amnet.net.au...
>
servers. I don't want to use DTS. While it's easy enough to use OPENQUERY
to pass thru a query that returns a dataset, I can't seem to pass thru a
query that doesn't return a dataset eg a create table query or a drop table
query. If I try, I get an error saying that the query returns no columns.
Is it possible to pass thru a query that returns no columns using a linked
server?
For example, the following query:
select *
from OPENQUERY(ORA8I,'CREATE TABLE MYTABLE AS SELECT * FROM EMP')
returns the error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'CREATE TABLE MYTABLE AS SELECT * FROM EMP'. The
OLE DB provider 'MSDAORA' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to proc
ess
object, since the object has no columnsProviderName='MSDAORA', Query=CREATE
TABLE MYTABLE AS SELECT * FROM EMP'].arch (Sorry , cannot test it right now)
SELECT *
FROM
OPENQUERY(ORA8I,'SELECT * INTO MYTABLE FROM EMP')
If it does not work you may want to try
CREATE FUNCTION dbo.fn_getdata()
AS
RETURNS TABLE
AS
BEGIN
RETURN(
SELECT *
FROM OPENQUERY(
[server_name],
'SET NOCOUNT ON;
SELECT * INTO database.MyTable FROM DataBase.EMP;') AS O)
END
"arch" <archangel@.arach.net.au> wrote in message
news:newscache$rzf9ui$m9c$1@.phantom.amnet.net.au...
> I'm trying to pass a query from sql server 2000 to Oracle using linked
> servers. I don't want to use DTS. While it's easy enough to use
> OPENQUERY
> to pass thru a query that returns a dataset, I can't seem to pass thru a
> query that doesn't return a dataset eg a create table query or a drop
> table
> query. If I try, I get an error saying that the query returns no columns.
> Is it possible to pass thru a query that returns no columns using a linked
> server?
> For example, the following query:
> select *
> from OPENQUERY(ORA8I,'CREATE TABLE MYTABLE AS SELECT * FROM EMP')
> returns the error:
> Server: Msg 7357, Level 16, State 2, Line 1
> Could not process object 'CREATE TABLE MYTABLE AS SELECT * FROM EMP'. The
> OLE DB provider 'MSDAORA' 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='MSDAORA',
> Query=CREATE
> TABLE MYTABLE AS SELECT * FROM EMP'].
>
>|||No luck with any of that.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e%23A$6awKGHA.2628@.TK2MSFTNGP15.phx.gbl...
> arch (Sorry , cannot test it right now)
> SELECT *
> FROM
> OPENQUERY(ORA8I,'SELECT * INTO MYTABLE FROM EMP')
> If it does not work you may want to try
> CREATE FUNCTION dbo.fn_getdata()
> AS
> RETURNS TABLE
> AS
> BEGIN
> RETURN(
> SELECT *
> FROM OPENQUERY(
> [server_name],
> 'SET NOCOUNT ON;
> SELECT * INTO database.MyTable FROM DataBase.EMP;') AS O)
> END
>
>
> "arch" <archangel@.arach.net.au> wrote in message
> news:newscache$rzf9ui$m9c$1@.phantom.amnet.net.au...
>
Subscribe to:
Posts (Atom)