Is there a migration tool that actually works for SAP SqlAnywhere 17 to MSSQL (any recent version)?
Searching for "sqlanywhere mssql migration" yields a variety of hits including SQL Server Migration Assistance (SSMA) but all of the tools I have tried — various free and some trials of commercial offerings — all failed to handle the following test (assume T is a real table with columns A and B):
SELECT A, B + 'X' AS B1 FROM T GROUP BY B1
SqlAnywhere permits this but on MSSQL the symbol
B1 is not in scope in the
GROUP BY clause, and the query must be transformed as follows:
SELECT * FROM (SELECT A, B + 'X' AS B1 FROM T) AS T1 GROUP BY B1
I wasn't even able to test SSMA. It is available for a range of platforms including Sybase ASE but is not compatible with the database formerly known as Sybase SqlAnywhere and now titled SAP SqlAnywhere. I've tried this, and it is not interested in using my SqlAnywhere ODBC drivers.
If there isn't an out of the box migration tool then is there anything that can transform syntax? In some ways this would be even more helpful than a migration tool because our applications are riddled with queries that exhibit this particular incompatibility. I seriously considered writing a transformation myself but quickly realised that SQL syntax is hideously complex and idiosyncratic.