create table via Linked server

  • 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

  • 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!

  • 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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply