locked
SSMA 5.3 Could not load the DLL %SSMA_O2SS_EP_BIN_PATH%\bin\ext\x64\SSMA4OracleSQLServerExtensions.dll RRS feed

  • Question

  •    


    I'm getting a failure with the use of dbo.xp_ora2ms_exec2_ex in SSMA 5.3 on a SQL 2014 install

    Msg 17750, Level 16, State 0, Procedure xp_ora2ms_exec2_ex, Line 1
    Could not load the DLL %SSMA_O2SS_EP_BIN_PATH%\bin\ext\x64\SSMA4OracleSQLServerExtensions.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).

    This does not happen my SSMA 5.2 SQL 2012 install.

    Both machine have the dll installed on the same path. Both had their SSMA for Oracle ExtPacts run as administrator.

    Directory of C:\Microsoft SQL Server Migration Assistant for Oracle Extension Pack\bin\ext\x64

    SSMA 5.3
    C:\Microsoft SQL Server Migration Assistant for Oracle Extension Pack\bin\ext\x64>
    01/05/2014  22:39           352,016 SSMA4OracleSQLServerExtensions.dll

    SSMA 5.2
    C:\Microsoft SQL Server Migration Assistant for Oracle Extension Pack\bin\ext\x64>
    12/01/2012  20:30           284,040 SSMA4OracleSQLServerExtensions.dll


    Interestingly the dll has been updated in SSMA5.3

    Its a bit of a noddy test but the script below will give a Msg 17750 Could not load the DLL on SSMA 5.3 but will correctly return a Msg 20001 for SSMA 5.2

    DECLARE
    @active_spid INT,
    @login_time DATETIME
    SET @active_spid = sysdb.ssma_oracle.GET_ACTIVE_SPID()
    SET @login_time = sysdb.ssma_oracle.GET_ACTIVE_LOGIN_TIME()
    DECLARE
    @return_value_argument varchar(8000)
    EXECUTE master.dbo.xp_ora2ms_exec2_ex
    @active_spid,
    @login_time

    Has anyone got any thoughts, can someone just test this on their SSMA 5.3 ?

    Thanks,

    Benedict

    Monday, October 24, 2016 4:37 PM

Answers

  • Hi Benedict Leahy,

    I apologize for any inconvience and it seems like either the environment variable of SSMA path is incorrect, or the installation of extension pack writes incorrect value to master database. Here are the workarounds:

    Workaround 1. You could follow the instructions in this KB article to change the name of a system variable from ‘SSMA_O2SS_EP_HOME’ to ‘SSMA_O2SS_EP_BIN_PATH’ then restart your computer.

    Workaround 2. You could change the properties of these extend stored procedures in SSMS. Open SSMS and connect to the instance that you have SSMA extended pack installed, locate all three stored procedures under Object Explorer\Instance Name\Databases\System Databases\Programmability\Extended Stored Procedures\System Extended Stored Procedures\ then right clicking on them and clicking on ‘Properties’. Then change the DDL path for them to either ‘%SSMA_O2SS_EP_HOME%\bin\ext\x64\SSMA4OracleSQLServerExtensions.dll’ or ‘C:\Microsoft SQL Server Migration Assistant for Oracle Extension Pack\bin\ext\x64\SSMA4OracleSQLServerExtensions.dll’.

    This should fix the problem. I’ve also attached a screenshot to help you locate these stored procedures.


    If you have any other questions, please let me know.

    Regards,
    Lin


    MSDN Community Support<br/>
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.
    If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.




    Tuesday, October 25, 2016 7:40 AM

All replies

  • Hi Benedict Leahy,

    I apologize for any inconvience and it seems like either the environment variable of SSMA path is incorrect, or the installation of extension pack writes incorrect value to master database. Here are the workarounds:

    Workaround 1. You could follow the instructions in this KB article to change the name of a system variable from ‘SSMA_O2SS_EP_HOME’ to ‘SSMA_O2SS_EP_BIN_PATH’ then restart your computer.

    Workaround 2. You could change the properties of these extend stored procedures in SSMS. Open SSMS and connect to the instance that you have SSMA extended pack installed, locate all three stored procedures under Object Explorer\Instance Name\Databases\System Databases\Programmability\Extended Stored Procedures\System Extended Stored Procedures\ then right clicking on them and clicking on ‘Properties’. Then change the DDL path for them to either ‘%SSMA_O2SS_EP_HOME%\bin\ext\x64\SSMA4OracleSQLServerExtensions.dll’ or ‘C:\Microsoft SQL Server Migration Assistant for Oracle Extension Pack\bin\ext\x64\SSMA4OracleSQLServerExtensions.dll’.

    This should fix the problem. I’ve also attached a screenshot to help you locate these stored procedures.


    If you have any other questions, please let me know.

    Regards,
    Lin


    MSDN Community Support<br/>
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.
    If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.




    Tuesday, October 25, 2016 7:40 AM
  • I tried workaround 1 but it didn't work for me, but workaround 2 did. I set the path to DLL in SSMS

    To

    C:\Microsoft SQL Server Migration Assistant for Oracle Extension Pack\bin\ext\x64\SSMA4OracleSQLServerExtensions.dll’.

    I then had to grant execute permission to the stored procs.

    xp_ora2ms_exec2

    xp_ora2ms_exec2_ex

    xp_ora2ms_versioninfo2

    Thanks for your help.

    • Marked as answer by Benedict Leahy Tuesday, October 25, 2016 3:37 PM
    • Unmarked as answer by Benedict Leahy Tuesday, October 25, 2016 3:37 PM
    Tuesday, October 25, 2016 3:37 PM