Hi,
I have come across a situation where I need to migrate my MySQL db to
MSSQL in wso2 api manager. I have so far tried the following.
1) Using the ODBC connector and creating linked databases in MSSQL
In this approach, when importing the database, the schema seems to
change, for e.g. for tables that have Auto increment has been disappeared
when converting to a MSSQL schema, also the not null, null definitions have
been swapped at certain instances.
2) Used ODBC connector to import data to an existing database
I kept getting the error ": The Source - am_api_comments was unable to
retrieve column information for the SQL command. The following error
occurred: ERROR [42000] [MySQL][ODBC 5.3(a)
Driver][mysqld-5.1.73-community]You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right
syntax to use near '"am_api_comments"' at line 1" for every table.
3) Tried some online tools
This too brought the errors in not converting the schema's properly as
step 1.
4) Exporting MySQL table by table as csv and importing them to MSSQL
This is the only way I could get it working. It is a very tedious way
of getting it done :(
i.e. 1) Export the data in MySQL data as csv file. When importing must
have a IFNULL check done for every varchar and not null column so that the
csv value is at the end correct so that the SQL can import it. An example
for this is that, when the MySQL column's have null values, when exporting
to csv, in the csv file it is needed to have a blank value, for this we
need to do the check. Once the csv's are ready from table to table, we can
import them to the MSSQL database, where we have already created the schema.
I am not sure if I did step 1 & 2 correct or if there was any issue in it.
I followed some online article and blog but could not succeed. If anyone
has any experience on this or knows of any other ways I could try please do
let me know.
--
Thanks and Regards
*,Shani Ranasinghe*
Senior Software Engineer
WSO2 Inc.; http://wso2.com
lean.enterprise.middleware
mobile: +94 77 2273555
linked in: lk.linkedin.com/pub/shani-ranasinghe/34/111/ab
_______________________________________________
Dev mailing list
[email protected]
http://wso2.org/cgi-bin/mailman/listinfo/dev
