pass thru queries 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'].....OR ....
use::
SELECT * INTO #EMP from ORA1..CAT1.EMP
to get the same effect!!
"arch" wrote:

> 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 OPENQUER
Y
> 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 tabl
e
> 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 proces
s
> object, since the object has no columnsProviderName='MSDAORA', Query=CREAT
E
> TABLE MYTABLE AS SELECT * FROM EMP'].
>
>|||arch,
Are you simply attempting to create a temp table on the SQL side dependant
on data from the Oracle side?
If so, then the reference to Oracle needs to be on the SELECT object.
We use linked servers from SQL to Oracle as a matter of day-to-day business
and I use something along the following lines (where ORA1 is the linked
server name as listed in the Security tabl of EntMgr):::
CREATE TABLE #EMP (x,y,z)
INSERT INTO #EMP
SELECT X,Y,Z
FROM ORA1..CAT1.EMP a
This, of course, indicates you know the schema or at least the columns you
want (remember you should reduce loads by not selecting redundant data), and
their respective datatypes.
Hope this assists,
Tony
"arch" wrote:

> 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 OPENQUER
Y
> 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 tabl
e
> 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 proces
s
> object, since the object has no columnsProviderName='MSDAORA', Query=CREAT
E
> TABLE MYTABLE AS SELECT * FROM EMP'].
>
>|||That's very useful stuff. But what I wanted to do was to create a table in
the oracle database. In microsoft access, it is very easy to execute a pass
thru query to Oracle, including action queries such as creating and dropping
objects. I can't seem to do that using SQL Server unless I use DTS. Is it
possible to do?
"Tony Scott" <TonyScott@.discussions.microsoft.com> wrote in message
news:90D78160-8F51-4BC2-8F06-D2226A10F95A@.microsoft.com...
> arch,
> Are you simply attempting to create a temp table on the SQL side dependant
> on data from the Oracle side?
> If so, then the reference to Oracle needs to be on the SELECT object.
> We use linked servers from SQL to Oracle as a matter of day-to-day
> business
> and I use something along the following lines (where ORA1 is the linked
> server name as listed in the Security tabl of EntMgr):::
> CREATE TABLE #EMP (x,y,z)
> INSERT INTO #EMP
> SELECT X,Y,Z
> FROM ORA1..CAT1.EMP a
> This, of course, indicates you know the schema or at least the columns you
> want (remember you should reduce loads by not selecting redundant data),
> and
> their respective datatypes.
> Hope this assists,
> Tony
> "arch" wrote:
>