Insert error using Linked Server MySQL

Question

I have created a linked server using ODBC driver with following provider string

DRIVER={MySQL ODBC 5.1 Driver};SERVER=HOST;USER=uid;PASSWORD=pw;OPTION=3

My linked server is named MYSQL. If i do Select and UPDATE in that way it works right

SELECT * FROM OPENQUERY(MYSQL,'SELECT * FROM DatabaseName.Table')
UPDATE OPENQUERY(MYSQL, 'SELECT * FROM DatabaseName.Table where test=0') SET test=1

when i try to perform an insert with following syntax

INSERT OPENQUERY(MYSQL,'SELECT Column1, Column2, Column3 from DatabaseName.Table WHERE 1=0') VALUES (10,20,30)

i have this following error

The OLE DB provider "MSDASQL" for linked server "MYSQL" could not INSERT INTO table "[MSDASQL]". Commands out of sync; you can't run this command now.

Solved creating my linked server in that way: 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; — Luigi Saggese, Apr 27 '12 at 09:34

score 0 · Accepted Answer · answered Apr 27 '12 at 09:35

0

I think you've got the same problem as this thread on another forum. From the answer that seems to have solved the problem for people there, it looks like your missing the database name from your provider string. Try adding DATABASE=mydb; to your provider string.

Here is the original answer:

Apparently if you try to create the linked server using studio and the various menus, a small detail is not saved in the connection string, which is the database!
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;
the bug in the odbc driver seems to affect only the insert statement and has been around since 2008... i would have hoped that mysql fixed it by now...

after you create the linked server using the procedure as above, the insert works as well as all the rest!

answered Apr 27 '12 at 09:35

Bridge

29,818
9
60
82

1

@Luigi It looks like you posted your comment about the same time I posted my answer. After asking your question in future, if you solve it yourself, please add the resolution as an answer and accept it. That way other users won't try to answer your question, and people with the same problem can find the answer without digging around in your comments. – Bridge Apr 27 '12 at 09:50
1

@Luigi So please now mark this question as resolved, by either posting your answer as a new answer and accept it, or accepting my answer. – Bridge Apr 27 '12 at 09:51
2

You are right Bridge. But i've tried to answer my question but i haven't 100 of reputation and to do that i have done a comment to my question. Now i mark your answer as resolved ;) – Luigi Saggese Apr 27 '12 at 12:51

score 0 · Answer 2 · edited Feb 08 '13 at 11:37

I just experienced the same problem and managed to solve it by deactivating Force use of forward-only cursor and Don't cache results for forward-only cursor for my ODBC connection.

I don't know for sure if both options need to be disabled. I got the hint from here

I linked SQL Server 2012 to MySQL v5.1 via MySQL ODBC/Connector v5.1.11, 64-bit. My link data source is configured to use a system ODBC DSN which already had the MySQL database specified (this is in relation to the previous answers) and yet I still got the error before deactivating those cursors options (by default they are not enabled for new data sources, I was the one who enabled them at some point).

Insert error using Linked Server MySQL

2 Answers2