Error when updating MySQL Data From SQL Server

Question

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=@mounika _job_agreement_id, bms_job_agreement_id=@mounika _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

Answer 1

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'

Answer 2

Thanks for this, never worked with MySQL before