Linked server sal server 2008 r2 with mysql works with select but not with insert into

• agarcia 38992

  Grasshopper

  Points: 16

  More actions

  February 10, 2015 at 1:25 pm

  #294607

  Hi,

  I've created a linked server in my sqlserver2008 r2 with an ODBC 5.3.4 for mysql, both work well with selects in my openquery statements but when i try to do an insert into this error pops!:

  El proveedor OLE DB "MSDASQL" del servidor vinculado "MYSQL" devolvió el mensaje "[MySQL][ODBC 5.3(a) Driver][mysqld-5.0.51b-community-nt-log]Commands out of sync; you can't run this command now".

  in English:

  Thw OLE DB "MSDASQL" provider from the links erver "MYSQL" returned the message "[MySQL][ODBC 5.3(a) Driver][mysqld-5.0.51b-community-nt-log]Commands out of sync; you can't run this command now".

  please any help!!!

• Eric M Russell

  SSC Guru

  Points: 125596

  More actions

  February 10, 2015 at 1:40 pm

  #1776751

  Reply back with the actual OpenQuery statement.

  "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

• agarcia 38992

  Grasshopper

  Points: 16

  More actions

  February 10, 2015 at 1:44 pm

  #1776754

  this is my qry:

  INSERT INTO OPENQUERY(MYSQL, 'SELECT DocNum, DocEntry, DocDate, CardCode, CardName, SubTotal, DiscPrcnt, DiscSum, VatSum, DocTotal, Comments, WhsCode, DBase, Referencia FROM OC_Abiertas')

  SELECT * FROM [GEM].[dbo].[WEBGEM_OPEN_OP]

• Lowell

  SSC Guru

  Points: 323522

  More actions

  February 10, 2015 at 1:55 pm

  #1776757

  openquery is not an obbject you can insert into. its a command that can RETURN a table like structure, but not insert into one.

  i think you can use a four part linked server command:

  INSERT INTO MYSQL...OC_Abiertas(DocNum, DocEntry, DocDate, CardCode, CardName, SubTotal, DiscPrcnt, DiscSum, VatSum, DocTotal, Comments, WhsCode, DBase, Referencia)

  SELECT * FROM [GEM].[dbo].[WEBGEM_OPEN_OP]

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Eric M Russell

  SSC Guru

  Points: 125596

  More actions

  February 10, 2015 at 2:18 pm

  #1776762

  You can attempt the following, but I doubt it would work, even if the target were another SQL Server instance.

  INSERT INTO MYSQL..OC_Abiertas

  ( DocNum, DocEntry, DocDate, CardCode, CardName, SubTotal, DiscPrcnt

  , DiscSum, VatSum, DocTotal, Comments, WhsCode, DBase, Referencia )

  SELECT DocNum, DocEntry, DocDate, CardCode, CardName, SubTotal, DiscPrcnt

  , DiscSum, VatSum, DocTotal, Comments, WhsCode, DBase, Referencia

  FROM [GEM].[dbo].[WEBGEM_OPEN_OP];

  I'd suggest either writing a stored procdure in MySQL that inserts into OC_Abiertas from a linked connection (?) to MSSQL..WEBGEM_OPEN_OP, or write an SSIS package that does this.

  In SSIS this would be a simple DataFlow task.

  "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho