question

AnthonyC-4036 avatar image
0 Votes"
AnthonyC-4036 asked AnthonyC-4036 answered

Error when updating MySQL Data From SQL Server

Good Morning Everyone

I am attempting to create a process to update data on a MySQL Linked Server.

So far I have set up a synonym to the Linked Server/Schema/Table
I can select data from the synonym
When attempting the update I get the following error message

UPDATE dbo.[SYNONYM NAME] SET job_agreement_id=@new_job_agreement_id, bms_job_agreement_id=@new_bms_job_agreement_id WHERE job_agreement_id=@old_job_agreement_id

(2 rows affected)

(3 rows affected)

(100 rows affected)
OLE DB provider "MSDASQL" for linked server "SRV-ORCHID" returned message "Data provider or other service returned an E_FAIL status.".
Msg 7330, Level 16, State 2, Line 36
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "SRV-ORCHID".

Completion time: 2021-04-27T11:29:33.2200247+01:00

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

You may be fighting an uphill battle. You talk to an OLE DB provider that talks to an ODBC driver that talks to MySQL. With that many components involved, it can be very difficult to say where things went wrong.

On top of that, to get good help, you need to find people who know both environments, and the ODBC driver. Myself, I have never worked with linked servers against MySQL. But two things to try/check:
1. Is job_agreement_id defined as a primary key?
2. What happens if you leave out job_agreement_id from the SET clause?

You may have better luck with EXEC AT:

EXEC('UPDATE `your table` 
       SET  job_agreement_id = ?,    
            bms_job_agreement_id = ?
       WHERE job_agreement_id=?',
 @new_job_agreement_id, @new_bms_job_agreement_id, @old_job_agreement_id)
 AT [SRV-ORCHID]

For this to work, you need to run

EXEC sp_serveroption 'SRC-ORCHID', 'rpc out', 'true'
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AnthonyC-4036 avatar image
0 Votes"
AnthonyC-4036 answered

Thanks for this, never worked with MySQL before

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.