Migrating Oracle to SQL Server using SSMA - Error O2SS0188 Sequence not found in source metadata

Article
06/29/2011

By Bill Ramos and Vishal Soni, Advaiya Inc.

This blog post covers some of the reasons why SQL Server Migration Assistant (SSMA) for Oracle cannot convert a statement that contains reference to an Oracle SEQUENCE object. A SEQUENCE is an Oracle object that is used to generate an ordered number sequence. This is useful when users need to create a unique number to act as a primary key or foreign key in any table.

Error O2SS0188 Sequence not found in source metadata

Background

To assist in the migration, SSMA can emulate most of the Oracle SEQUENCE behaviors. In SSMA for Oracle, SEQUENCE emulation is implemented by using both the Transact-SQL procedures and functions, and extended stored procedures.

When converting the sequence, SSMA uses two additional objects in the target SQL Server schema:

1. A Table named $SSMA_seq_<sq>, that has a single identity column

2. A Stored procedure $$SSMA_sp_get_nextval_<sq>

SSMA displays the error “O2SS0188 sequence not found in source metadata” if it is not able to locate the sequence object in the SSMA source metadata for the project.

For more information on how SSMA implements the Oracle sequence object, please see the article from DBBest titled Migration of Oracle sequences to SQL Server 2005/ 2008

Reasons

There are two possibilities leading to the scenario where SSMA is not able to find the sequence in the source metadata:

· The SEQUENCE object was excluded from the import operation when connecting to the Oracle database.

· The SEQUENCE object is located on a remote database by using a database link.

Possible Remedies

The corresponding sequence was not found in the source metadata. This might happen due to some security features in Oracle or some issues while connecting with the Oracle database. To resolve this, you need to ensure the following:

- The SSMA is properly connected with the Oracle database and all the Objects from the Oracle database like Triggers, Procedures, and particularly Sequences are visible under the Oracle Metadata Explorer.

Consider the following example:

INSERT INTO employees (employeeid, lastname, Firstname) VALUES(sq1.nextval, 'David', 'Miller');

Here, this query refers to the sequence sq1 created in Oracle, which SSMA will try to migrate to SQL server. But if this SSMA is not able to find the reference of this sequence, it should give the following error:

In this case, make sure that the SSMA is properly linked with the Oracle Database, by referring the Oracle Metadata Explorer. If the corresponding required Sequence is not visible in the Metadata explorer node for Sequences, reconnect with the Oracle with DBA permissions. The referred Sequence should then be visible under the Oracle Metadata explorer.

If the SEQUENCE that you are referencing is in another database using a database link, SSMA doesn’t support the direct migration of the sequence for shared use in SQL Server. Instead, recreate the SEQUENCE on the source database, remove the database link reference in the query, and then use SSMA to convert the SEQUENCE into the SQL Server database.

There are several other errors related to the SEQUENCE that you may encounter. These include the following:

· O2SS0214 Column has bound sequence, but sequence was not found in source metadata

· O2SS0216 Call to identity sequence CURRVAL not supported

· O2SS0221 Call to identity sequence NEXTVAL not supported