create table via Linked server

• newbieuser

  SSCrazy Eights

  Points: 9423

  More actions

  May 16, 2016 at 6:58 am

  #316552

  Hi friends,

  We would need to create tables in Oracle via Linked Server in SQL Server.. I tried select * into from table, I get the below error..

  select * into LINK1..USER1.ORATABLE1 from sqltable;

  Msg 117, Level 15, State 1, Line 2

  The object name 'LINK1..USER1.ORATABLE1' contains more than the maximum number of prefixes. The maximum is 2.

  Is this possible to create table in Oracle using linked server? Or do we need to create tables manually in Oracle and push the data via linked Server?

  Thanks a lot

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 16, 2016 at 9:04 am

  #1879286

  newbieuser (5/16/2016)

  ---

  Hi friends,

  We would need to create tables in Oracle via Linked Server in SQL Server.. I tried select * into from table, I get the below error..

  select * into LINK1..USER1.ORATABLE1 from sqltable;

  Msg 117, Level 15, State 1, Line 2

  The object name 'LINK1..USER1.ORATABLE1' contains more than the maximum number of prefixes. The maximum is 2.

  Is this possible to create table in Oracle using linked server? Or do we need to create tables manually in Oracle and push the data via linked Server?

  Thanks a lot

  you have to use EXECUTE AT, you might certainly hit permissions problems too.

  the static string you pass has to be able to be processed by the target server, which in your case seems to be oracle.

  if you need a permanent table, i would just go and create it, and then reference it in the linked server, it like doing an end run around the DBA otherwise.

  anyway here's just decent example; since Oracle doesn't have an Identity() column function, you have to simulate one with a sequence and a trigger, so here's an example of that; CHANGE_ME would be the user/schema Oracle you want to insert into.

  --SQL version of the table

  EXECUTE ( '

  CREATE TABLE [dbo].[AllObjects] (

  [name] SYSNAME NOT NULL,

  [object_id] INT NOT NULL,

  [principal_id] INT NULL,

  [schema_id] INT NOT NULL,

  [parent_object_id] INT NOT NULL,

  [type] CHAR(2) NOT NULL,

  [type_desc] NVARCHAR(60) NULL,

  [create_date] DATETIME NOT NULL,

  [modify_date] DATETIME NOT NULL,

  [is_ms_shipped] BIT NOT NULL,

  [is_published] BIT NOT NULL,

  [is_schema_published] BIT NOT NULL)

  ' ) AT SeattleSales;

  an oracle version:

  EXECUTE ( 'CREATE TABLE CHANGE_ME.ALLOBJECTS (

  NAME SYSNAME NOT NULL,

  OBJECT_ID NUMBER(10) NOT NULL,

  PRINCIPAL_ID NUMBER(10) NULL,

  SCHEMA_ID NUMBER(10) NOT NULL,

  PARENT_OBJECT_ID NUMBER(10) NOT NULL,

  TYPE CHAR (2) NOT NULL,

  TYPE_DESC NVARCHAR2(120) NULL,

  CREATE_DATE DATE NOT NULL,

  MODIFY_DATE DATE NOT NULL,

  IS_MS_SHIPPED NUMBER(10) NOT NULL,

  IS_PUBLISHED NUMBER(10) NOT NULL,

  IS_SCHEMA_PUBLISHED NUMBER(10) NOT NULL);

  --CHANGE_ME.ALLOBJECTS__SEQUENCE

  CREATE SEQUENCE CHANGE_ME.ALLOBJECTS__SEQUENCE

  START WITH 1

  NOMAXVALUE

  MINVALUE 1

  NOCYCLE

  CACHE 20

  NOORDER;

  ' ) AT SeattleSales;

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• teenujohn555

  SSC Journeyman

  Points: 95

  More actions

  July 4, 2016 at 11:54 pm

  #1888374

  Nice information. You know i am not familiar with SQL Server. It is a new information for me. But the description is very clear and understanding one. I can understand the basic and helped me know more.Thanks for sharing. For more details visit best dissertation writing service

  best dissertation writing service