Can’t migrate from Sybase ASA to SQL Server using SSMA

  • Article

Unfortunately we never had Sybase ASA on our plans for SSMA. The plan is still intact and we still support migration from Sybase ASE 11.9 onwards with the latest version of SSMA for Sybase https://www.microsoft.com/en-us/download/details.aspx?id=28765

 Even if we can’t use SSMA we still have a way to migrate in case only schema and data needs to be migrated (which is mostly the case). You can use a Linked Server to great advantage. The syntax between Sybase and MS SQL Server don’t differ much as they share the same DNA. Below are the providers that you can use to setup a linked server from SQL Server to Sybase ASA and then use queries to pull data and insert into the local database.  I am providing some information below to give you pointers for the same.   

a)      Have to install the required provider/driver on the SQL Server machine.

 Sybase ASA can be connected in following ways

 OLE DB Providers

 You need an OLE DB provider for each type of data source you wish to access. Each provider is a dynamic-link library. There are two OLE DB providers you can use to access Sybase IQ:  Sybase ASA OLE DB provider The Adaptive Server Anywhere OLE DB provider provides access to Sybase IQ as an OLE DB data source without the need for ODBC components. The short name for this provider is ASAProv. 

When the ASAProv provider is installed, it registers itself. This registration process includes making registry entries in the COM section of the registry, so that ADO can locate the DLL when the ASAProv provider is called. If you change the location of your DLL, you must reregister it. 

If you use the Adaptive Server Anywhere OLE DB provider, ODBC is not required in your deployment. 

ODBC Driver

Microsoft OLE DB provider for ODBC Microsoft provides an OLE DB provider with a short name of MSDASQL.

The MSDASQL provider makes ODBC data sources appear as OLE DB data sources. It requires the Sybase IQ ODBC driver.

 b)      Create a Linked Server and then query the same.

 This link species the steps and some common errors while setting the Sybase ASA Linked Server

 Steps :  https://sql-articles.com/articles/dba/creating-linked-server-to-sybase-from-sql-server/  (These are for ASE but are the same for ASA except that you have to choose a different provider/driver)

 Connection Issues : https://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/fe29f12d-67b0-40a8-a41b-1767853a0185

 

Angshuman Nayak