October 24, 2016 at 10:20 am
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
Anyone got anything thoughts other than seeing I can do without the call to dbo.xp_ora2ms_exec2_ex ?
October 26, 2016 at 3:49 am
FYI its a bug with SSMA
I apologize for any inconvenience 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’.
October 26, 2016 at 3:50 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply