What is the proper way to configure a linked server from SQL Server 2008r2 to MySQL that will allow inserts?

I have set up the linked server and I can query it and I can update it. When I try to insert a record I get this message:

OLE DB provider "MSDASQL" for linked server "homeprojectadmin" returned message "[MySQL][ODBC 5.1 Driver][mysqld-5.0.92-log]Commands out of sync; you can't run this command now".
Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "MSDASQL" for linked server "homeprojectadmin" could not INSERT INTO table "[MSDASQL]".

My insert looks like this:
insert INTO OPENQUERY(homeprojectadmin,'select * from home_projects_app_links')
values ('1234','Test Merchant','1234-12','12','https://creditapplication.xyz.com/?program=Home%20Projects&referral=1234-12&merchant=1234','https://uat-creditapplication.xyz.com/?program=Home%20Projects&referral=1234-12&merchant=123','https://1uat-creditapplication.xyz.com/?program=Home%20Projects&referral=1234-12&merchant=1234')

Microsoft SQL ServerMySQL Server

Last Comment

jmelcher

8/22/2022 - Mon

lcohan

3/13/2012

Is it the "Allow Inprocess" property checked or unchecked on the linked server under - Server Objects - Linked Servers - Providers on the MSDASQL? Caution that the setting is for all linked servers on that box so don't just change it.

jmelcher

3/13/2012

ASKER

It's the "Allow Inprocess" property. It was checked and wasn't working; I unchecked it and it still didn't work. Do I need to re-starts SQL Services when I change that property?

lcohan

3/13/2012

" Do I need to re-starts SQL Services when I change that property?"

I believe that is way deeper tied to the OS (ODBC and all the rest) and it needs a reboot unfortunately. That's what I have to do usually when my box linking SQL to MySQL and Postgress starts to throw errors.

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

William Peck

jmelcher

3/14/2012

ASKER

I unchecked allow inprocess, rebooted and tried the insert.

I get the exact same error.

lcohan

3/14/2012

I think you should put it back whenever you have a chance if it's not done already and could you please check your insert query as I believe it should be like below:

INSERT INTO OPENQUERY(homeprojectadmin,'select * from home_projects_app_links' values ('1234','Test Merchant','1234-12','12','https://creditapplication.xyz.com/?program=Home%20Projects&referral=1234-12&merchant=1234','https://uat-creditapplication.xyz.com/?program=Home%20Projects&referral=1234-12&merchant=123','https://1uat-creditapplication.xyz.com/?program=Home%20Projects&referral=1234-12&merchant=1234'))

lcohan

3/14/2012

If you can please replace the SELECT * with actual column list and I found sometimes you may need to CAST/CONVERT the VALUES to exactly match the target table column datatype as implicit conversion in drivers not always works well.

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

jmelcher

3/14/2012

ASKER

The syntax you've suggested isn't correct.

I did replace the * with the column list. Same error.

insert INTO OPENQUERY(homeprojectadmin,'select unique_identifier, merchant_name, promotion_code, plan_code, prod_url, uat_url, test_url from home_projects_app_links')
values ('1234','Melcher Test Merchant','1234-12','12','https://creditapplication.greenskycredit.com/?program=Home%20Projects&referral=1234-12&merchant=1234','https://uat-creditapplication.greenskycredit.com/?program=Home%20Projects&referral=1234-12&merchant=123','https://1uat-creditapplication.greenskycredit.com/?program=Home%20Projects&referral=1234-12&merchant=1234')

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

3/22/2012

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.