SSMA for Oracle requires installation of SSMA Extension Pack on the target SQL Server. This blog post describe errors that you may receive during the installation and how to resolve the issue.
Must declare variable error messages
During the installation of SSMA Extension Pack, you receive a message that you complete the first step of installation:
However, when you click next, you may receive an error which says that you do not have permission to install. Or, you may be able to continue but receive Invalid connection (Must declare the scalar variable … ) error similar to below:
SSMA install extension pack to SQL Server Binn folder. You will receive the above message if you do not have write permission to the folder.
If you receive this message, you can cancel and exit the installation. You can proceed with the installation with the following steps:
- Locate Microsoft SQL Server Migration Assistant for …. Extension Pack
- Right click and select “Run as Administrator”
A wizard window will appear and you can follow the steps to complete the installation.
Missing .NET assembly error
During the installation of SSMA Extension Pack, you may encounter the following error:
Errors: SSMA4OracleSQLServerCollections.NET assembly not found SSMA4OracleSQLServerCollections.NET.XmlSerializers assembly not found
Synchronization error: SSMA4OracleSQLServerCollections.NET assembly not found
SSMA4OracleSQLServerCollections.NET.XmlSerializers assembly not found On: OTS_COLLECTIONS3
The issue may occur when the user and the machine belong to different network domain. The installation file execute sql statements which requires it to query Active Directoory for user’s permission. When the machine and the user belowng to different domains which do not have trust relationship, the above error occurs.
The workaround for this issue is to install the Extension Pack and connect to SQL Server using SQL Authentication login. This is only needed during installation, you can change to windows authentication mode after the installation completes.