locked
Migrating from oracle database to MSSQL using SSMA RRS feed

  • Question

  • Hi,

    I am migrating schema tables from Oracle DB to MSSQL using SSMA.I have installed oracle client and ODAC.

    But while connecting oracle DB using SSMA,i receiving the error "unable to find specfied provider. COMPATIBLE ORACLE DATA ACCESS CONNECTIVITY LIBRARIES WERE NOT FOUND ON THE MACHINE.".Can anyone please help to resolve.

    Wednesday, February 28, 2018 2:42 AM

All replies

  • Please refer below

    https://stackoverflow.com/questions/5809195/installing-oracle-instant-client/26439785#26439785

    https://social.msdn.microsoft.com/Forums/en-US/e2490b75-d2ff-400f-b2ff-5d900c4d778e/unable-to-find-specified-provider-compatible-oracle-data-access-connectivity-libraries-were-not?forum=sqlservermigration

    -------------------------------------------------------------------
    Please Mark This As Answer if it solved your issue 
    Please Vote This As Helpful if it helps to solve your issue so that other forum members can benefit from it

    Yuvraj Singh Bais
    -------------------------------------------------------------------


    Wednesday, February 28, 2018 6:42 AM
  • Many thanks Yuvraj Singh.I updated PATH(variable) to that installed folder and it works.

    Can you help me resolve another error " Connection to Oracle established successfully.Error occurred while collecting data."

    Hope you help soon.

    Monday, March 5, 2018 6:40 AM
  • Giving some more detail of the error..

    while i try to connect oracle server using SSMA,a dialog box appears and shows Operation Prerequisites not met:

    common requirement: user doesnot have required permission

    select any table

    select any dictionary

    select any procedure

    select any sequence....then clicked OKin dialogue box

    this error appeared "Connection to Oracle established successfully.Error occurred while collecting data."

    Thanks,

    Lakshee

    Monday, March 5, 2018 7:50 AM
  • Hi,

    Did you give your user all required permissions before clicking OK in the dialog?

    Regards,
    Alex.

    Monday, March 5, 2018 3:29 PM
  • Thanks for your reply Alex.It seems like the user trying to connect dont have CONNECT permission.

    In SSMA ,connecting to oracle database Prerequisites mentioned that should have atleast CONNECT permission.But when i checked table DBA_TAB_PRIVS for the user dont have CONNECT privilege.

    Regards,

    Lakshee

    Tuesday, March 6, 2018 6:33 AM
  • Hi,

    It seems like the user test have CONNECT permission.I used the query to check SELECT * FROM dba_role_privs WHERE grantee='TEST';

    Whether above SSMA error (Connection to Oracle established successfully.Error occurred while collecting data.")  is due to  ADMIN_OPTION = 'NO' in dba_role_privs  table.

    And i have also SELECT,INSERT,UPDATE,DELETE permission for all tables inside.

    GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE
    TEST CONNECT NO YES
    TEST SCHEMA_OWNER NO YES
    TEST SELECT_CATALOG_ROLE NO YES
    TEST SS_STD YES YES

    Regards,

    Lakshee

    Tuesday, March 6, 2018 8:06 AM
  • Hi,

    All necessary permissions for SSMA to work are documented here and also mentioned in the dialog when you connect to Oracle. Please make sure the user you are using to connect has all required permissions.

    Regards,
    Alex.

    Monday, March 12, 2018 4:04 PM