A Digital Age Deserves A Digital Leader

DTS Export of Database from SQL Server to ORacle 9i

DTS Export of Database from SQL Server to ORacle 9i

Postby hellraiser_2030 » Tue Aug 03, 2004 6:45 am

Hi,

I am trying to export Data from SQL Server 2000 to Oracle 9i using DTS this works fine. The actual problem comes when i am trying to access the Table from SQL plus it complaints that the Table doesnt exist even though i can see the Table under the Schema in the Enterprise Manager of the Oracle.

After investigatin i found that the DTS creates the tabel in oracle as "SCOTT"."Class" instead of "SCOTT".Class.

when the table is created as "Class" then i need to specify a Synonym to the Table as Class and then i can retrieve the Data in the Table which failed when i wrote

Select * from Class; - > when the Synonym is not defined

to make it work i need to specify as Select * from "SCOTT"."Class" but this becomes impractical when there's a lot of columns.

but Select * from Class; works fine when a Synonym is defined.

One more issue when i edit the SQL script generated by DTS to "SCOTT".Class the Table is created and the Select query works as it should..but the Data fails to be transfered by the DTS to the Table as it fails to recognize "SCOTT".Class.

DO u know any way to over come this?
HellRaiser_2030
PRO Level 3
User avatar
Posts: 71
Joined: Wed Apr 07, 2004 11:50 am
Location: Boston

Return to HTML, CSS, and Scripts

Who is online

Users browsing this forum: No registered users and 6 guests