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.
When converting the ROWNUM pseudocolumn, SSMA provides two forms of emulation:
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
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;
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
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.
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.
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
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?