I work majorly on SQL Server but rarely do I get a chance to work on Oracle in general. When some of the error messages reach my inbox, I get intrigued as to why some occur. This error message was an outcome of that exploration. When I got this error message, I said I have no clue as to why this is happening and I asked if it was in any way related to SQL Server? Let us fix SSMA Error.
The gentleman wrote back stating this was coming from a SQL Server tool and since they were doing a migration using SQL Server Migration Assistant Tool (SSMA). Now that statement got me interested because it was coming from a SQL Server migration.
I went ahead to asked about the environment to get a better hang of things. I asked the Oracle client version they were running. They responded by saying, though we are running Oracle Client 9.2.0.8 we are getting an Oracle 8.1.7 or higher error strangely.
The following message is being displayed on the console:
Migrating data…
Analyzing metadata…
Preparing table SYSTBL.CMN_TBL_CUST_…
Preparing data migration package…
Starting data migration agent…
Starting data migration…
Exception during processing: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.
Data migration complete.
0 table(s) successfully migrated.
0 table(s) partially migrated.
1 table(s) failed to migrate.
After going through the complete problem statement. I saw that it was working on a local server, but was failing when it was called from remote server.
Reason After Investigation: SSMA Error
The Oracle client provider is not there on the remote SQL Server. If working with remote machines, the Oracle Client provider should be present on the remote SQL Server.
I sent a mail to – Install the Oracle Client provider on the remote SQL Server and verify the data migration. As SSMA doesn’t have any limitation to work with remote SQL Server. Also as part of the installation, asked to check if the extension packs were installed on the remote servers.
After this exercise, I felt there was some good learning for me too and worth a share here. I am sure you are much more experienced in working with SSMA but feel free to let me know if I missed anything. Talk to me on Twitter.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
