Nothing to Process error when creating Assessment report

Symptom

When user tries to create Assessment Report by click “Create Report” they may get error “There is Nothing to process”

Cause

We get this message if there are no objects in the node selected.

Resolution

As there are no objects in any of the schemas you are getting that message ”There is nothing to process”. You can create report only when you have objects(tables,Procedures etc) in the node selected.

For example, lets say the user guest does not have access to any database objects. So there are no objects under guest schema. If the above user is given permission to those objects, then those objects will be visible in Metadeta explorer. Then you won’t get this error message.

More Information

Article Id: KBO2SS1010

Applies to

SSMA 2005 for Oracle

SSMA 2008 for Oracle

Cannot Convert Schema or Migrate data for SYSTEM schema

Symptom

The user created their tables in the SYSTEM schema and when they try to use Convert Schema or Migrate data for the SYSTEM schema the options are greyed out.

Cause

SSMA does not migrate system objects

Resolution

SSMA considers all objects in SYS and SYSTEM schemas as system objects. SSMA does not migrate objects which is considered to be system objects of Oracle in SSMA intentionally. Creating objects in another schema and do the migration.

More Information

Article Id: KBO2SS1010

Applies to

SSMA 2005 for Oracle

SSMA 2008 for Oracle

Cannot create New Test Case

Symptom

When using SSMA tester while creating New Test Case users may see the below error.

Error:

Installed data not found.Error occurred during following command execution:

CREATE USER ssmatester oracle IDENTIFIED BY TESTER

Tester database installation failed

Cause

SSMA Tester requires ssmatester_oracle schema to be installed on the oracle database.

Resolution

SSMA Tester requires ssmatester_oracle schema to be installed on the oracle database. The error message usually comes when the execution of the command ‘CREATE USER ssmatester_oracle IDENTIFIED BY TESTER’ failed because of insufficient privileges of the user. Try to execute this command manually in Oracle database with the same user with which you are connecting to SSMA and grant all the required privileges and check with SSMA again. You must have Oracle privileges to create a new user and create objects in the schema of this user. More information about testing is provided in the ‘Testing Migrated Database Objects’ section in the help menu (F1) of the SSMA tool.

More Information

Article Id: KBO2SS1009

Applies to

SSMA 2005 for Oracle

Given key was not present in the dictionary error

Symptom
During Data Migration using SSMA, tables may get an error “The given key was not present in the dictionary”

Cause
‘The given key was not present in the dictionary’ usually occur when the schema of the table in sql server doesn’t exactly match with that of oracle database.

Resolution
The error message ‘The given key was not present in the dictionary’ usually occur when the schema of the table in sql server doesn’t exactly match with that of oracle database.
There might be lot of reasons related with this:

- Tables have different column names(column names(case sensitive) should match both on oracle and sql server)

- Natural column order- the order of columns as the server(oracle or sql server)

- Data types

More Information
Article Id: KBO2SS1008

Applies to
SSMA 2005 for Oracle
SSMA 2008 for Oracle

SSMA Extension pack not found on database server

Symptom

When doing Data migration using SSMA user may encounter the following pop-up message:

Failed prerequisites list:

Common requirement: SSMA extension pack was not found on the database server. Install the SSMA extension pack to use the data migration.

Cause

This error message usually occur when

a) Extension pack is not installed on SQL Server instance

b) The Sysdb and ssma_oracle databases doesn’t exist on the sql server instance which you are trying to connect or is not accessible because of lack of permissions.

Resolution

a) Install the extension pack on SQL server instance.

b) Ensure that the Sysdb and ssma_oracle databases exists on the sql server machine which you are trying to connect and is accessible.

More Information

Article Id: KBO2SS1007

Applies to

SSMA 2005 for Oracle

SSMA 2008 for Oracle

The table does not exist in SQL Server

Symptom

When trying to migrate data using SSMA user may get the above error. When clicked on "Migrate Data", after entering the user name and password, it pops up "The table 'xxxxxx' does not exist in SQL Server. You must first convert the table and then load it into the database."

Cause

This error occurs when user tries to migrate the data before doing the ‘synchronize with the database’ operation.

Resolution

This error message generally occurs when the target table doesn’t exist on sql server database. After converting schema, you need to synchronize the table with the database before migrating the data. To do this you right click on the SQL Server database in Metadata explorer and click “Synchronize with database” menu. The table structure will not be created in the sql server database until you synchronize.

More Information

Article Id: KBO2SS1006

Applies to

SSMA 2005 for Oracle

SSMA 2008 for Oracle

Unable to migrate Oracle to case sensitive SQL Server 2005

Symptom

User may face problems when using SSMA to convert Oracle to case-sensitive SQL Server

Cause

SSMA does not support migration to case-sensitive SQL Server instance.

Resolution

Even though SSMA does not support migration to case-sensitive SQL Server, it may be used during migration though. Using SSMA is highly recommended because this way amount of manual work will be reduced significantly comparing it to migration without usage of SSMA.

More Information

Article Id: KBO2SS1005

Applies to

SSMA 2005 for Oracle

SSMA 2008 for Oracle

Deadlock occurs during execution of converted code.

Symptom

Oracle function with DML was converted using SSMA. When the converted code is executed a deadlock may occur.

Cause

Extended procedure xp_ora2ms_exec2_ex is used for conversion of Oracle stored functions with DML and other statements prohibited in SQL Server UDFs. In this case, Oracle function is converted into implementation procedure (called $impl procedure) and this $impl procedure is called from generated UDF wrapper via xp_ora2ms_exec2_ex.

Extended procedure xp_ora2ms_exec2_ex, which is part of SSMA for Oracle Extension Pack, spawns a new connection to the same SQL Server and executes specified stored procedure in this connection. It is used in many Migration Issues throughout SSMA for Oracle. One of the parameters of this XP is bind_to_transaction which can be "true" or "false".

If bind_to_transaction is "true", xp_ora2ms_exec2_ex binds a new connection to external transaction. Otherwise, called stored procedure is executed in "automomous" transaction.

xp_ora2ms_exec2_ex cannot bind to external transaction if called from SQL Server UDF. That makes all calls to stored procedures made in spawned connection processed in "automomous" transaction, causing possible deadlocks and loss of transaction's atomicity.

Resolution

Replace function calls with direct EXECs of $impl procedures.

More Information

Article Id: KBO2SS1004

Applies to

SSMA 2005 for Oracle

SSMA 2008 for Oracle

Cannot see SCOTT schema in Metadata explorer

Symptom

The user cannot see the SCOTT schema in the Oracle Metadata explorer. They can see SYS and SYSTEM schemas.

Cause

SCOTT schema is not selected in Project settings/Default Project Settings.

Resolution

The Loading System Objects page of the Project Settings dialog box lets you specify which Oracle system objects, SSMA converts and loads into SQL Server.

The Loading System Objects pane is available in the Project Settings and Default Project Settings dialog boxes:

To specify settings for all SSMA projects, on the Tools menu, select Default Project Settings, click General at the bottom of the left pane, and then click Loading System Objects.

To specify settings for the current project, on the Tools menu, select Project Settings, click General at the bottom of the left pane, and then click Loading System Objects.

More Information

Article Id: KBO2SS1003

Applies to

SSMA 2005 for Oracle

SSMA 2008 for Oracle

Common Requirement: User does not have required permissions

Symptom

A window will display when the user is trying to use SSMA connect to source Oracle database to load the Oracle metadata, saying that user does not have permissions: SELECT ANY TABLE, CREATE ANY PROCEDURE, SELECT ANY SEQUENCE, CREATE ANY TYPE,CREATE ANY TRIGGER

Cause

The above window will display when the user does not have one of these permissions:

"SELECT ANY TABLE",

"CREATE ANY PROCEDURE",

"SELECT ANY SEQUENCE",

"CREATE ANY TYPE",

"CREATE ANY TRIGGER

Resolution

The account that is used to connect to the Oracle database must have at least CONNECT permissions. This enables SSMA to obtain metadata from schemas owned by the connecting user.

To obtain metadata for objects in other schemas and then convert objects in those schemas, the account must have the following permissions: CREATE ANY PROCEDURE, EXECUTE ANY PROCEDURE, SELECT ANY TABLE, SELECT ANY SEQUENCE, CREATE ANY TYPE, CREATE ANY TRIGGER

Yes, you can ignore the warnings and click on continue so that SSMA will get the metadata from the schemas owned by the user. You can proceed with schema migration and data migration.

SSMA won't write anything to the source (oracle) database when you run schema migration and data migration.

More Information

Article Id: KBO2SS1002

Applies to

SSMA 2005 for Oracle

SSMA 2008 for Oracle

System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.

Symptom

When doing data migration you may get the following error. Following message may be displayed on the console.
Migrating data...
Analyzing metadata...
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.

Cause

This error may be caused due to one the following reasons:
a) Oracle client provider is not installed on the sql server, you are trying to connect.

b) SSMA Extension Pack is not installed on SQL Server

c) Oracle Client may not be accessible to the DataMigrator utility from Extension Pack. This utility is started with the same account used to run SQL Agent.

Resolution

SSMA Data Migrator needs Oracle client provider installed on the SQL Server machine to perform data migration. When it does not find Oracle Client provider it gives this error.

1. Install the Oracle Client provider on your sql server and verify data migration.

2. Cross check Extension pack is installed on your sql server machine

3. Oracle Client may not be accessible to the BCPDataMigrator utility from Extension Pack. This utility is started with the same account used to run SQL Agent. Make sure this account has access to the Oracle client folder and it’s contents.

More Information

Article Id: KBO2SS1001

Applies to

SSMA 2005 for Oracle

SSMA 2008 for Oracle