Bug: INSERTing into a MySQL Database via Linked Server

Recently I found myself involved in a project where a tab delimited text file (updated daily) was being imported into a MS SQL Server 2008 R2 database and then the single imported table was manipulated into a handful of others in a completely different format. This was done to stage the data in a manner which was going to be exported to Excel and then uploaded to an Open Cart based eCommerce site.

Once this process was complete there was an issue discovered with the uploading extension to Open Cart and I was asked to convert this process into direct INSERTS/UPDATES into the MySQL database schema instead. Naturally, I decided to not re-invent the wheel so I set out to setup a linked server to the MySQL database and then just run the necessary logic and DML statements from MS SQL Server and pushing to MySQL.

Detailed instructions of how to setup a MySQL linked server via the SSMS GUI is located here in a post by Taylor Gerring.

After successful validating my connection with read-only queries I wrote myself a stored procedure which would basically perform a merge process based on certain business logic and as soon as I executed the proc in my testing environment I encountered this error.

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "MYSQL" reported an error. The provider did not give any information about the error.
Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "MSDASQL" for linked server "MYSQL" could not INSERT INTO table "[MSDASQL]". Unknown provider error.

As it turns out, even though by following Taylor’s instructions I assigned a catalog to the ODBC DSN, the GUI requires and fails to set the catalog setting in the linked server. Another oddity is that this bug only affects INSERT statements because my read-only queries all worked just fine without setting the database name. Below is an example of one of my test queries which worked.

DECLARE @optionID as INT
SELECT @optionID = option_id
FROM MYSQL_OPENCART...[option_description]
WHERE name = 'Add-ons'

So now what do we do about this?

We are going to have to recreate our linked server with T-SQL instead so we can manually set the database name. Below is a statement which I was successfully with and which I first gathered from a discussion about this bug here.

EXEC master.dbo.sp_addlinkedserver @server='MYSQL', @srvproduct='MySQL',
@provider='MSDASQL', @provstr='DRIVER={MySQL ODBC 5.1
Driver};SERVER=HOST;Port=3306;USER=uid;PASSWORD=pw;OPTION=3;DATABASE=mydb;

With this annoying bug identified and worked around I’ve move forward with my project and  hope that this won’t be necessary in SQL Server 2012.

Versions Suffering from this Bug:

  • MS SQL Server 2008
  • MS SQL Server 2008 R2

Leave a Reply

%d bloggers like this: