Post reply

how to send data from sqlserver to oracle

  • January 29, 2009 at 10:26 am

    #129847

    hi

    i had migrated the structure(database) from sql server2005 to oracle10g. now i want to send that existing data of the database (sql server2005) to oracle database.

    thanks in advance

    🙂

  • January 29, 2009 at 10:52 am

    #934892

    Should be fairly easy using Integration Services.

    The simplest way to use this ETL tool would be:-

    1) In Management Studio click on the database from which you wish to export data.

    2) Right click, TASKS -> Export Data.

    3) The default Data Source details should be correct for the database you originally selected.

    4) Click Next.

    5) In the destination drop down, select the Oracle provider for OLEDB.

    6) Click the Properties button.

    7) Give the data source name (same name as you would find in the TNSNAMES.ORA file) and login details for the server.

    8) Test the connection by clicking the "Test Connection" button.

    9) Click Next and continue by selecting the tables \ data you wish to export, either explicitly by name or by a SQL statement.

    Carry on through the wizard and when reach the end and you are happy with your selections click the box for "Execute Immediately" and click Finish.

    You should see the progress indicator as your data is exported.

  • January 30, 2009 at 5:30 am

    #935427

    hi

    it is creating the tables with data. i dont need to create the tables.

    i want just to migrate the data only. please help as soon as possible

  • February 10, 2009 at 4:54 am

    #941090

    Hi Everyone,

    I had sucessfully migrated(tables with data) from sqlserver2005 to oralce10g. now i am getting the error.

    in oracle10g,after executing the below query::

    >>select count(*) from policy;

    ERROR at line 1:

    ORA-00942: table or view does not exist

    can any one help me on this, very urgent

    thanks in advance

    rock

  • February 10, 2009 at 5:01 am

    #941092

    Dear

    PL check the correct schema and user,because u have just insert the data,please enter into oracle client with same user as data transferred from the sql server.

    Thanks

  • February 10, 2009 at 5:06 am

    #941095

    Dear

    PL check the correct schema and user,because u have just insert the data,please enter into oracle client with same user as data transferred from the sql server.

    Thanks

  • February 10, 2009 at 5:12 am

    #941098

    rockingadmin (2/10/2009)

    Hi Everyone,

    I had sucessfully migrated(tables with data) from sqlserver2005 to oralce10g. now i am getting the error.

    in oracle10g,after executing the below query::

    >>select count(*) from policy;

    ERROR at line 1:

    ORA-00942: table or view does not exist

    can any one help me on this, very urgent

    thanks in advance

    rock

    Either you do not have privileges on the target table or the target table is sitting in a schema other than the one owned by the account you are using.

    Try by fully qualifying the table, like "select count(*) from schema_name.policy;"

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • February 10, 2009 at 11:04 am

    #941476

    hi

    i have checked the user. i can see the data in that user. but the problem is i cant able to use a query

    how can i see that table is on which schema>

    help me to sort out my problem

    thanks

    rock

  • February 10, 2009 at 11:29 am

    #941498

    rockingadmin (2/10/2009)

    hi

    i have checked the user. i can see the data in that user. but the problem is i cant able to use a query

    how can i see that table is on which schema>

    help me to sort out my problem

    thanks

    rock

    Let me quote myself... Either you do not have privileges on the target table or the target table is sitting in a schema other than the one owned by the account you are using.

    Try by fully qualifying the table, like "select count(*) from schema_name.policy;

    Either way, to check tables owned by a specific Oracle ACCOUNT -a.k.a. schema - do this...

    select *

    from dba_tables

    where owner = 'ACCOUNT"

    ;

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • February 11, 2009 at 3:40 am

    #941965

    hi

    using this query i can able to see the tables of that user.

    select *

    from dba_tables

    where owner = 'ACCOUNT"

    ;

    but i try to get the data using a query from that table i am getting the error.

    select * from user_Tables;

    it shows 90 tables.

    when i use the below query

    select * from policy;

    select * from dbo.policy;

    i am getting the error...

    any idea

    thanks in advance

    rock..

  • February 11, 2009 at 3:52 am

    #941969

    rockingadmin (2/11/2009)

    hi

    using this query i can able to see the tables of that user.

    select *

    from dba_tables

    where owner = 'ACCOUNT"

    ;

    but i try to get the data using a query from that table i am getting the error.

    select * from user_Tables;

    it shows 90 tables.

    when i use the below query

    select * from policy;

    select * from dbo.policy;

    i am getting the error...

    any idea

    thanks in advance

    rock..

    Either you are not reading what other people including myself are posting, you are doing something different of what we suggest or you are posting your personal interpretation of what it is actually happening.

    Please copy/paste your entire session as it is so we can actually see what is going on.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Viewing 11 posts - 1 through 10 (of 10 total)

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