This article is going to explain how you connect to a MySQL data source through the Application Specific ODBC connector.
While the ODBC option as available, the ADO.net provider is recommended for improved performance.
Install and set up the provider
To use the ODBC connection to MySQL you need to have a 64 bit ODBC provider. You can use this https://dev.mysql.com/downloads/connector/odbc/. Download and install it.
Open ODBC Data Sources Administrator (64-Bit) go to System DSN and press Add. Find the driver in the list and press Finish.
Give it a name add the server in the TCP/IP Server field, add 3306 in Port, add the user you run as in the User name field and the user accounts password in the Password field. When this is set up, press Test.
You can find all the databases the server contains by looking through the drop down menu.
Add a MySQL ODBC data source
To add it, right click on Data Sources, go to Data Sources, Application Specific ODBC and choose Add MySQL 5.1 Client data source.
In the menu choose the System DSN just created.
- The default escape character is Accent Grave (`). Keep this unless your source somehow uses another option.
- Set the Override text type variable length field to true to make all text fields become varchar instead of char.
- Set the Override character encoding field to Unicode to make all text fields become nvarchar or nchar instead of varchar or char.
- Set the Override text type length field to force all text fields to be the length provided.
- Set the Override numeric type precision field to force all decimal values to be the amount provided.
- Check the Convert out-of-range dates to SQL Server min/max value field to make all dates become at least 1753-01-01. In MySQL the smallest allowed date is 0001-01-01. That is not the case for datetime fields in MSSQL where it is 1753-01-01.
Synchronize with the data source and see the fields. In this you will only get the tables related to the database you chose in the DSN setup. If you have more databases and want to connect to them as well, you need to create additional DSN providers for all of them.
Common issues with MySQL data
Date fields smaller than 1753-01-01
In MySQL the smallest allowed date is 0001-01-01 in MSSQL that is not the case for datetime fields.
If that is the case you will get the following error message.
To fix this you can use the Convert out-of-range dates to SQL Server min/max value option explained above. If you do want the date that is below this date due to it being actual data you can instead also use a data type override.
You can set up a general rule where DateTime fields are converted to DateTime2 fields. Remember that it differentiates between upper case and lower case.
Apply the override and synchronize with the data source. You should get a notification about which fields were affected by the change.
Versioning issues
The provider we have is made specifically for MySQL version 5.1. In this link you can see what the current version of MySQL is Link.
If there is issues with the provider due to this you can use the generic ODBC provider. Guide here or you can use the ADO.NET MySQL provider. There is a guide here.
