AFTER INSERT trigger on local table inserting to Azure table fails

Question

I have a really simple trigger on a SQL table in a local database. The trigger looks something like this:

ALTER TRIGGER [dbo].[trg_UpdateAzureDB]
ON  [dbo].[my_local_table]
AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here
    INSERT INTO [myazuresvr].[myazuredb].[dbo].[myazuretable]([ImageId], [PimObjectId], [Relation], [ObjectType])
    SELECT * FROM inserted
END

So, as you can see what I want to do is push the data that has been inserted into an Azure SQL database table. However, when I insert to the table, I get the following error:

Msg 7399, Level 16, State 1, Procedure trg_UpdateAzureDB, Line 16 [Batch Start Line 10]
The OLE DB provider "SQLNCLI11" for linked server "myazuresvr" reported an error. One or more arguments were reported invalid by the provider.
Msg 7391, Level 16, State 2, Procedure trg_UpdateAzureDB, Line 16 [Batch Start Line 10]
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "myazuresvr" was unable to begin a distributed transaction.

The Server is defined as a linked server within the local server.

The really weird thing is that if I do:

INSERT INTO [myazuresvr].[myazuredb].[dbo].[myazuretable]([ImageId], [PimObjectId], [Relation], [ObjectType])
    SELECT * FROM [mylocaltable]

This completes fine. It would appear to be something to do with the fact I am inserting into an Azure table from within a trigger...

Local SQL version is 12.0.6108 Azure version shows as 12.0.2000

Any suggestions?

Answer 1

The error message says it's related to distributed transaction. Setting "Enable promotion of distributed transaction" flag to false should work.