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
