Error when executing Insert Trigger with distributed Querys (OpenQuery)

Question

My MSSQL trigger:

ALTER TRIGGER [dbo].[ioTrigger_dbo_vsolv_mst_tproddtchng]
ON [dbo].[vsolv_mst_tproddtchng]
for INSERT
AS 
BEGIN
SET NOCOUNT ON;

INSERT  OPENQUERY (LINKED,'SELECT proddtchng_product_gid,
    proddtchng_mrp,
    proddtchng_tndpwtax,
    proddtchng_tndpwotax,
    proddtchng_kldpwtax,
    proddtchng_kldpwotax,
    proddtchng_costprice,
    proddtchng_website,
    proddtchng_purpose,
    proddtchng_weight,
    proddtchng_suitable,
    proddtchng_workingrange,
    proddtchng_cutoff,
    proddtchng_timedelay,
    proddtchng_description,
    proddtchng_validfrom,
    proddtchng_validto,
    proddtchng_createby,
    proddtchng_createdate,
    proddtchng_isactive,
    proddtchng_isremoved
from  vsolv_line_tn.vsolv_mst_tproddtchng')
SELECT 
    proddtchng_product_gid,
    proddtchng_mrp,
    proddtchng_tndpwtax,
    proddtchng_tndpwotax,
    proddtchng_kldpwtax,
    proddtchng_kldpwotax,
    proddtchng_costprice,
    proddtchng_website,
    proddtchng_purpose,
    proddtchng_weight,
    proddtchng_suitable,
    proddtchng_workingrange,
    proddtchng_cutoff,
    proddtchng_timedelay,
    proddtchng_description,
    proddtchng_validfrom,
    proddtchng_validto,
    proddtchng_createby,
    proddtchng_createdate,
    proddtchng_isactive,
    proddtchng_isremoved
FROM
    inserted;
END

When I am executing the above trigger I am getting the following error:

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

I have created a Linked server to Mysql from Sqlserver2008, I have already configured the DTC and its status is running, automatic, please help me how should I overcome the above error.

Are you trying to create a trigger on Mysql instance using SQL server ? — Ven
Can you give details of what version of SQL you are using and also Mysql, I assume you are missing ODBC driver in your VM ware where MSSQL is hosted or it could be not upto date. Are you able to execute a select query using openquery — Ven
sql server 2008,mysql version 5.7 and i can execute select query using openquery — selvakumar
@Thomas Nothing wrong with Syntax i bet. Its connection issues with Linked servers — Ven

Ven Ven · Accepted Answer · 2018-06-29T13:00:29

Its your Distrubution tranasaction property which breaks while creating procs/triggers into Linked servers, especially with Non-MSSQL connections

1) right click on Linked server

2) Select Properties and enable this- Set Property to False

Run Proc:

EXEC master.dbo.sp_serveroption @server=N'SVRLINK',
 @optname=N'remote proc transaction promotion', @optvalue=N'false

Error when executing Insert Trigger with distributed Querys (OpenQuery)

2 Answers