Create table syntax through linked server.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Create table syntax through linked server.

Author

Topic

sowmyav
Starting Member

34 Posts

Posted - 2009-03-04 : 02:19:20

Hi,
I created a linked server to conect the oracle through sqls erver2005.
And it's working fine.I am able to fetch the rows, count, and select the data.
And I want use the delete from table, create table, and also insert and merger table cmds through the linked server.
The Delete syntax,
I gave it as
delete from linkser..oracleDB.Tablename-it's deleting the rows.similarly I want to create table thorugh linked server using some of the existing tables in oracle like

create table Tablename as
SELECT /*+ leading (a) use_nl(b)*/
b.ANum_ID,
a.EVTSEC,
From
tablename1 a,
tablename2 b
Where a.sys_id = b.sys_id
AND a.log_file_id = b.log_file_id
AND b.ANum_ID >= '20070101%' ;

But I am not able to get the syntax for the same.
Pleae let me know where should I add the linked server(linkser..oracleDB.) which makes to create the table after the deletion using the above delete cmd.

Thanks
Sowmya

sowmyav
Starting Member

34 Posts

Posted - 2009-03-04 : 02:51:01

Even if I try the below simple create table syntax as
create table tablename as
SELECT * From linkser..oracleDB.Tablename;

I am getting the err msg as
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'as'.

But If I give the
SELECT * From linkser..oracleDB.Tablename alone, I am able to select the data.

Could any one please help me on the same.,

Thanks
Sowmya

matty
Posting Yak Master

161 Posts

Posted - 2009-03-04 : 03:12:02

SELECT * INTO tablename From linkser..oracleDB.Tablename

quote:
Originally posted by sowmyav

Even if I try the below simple create table syntax as
create table tablename as
SELECT * From linkser..oracleDB.Tablename;

I am getting the err msg as
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'as'.

But If I give the
SELECT * From linkser..oracleDB.Tablename alone, I am able to select the data.

Could any one please help me on the same.,

Thanks
Sowmya

sowmyav
Starting Member

34 Posts

Posted - 2009-03-04 : 03:27:33

Thanks a lot!.
And To create in sql server I can use the SELECT * into
and also insert into the needed db as Inter into linkser..oracleDB.Tablename .
It was very help ful to me.
And Could please clear my doubt on the ERR msg which the changed the table name to CAPS letter.
But even though I am getting the ERR mesage for some tables?

Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "MSDAORA" for linked server "PDW" does not contain the table ""ORDB"."t_info"". The table either does not exist or the current user does not have permissions on that table.

But when I desc t_info in oracle dB,
I am able to checkout the table as well as it's count.
But in SQL server thorugh linked server,I am getting only errmsg.
Your suggestions helps me a lot.
Thanks
Sowmyua

Missael
Starting Member

1 Post

Posted - 2009-05-28 : 10:35:52

sowmyav
I'm having the samen problem as you. I use the function OPENQUERY to select, insert, update and delete rows in the linked served. But i cant use the function OpenQuery to execute a Package. I´d like to know if u found a soluction at you problem.

Thanks a lot.

Salud

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources