0

My problem is that i'm trying to transfer some data to mysql from my sql server and it returns this error

OLE DB provider "MSDASQL" for linked server "SRV" returned message "[MySQL][ODBC 8.0(a) Driver]Optional feature not supported". Msg 7391, Level 16, State 2, Procedure exportbds, Line 10 [Batch Start Line 2] The operation could not be performed because OLE DB provider "MSDASQL" for linked server "SRV" was unable to begin a distributed transaction.

I have established a linked server between the last two (connection successful) and used the cmd OPENQUERY ([MyLinkedserver], 'query') inside a stored procedure and called by a trigger after insert, so that whenever i insert my data into my table it transfers directly to mysql. here below i'll be sharing with you my code :

My trigger after insert

ALTER TRIGGER [dbo].[exportbds] on [dbvlms].[dbo].[so_bs_creation_duplicata] 
AFTER insert 
as 
exec [dbo].[exportbondesortie]
TRUNCATE TABLE [dbo].[so_bs_creation_duplicata]

My Stored procedure

ALTER PROCEDURE [dbo].[exportbondesortie]
AS
BEGIN
INSERT INTO OPENQUERY (SRV, 'SELECT id, id_bs FROM mapping_db.so_bs_creation') 
SELECT * FROM OPENQUERY (EDIPRODB2BI01, 'SELECT id, id_bs FROM dbo.so_bs_creation_duplicata') 
END
Thom A
  • 88,727
  • 11
  • 45
  • 75
Anas El Mouki
  • 1
  • https://stackoverflow.com/questions/5945435/sql-server-2000-linked-server/6651704#6651704 – Mitch Wheat Feb 14 '22 at 11:15
  • Honestly - just start over and pay attention to the code you write and the context you imply when posting. First, we don't know your system. We don't know how "SRV" is defined nor how "EDIPRODB2BI01". We don't know why you have multiple linked servers - and that is a good place to start. Your approach seems problematic in the first place. A trigger on table x that uses a linked server to the same table and then truncates that same table seems to be a big problem. I'll guess you assume a single row is inserted - a very common logic flaw. What is the actual goal here? – SMor Feb 14 '22 at 12:19
  • My system is a MSSQL Management studio 18 and MySQL Workbench both installed in a VM. My main goal is to transfer data from my sql server database to my mysql database after insert in the first db. SRV is just a linked server using MySQL odbc driver 8.0 connecting sql server and mysql and "EDIPROBB2BI01" is my instance localhost. For the insertion it can be a single row or multiple rows – Anas El Mouki Feb 14 '22 at 13:11

0 Answers0