newbie openquery question

How do I use openquery to create a table in an oracle database. Any sql
string that I pass thru fails as it doesn't return any rows.Please post which query you are using and whats the error message that
comes back.
HTH, Jens Suessmeyer.|||I'm trying to use:
select 1
from openquery(ORACLE8I,'CREATE TABLE TEST01 AS SELECT * FROM EMP')
and getting error message:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'CREATE TABLE TEST01 AS SELECT * FROM SCOTT.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 TEST01 AS SELECT * FROM SCOTT.EMP'].
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1137227213.161298.325390@.f14g2000cwb.googlegroups.com...
> Please post which query you are using and whats the error message that
> comes back.
> HTH, Jens Suessmeyer.
>|||AS OpenQUERY is expecting a resultset being send back, you have to send
back even a dummy Select like "Select 1" (especially in Oracle "Select
1 from Dual"), so that should do the trick. (BTW: Do not use * in
productional systems for selecting, but I assume that you only did this
for testing, right ;-) )
select 1
from openquery(ORACLE8I,'CREATE TABLE TEST01 AS SELECT * FROM
EMP;Select 1 FROM dual;')
HTH, Jens Suessmeyer.|||unfortunately that still didn't work. Getting:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'CREATE TABLE TEST01 AS SELECT * FROM EMP;Select 1
FROM dual;'. 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 TEST01 AS SELECT * FROM EMP;Select 1 FROM dual;'].
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1137229967.162954.49310@.g14g2000cwa.googlegroups.com...
> AS OpenQUERY is expecting a resultset being send back, you have to send
> back even a dummy Select like "Select 1" (especially in Oracle "Select
> 1 from Dual"), so that should do the trick. (BTW: Do not use * in
> productional systems for selecting, but I assume that you only did this
> for testing, right ;-) )
> select 1
> from openquery(ORACLE8I,'CREATE TABLE TEST01 AS SELECT * FROM
> EMP;Select 1 FROM dual;')
>
> HTH, Jens Suessmeyer.
>|||ok, I think as the provider I waiting for a column description, try to
name the column which is coming back. I assume that this is expecting a
column description meta data. Once we had something similar executing a
stored procedure on a informix server. Due to the fact that the
procedure didn=B4t passed back anything, we received a smiliar error.
Adding a Select "'Procedure ready' AS result" at the end of the
procedure did the trick.
So try
select 1
from openquery(ORACLE8I,'CREATE TABLE TEST01 AS SELECT * FROM
EMP;Select 'Table created' AS ResultMessage FROM dual;')
HTH, Jens Suessmeyer.|||I got a syntax error due to the single quotes I think. When I modified to
add extra quotes:
select 1
from openquery(ORACLE8I,'CREATE TABLE TEST01 AS SELECT * FROM EMP;Select
''Table created'' AS ResultMessage FROM dual;')
I still get the error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'CREATE TABLE TEST01 AS SELECT * FROM EMP;Select
'Table created' AS ResultMessage FROM dual;'. 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 TEST01 AS SELECT * FROM EMP;Select 'Table created' AS ResultMessage
FROM dual;'].
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1137233494.070652.28380@.f14g2000cwb.googlegroups.com...
ok, I think as the provider I waiting for a column description, try to
name the column which is coming back. I assume that this is expecting a
column description meta data. Once we had something similar executing a
stored procedure on a informix server. Due to the fact that the
procedure didn´t passed back anything, we received a smiliar error.
Adding a Select "'Procedure ready' AS result" at the end of the
procedure did the trick.
So try
select 1
from openquery(ORACLE8I,'CREATE TABLE TEST01 AS SELECT * FROM
EMP;Select 'Table created' AS ResultMessage FROM dual;')
HTH, Jens Suessmeyer.