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