|
Home > Archive > MS SQL Server > February 2006 > linked server to Oracle
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
linked 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,'CRE
ATE 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'].
| |
| Uri Dimant 2006-02-06, 7:23 am |
| arch (Sorry , cannot test it right now)
SELECT *
FROM
OPENQUERY(ORA8I,'SEL
ECT * 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$rzf9u
i$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,'CRE
ATE 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,'SEL
ECT * 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$rzf9u
i$m9c$1@phantom.amnet.net.au...
>
>
|
|
|
|
|