Migrating Oracle to SQL Server using SSMA – Error O2SS0021 Statement with ROWNUM not converted


By Bill Ramos and Ankit Matta, Advaiya Inc.

This blog post covers some of the reasons why SQL Server Migration Assistant (SSMA) for Oracle cannot convert a statement that contains the Oracle ROWNUM pseudocolumn. The Oracle ROWNUM returns a number indicating the order in which a row was selected from a table. The first row selected has a ROWNUM of 1; the second row has a ROWNUM of 2, and so on.

Error O2SS0021 Statement with ROWNUM not converted

Background

When converting the ROWNUM pseudocolumn, SSMA provides two forms of emulation:

  • With the TOP keyword of the SELECT statement if this pseudocolumn is used only to limit the size of the result set.
  • With the ROW_NUMBER() function if the row numbers appear in a SELECT list.

There are two cases where SSMA decides that it cannot convert the ROWNUM pseudocolumn to SQL Server and hence, generates the O2SS0021 error message.

  • Scenario 1: ROWNUM is included as part of a complex WHERE clause
  • Scenario 2: ROWNUM is used in a WHERE clause like this: WHERE ROWNUM > a positive integer.

Possible Remedies

Scenario 1: ROWNUM is included as part of a complex WHERE clause

Consider the following example:

DELETE FROM employees WHERE ROWNUM-1<=11+1 AND employeeid>10;

The solution is to simplify the expression for the ROWNUM to be ROWNUM <=11. The end result would translate to use the TOP clause. Below is the example of the simplified query along with the screenshot of the converted query:

DELETE FROM employees WHERE ROWNUM<=11 AND employeeid>10;

clip_image002

In the next example, SSMA doesn’t know how to parse ROWNUM with the IN clause.

DELETE FROM employees WHERE ROWNUM IN (1,2,3,4);

Rather than attempting to translate this literally, you should consider using a WHERE clause that uses the primary key or a unique identifier, especially when there is no ORDER BY clause. For example, use the primary key column employeeid instead of ROWNUM as follows:

DELETE FROM employees WHERE employeeid IN (1,2,3,4);

Scenario 2. ROWNUM is used in a WHERE clause like this: WHERE ROWNUM > a positive integer

Consider the following example:

SELECT employeeid FROM employees WHERE ROWNUM > 2 OR employeeid > 8;

Whenever you are using ROWNUM > Positive integer, the expression is always evaluated to false. SSMA will report the problem using error O2SS0021. Simply remove the ROWNUM > 2 expression to correct the problem.

Related Errors

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

In most if these situations, consider what the query is trying to accomplish and rewrite it to use the ROW_NUMBER() emulation, TOP clause, or use a unique expression for your WHERE, GROUP BY, or ORDER BY clauses.

References

For more information, check out the following references.

Migrating Oracle to SQL Server 2008 White Paper

ROW_NUMBER() MSDN Help Topic

KB Article 186133 – How to dynamically number rows in a SELECT Transact-SQL statement

ROWNUM Pseudocolumn – Oracle Database SQL Language Reference

Comments (1)

  1. Rob says:

    I have sql server 2008 r2 developer and oracle 10g on my pc. From the migration assistant I can connect to both computers I select the source and destination schema and database but the convert schema, migrate data, and create report buttons and menu items are ghosted out and do nothing when selected. I am not tring to copy a sys or system schema from oracle, just a simple schema. What am I doing wrong?