Hi Madusanka/ Dhanuka, Thanks for your responses.
@Madusanka, The SSMA tool you mentioned seems to work fine. It seems to convert the schema properly. Will do some more testing and verify, thanks a lot for the info. On Mon, Apr 27, 2015 at 6:38 PM, Madusanka Premaratne <madusan...@wso2.com> wrote: > Hi Shani, > I used a tool called SQL Server Migration Client. But I did this a few > years back and I think it only works with windows. > Have a look at [1] > > [1] - http://blogs.msdn.com/b/ssma/p/mysql.aspx > > Thanks, > Madusanka > > On Mon, Apr 27, 2015 at 4:44 PM, Dhanuka Ranasinghe <dhan...@wso2.com> > wrote: > >> I haven't done such thing before but you can try out something like this. >> >> Write some sql scripts which has some select insert statements (select >> form mysql insert into mssql). Hope this is not much complex since column >> names and table names are equal. You may need to write simple Java program >> to select queries and map them to insert statements which use both jdbc >> drivers. If you can use JPA this can be done easily. >> >> Cheers, >> Dhanuka >> >> *Dhanuka Ranasinghe* >> >> Senior Software Engineer >> WSO2 Inc. ; http://wso2.com >> lean . enterprise . middleware >> >> phone : +94 715381915 >> >> On Mon, Apr 27, 2015 at 4:08 PM, Shani Ranasinghe <sh...@wso2.com> wrote: >> >>> >>> 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 >> Dev@wso2.org >> http://wso2.org/cgi-bin/mailman/listinfo/dev >> >> > > > -- > *Madusanka Premaratne* | Associate Software Engineer > WSO2, Inc | lean. enterprise. middleware. > #20, Palm Grove, Colombo 03, Sri Lanka > Mobile: +94 71 835 70 73| Work: +94 112 145 345 > Email: madusan...@wso2.com | Web: www.wso2.com > > [image: Facebook] <https://www.facebook.com/maduzanka> [image: Twitter] > <https://twitter.com/rmmpremaratne> [image: Google Plus] > <https://plus.google.com/u/0/+MadusankaPremaratnemaduz/about/p/pub> [image: > Linkedin] <http://lk.linkedin.com/in/madusanka/> [image: Instagram] > <http://instagram.com/madusankapremaratne> [image: Skype] > <http://@rmmpremaratne> > > -- 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 Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev
