Luigi Saggese Luigi Saggese - 4 months ago 17
MySQL Question

Insert error using Linked Server MySQL

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.

Answer

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!

Comments