Tips for migrating SQL Anywhere DB to MS SQL (mainly for functions)

[vlad1]

Hi,

No, before you ask, we are not defecting. We've been proud champions of SQL Anywhere for 20 years, however, for one client we have to convert the database to MS SQL. We reckon our application is fairly DB agnostic to just pick up ODBC and work correctly, but it's for the actual DB migration that we need to produce a feasibility study.

We've already attempted a migration of all tables, ref integrity and data using the tool SQL Lines. Works really well, but it didn't migrate views and functions/procedures. The website states that there is another tool SQL converter exactly written for procedures and functions, and an online converter, which does attempt to convert to Transact_SQL, but MS SQL is still giving lots of syntax differences, especially for CURSORS and CASE statements. We can fix those manually, but we have a large number of functions, it would take quite us some time for syntax editing and testing.

Is there a better tool you can recommend that we could trust to convert the functions?

Thanks

[Breck_Carter]

Just so you won't feel you're being ghosted :)... no, I don't know of any such tool.

[VolkerBarth]

I do not know such a tool, either. So do your functions, procedures and triggers use Watcom-SQL or Transact-SQL?

In my experience, particularly the differences for stored functions are significant, probably hard to solve for a migration tool...Triggers have also several restrictions in MS SQL in my humble experience...

[vlad1]

lol, thanks anyway Breck 🙂

[vlad1]

They're Watcom, and we can translate them to T-SQL slowly, we can even utilise in-built SQL Anywhere editor for that, but there is lots of differences between the two that do not get translated correctly, we mostly noticed it in dynamic cursors and (curiously) CASE statements.

They're relatively easy to do, it's just that all of these functions have been stress-tested for many many years, and we know they are 100% correct, whereas even with minor changes in translating them we need a lot of vigilance and testing to make sure that they are converted OK. This is what will take most of the time.

[Vlad]

I have never used these function, but maybe you can... hm... automate your tasks with their help: SQLDIALECT, TRANSACTSQL. But that's everything what I know 😞

[VolkerBarth]

Well, I thought those functions could only handle single SQL statements but apparently they work with complete CREATE FUNCTIONS statements and translate the according statements in the function's body, as well.

However, I guess it is still a lot of manual fine tuning necessary, particularly as SQL Anywhere's T-SQL dialect differs from MS SQL T-SQL in many aspects...

[vlad1]

Thanks, SQL Anywhere already does have option "Translate to Transact-SQL", but from what we can see, it mostly just sticks "@" character in front of variable names. However, there are still a lot of inconsistencies between that and MS SQL syntax, mostly as I said with dynamic cursors and case statements.

I mean it's still useful in cutting down a lot of silly work, it's just that it would be perfect if we didn't have to translate functions that we know are working perfectly right now.

[VolkerBarth]

it would be perfect if we didn't have to translate functions that we know are working perfectly right now.

Fully agreed. If those customers only knew...

[chris_keating]

Transact-SQL dialect used in SQL Anywhere is based on Adaptive Server Enterprise (ASE) TSQL dialect. Since MSS become independent from ASE, the MSS TSQL dialect has diverged making ASE and MSS TSQL not completely compatible.

I would certainly review the documentation which covers much of the behaviour differences between Watcom and TSQL i.e., may explain the cursor differences you previously raised.

As an aside, the TRANSACTSQL method should be making more changes if the source is a Watcom dialect procedure. There are several differences in the syntax of a Watcom and TSQL dialect procedure.

Unfortunately, migration to MSS requires some effort as you have already learned.