SQL Server 2019 Express : reading from / writing to SQL Server database (serverless)

I've been struggling with a trigger on my SQL Server Express database that verifies an incoming record and then writes it to my Azure SQL database (serverless).

Initially I ran the "trigger t-sql" code as a standard query in SSMS (19.0 Preview 2); it worked, and transferred 50k of records into the Azure SQL database.

I then updated this T-SQL code to reference the [inserted] table, and manually inserted a row - and irrespective of what I did in terms of using / not using cursors, variables, BEGIN DISTRIBUTED TRANSACTION, different users, and more the trigger failed to insert while the identical process run as a query from SSMS worked.

I then simplified the INSERT trigger to a simple:

CREATE TRIGGER [dbo].[tc_table_ITrig]
ON [dbo].[tc_table]
AFTER INSERT
AS
BEGIN
    INSERT INTO [SQL_Database].[DatabaseName].[schema].[Table]([Var1], [Var2], [Var3], [Var4])
        SELECT 1, 2, 3, 4
END;
GO

If I run the statement in SSMS it succeeds

If I insert into [dbo].[tc_table] (which initiates the trigger) the insert fails with the following message:

OLE DB provider "MSOLEDBSQL" for linked server "SQL_Database" returned message "The parameter is incorrect.".

Msg 7399, Level 16, State 1, Procedure tc_positions_ITrig, Line 13 [Batch Start Line 0]
The OLE DB provider "MSOLEDBSQL" for linked server "SQL_Database" reported an error. One or more arguments were reported invalid by the provider.

Msg 7391, Level 16, State 2, Procedure tc_table_ITrig, Line 13 [Batch Start Line 0]
The operation could not be performed because OLE DB provider "MSOLEDBSQL" for linked server "SQL_Database" was unable to begin a distributed transaction.

I sincerely hope someone can give me guidance on this; I've been struggling with it for over a month.

CodePudding user response：

To run distributed transaction, you need to make the settings in both machines.

configure msdtc on sql server

CodePudding user response：

Distributed transactions in SQL Server (Express) relies on MSDTC which does not exist for cloud services as Azure SQL Database. You cannot include an insert in your local db and in cloud db in the same transaction. Perhaps you are better off with building a solution based on change tracking?