Post reply

Problem with Schema name

  • November 18, 2011 at 4:56 am

    #263152

    Migration from Oracle to Sql server

    Oralce:9i

    Sql server :2005

    I have finished the migrating the Oracle table data to Sql Server using 2 methods..

    1.import/export

    2.using SSIS packages.

    Both were successful but in case of SSIS i have received the oracle table name in sql server database with DBO schema but not actual schema belongs to the table in oracle ..I have taken oledb source ,data conversion and oledb destination in package..

    But in import and export method i have received the table name in my sql server database destination with same schema name that originally comes from oracle database..

    Thanks

    Geetha

  • November 18, 2011 at 5:03 am

    #1409807

    In the absence of a question in your post, it seems that the import/export (or should that be export/import) method is the one to use.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • November 18, 2011 at 7:01 am

    #1409893

    geetha@in (11/18/2011)

    Both were successful but in case of SSIS i have received the oracle table name in sql server database with DBO schema but not actual schema belongs to the table in oracle

    My guess - I have to guess as there is no question - is that you created the destination table in SQL Server using the "wizard" in the OLE DB Destination. That one takes dbo as the default schema.

    If you want another schema, you'll have to script a CREATE TABLE statement yourself.

    Or, just move the table from the dbo schema to the schema you want with the ALTER SCHEMA ... TRANSFER statement.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • November 21, 2011 at 2:51 am

    #1410563

    Thanks Koen ...I have changed schema name with new one using below one

    ALTER SCHEMA <NEW SCHEMA NAME> TRANSFER <OLD SCHEMANAME.OBJECTNAME>

  • November 21, 2011 at 3:43 am

    #1410575

    geetha@in (11/21/2011)

    Thanks Koen ...I have changed schema name with new one using below one

    ALTER SCHEMA <NEW SCHEMA NAME> TRANSFER <OLD SCHEMANAME.OBJECTNAME>

    Great, glad your problem got fixed.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 5 posts - 1 through 4 (of 4 total)

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