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