Migrating Oracle to SQL Server using SSMA – Error: Cannot find either column "ssma_oracle" or the user-defined function or aggregate "ssma_oracle.rpad_varchar"

  • Article

Issue:

You are trying to migrate server objects and data from Oracle to SQL Server using SSMA for Oracle and for a particular view which has the RPAD function and getting the following error while performing database synchronization.

Errors: Cannot find either column "ssma_oracle" or the user-defined function or aggregate "ssma_oracle.rpad_varchar", or the name is ambiguous.
Synchronization error: Cannot find either column "ssma_oracle" or the user-defined function or aggregate "ssma_oracle.rpad_varchar", or the name is ambiguous. On: <source_object>

 

Cause:

When you are trying to synchronize a database object on the target SQL Server console, either the ssma_oracle schema has not been synchronized initially or it is unchecked during the process of database synchronization.

1

 

Solution:

While server object synchronization, we need to ensure that the ssma_oracle schema is selected and synchronized first before synchronizing and migrating any other database objects.

2

 

A bit more:

For any database migration in SSMA, the schema mapping has to be done at the initial phase. The tool takes care of this with the ‘Convert Schema’ option. When we initiate the ‘Convert Schema’ command, we see a new schema being generated called ‘ssma_oracle’ on the target SQL Server database. This new schema contains oracle specific database objects namely the built-in functions, stored procedures and views(find more on ssma_oracle objects here).  This has to be synchronized first before synchronizing the user defined schema objects which we need to migrate else this will error out with the unidentified schema objects. On choosing the target SQL Server, the schema ‘ssma_oracle’ will be selected by default. If not, we need to explicitly enable and synchronize it before synchronizing other objects.

 

Following is the flow on how we arrive onto this issue and drive for resolution.

  1. After selecting the Oracle source server, selected the targeted SQL server.

3

As we see here, the ssma_oracle has not been created initially

2. You initiate the convert_schema on the Oracle source server objects:

4

This results in the creation of ssma_oracle schema which contains the oracle specific database objects.

3. Now when you try to synchronize with the database, without selecting the ssma_oracle, you hit the following error:

5 6

 

When you try to synchronize all objects by selecting the root schema node, SSMA will consider the synchronization of the ssma_oracle schema at the first place and then proceeds with the rest of the user defined schemas.

7

 

Reference:

 

Author:         Chetan KT   – Support Engineer, SQL Server BI Developer team, Microsoft

Reviewer:    Krishnakumar Rukmangathan – Support Escalation Engineer, SQL Server BI Developer team, Microsoft