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'].
>>
>