martes, 10 de abril de 2012

Advanced SQL Queries Migration, SQL Query Editor convert Queries than SSMA can't

In current Migration Project, from Sybase to SQL Server, we use SSMA for Sybase (Sql Server Migration Assistant).

There are some "complex" queries with *= and =* operators, and sometimes SSMA can't transform queries correctly.

We want to change it for LEFT/RIGHT  JOIN and so on, because it seems that Microsoft only will have compatibility with two previous versions, for example:

  • SQL 2008, supports 2005 and 2000.

  • SQL 2012, will support 2008 and 2005 (I supose).


In fact, it affects to NON-ANSI join operators, because this compatibility (for SQL Server 2000), takes no longer in SQL 2012.

First of all we test the query as is in SQL Server (2008 with database compatibility set to 2000) :

Error: Query contains an outer-join request that is not permitted.


 


The more usual is to use SSMA, so we insert it like a statement in SSMA Project Migration.



Some queries with unions, sub-selects NON-ANSI JOIN and so more, can't be transformed correctly.

TIP: 

When it occurs, try to copy the original statement to New Query Window, in SQL Server, select it, and go to Design Query in Editor. It convert the statement better than SSMA. It's a good solution for some statements, but in a migration project, with thousand's of statements, it's not a good practice, because we can't do it massively.



 

(For business confidentiality, I can't post the statements, but As soon as I retrieve a "big" query with this conversion problem, I'll try to post here.)

No hay comentarios:

Publicar un comentario