Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm in linked server hell! I've created a linked server to a remote MySQL database. I'm able to query and do updates. However when I attempt an insert I get an error:

"[MySQL][ODBC 5.1 Driver][mysqld-5.0.92-log]Commands out of sync; you can't run this command now".

What are the correct Settings I should have? I realize there are 3 components with settings (MySQL ODBC Driver, MSDASQL Provider, Linked Server Object). Here's what I have so far:

ODBC Driver:
Connection Tab: Nothing checked.
Metadata Tab: Nothing checked.
Cursors/Results Tab:  
     Don't Cache Results of forward-only cursors - checked
     Force use of forward-only cursors - checked
     Return mached rows instead of affected rows - checked
Debug Tab:  Nothing checked.
SSL Tab: Nothing checked.
Misc Tab: Nothing Checked.

MSDASQL Provider:
     The following are checked:
     Nested Queries
     Level zero only
     Allow inprocess

Linked Server:

EXEC master.dbo.sp_addlinkedserver @server = N'HOMEPROJECTADMIN', @srvproduct=N'MySQL', @provider=N'MSDASQL', @datasrc=N'HOMEPROJECTADMIN', @provstr=N'DSN=HOMEPROJECTADMIN;OPTION=3'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'HOMEPROJECTADMIN',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
share|improve this question

migrated from stackoverflow.com Mar 15 '12 at 23:06

This question came from our site for professional and enthusiast programmers.

    
you should flag this for migration to dba.se - more likely to get mileage on that site. –  ConcernedOfTunbridgeWells Mar 15 '12 at 19:29
    
Do you have MS-DTC setup and working on the server correctly? –  mrdenny Apr 16 '12 at 0:47
add comment

1 Answer 1

Just a quick observation: you using MySQL 5.1 Driver against MySQl 5.0.92

I recommend you should either

  • downgrade to MySQL 5.0 ODBC Driver (easier option)
  • upgrade to MySQL 5.1.61
share|improve this answer
    
The MySQL server is a hosted server at GoDaddy.com so I'll have a go at downgrading the ODBC Driver. –  Erik Mar 16 '12 at 12:55
    
Is there a 64-bit 5.0 ODBC Driver? I installed the 32-bit version that I found but when I start to add a new DSN I only see the 5.1 driver in the list of drivers available. Do I need to uninstall the 5.1 driver? Reboot the server? –  Erik Mar 16 '12 at 13:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.