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

Question

Post reply

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

agarcia 38992

SSC Rookie

Points: 26
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!!!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eric M Russell SSC Guru Points: 125600 More actions · Answer 1

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 SSC Rookie Points: 26 More actions · Answer 2

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: 323574 More actions · Answer 3

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: 125600 More actions · Answer 4

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