SQL Server 2019 Express：从 /写入到SQL Server数据库（无服务器） - 在SSM中工作，但在插入触发器中失败
我一直在使用SQL Server Express数据库上的触发器挣扎，该数据库验证了传入记录，然后将其写入我的Azure SQL数据库（无服务器）。
最初，我将“ Trigger T-SQL”代码作为SSMS中的标准查询（19.0 Preview 2）中的标准查询;它起作用，并将50K记录转移到Azure SQL数据库中。
然后，我更新了此T -SQL代码以引用[插入]表，然后手动插入了一行 - 无论我在使用 /不使用光标，变量，开始分布式事务< / code>，< / code>方面所做的一切如何不同的用户以及更多的触发因素无法插入，而相同的过程作为SSM的查询运行。
然后，我将插入触发器简化为简单：
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
如果我在SSMS中运行语句，
如果我将插入插入[dbo]。[tc_table]。 ：
ole db提供商“ msoledbsql”链接服务器“ sql_database”返回消息“参数不正确。”。
msg 7399，级别16，状态1，过程tc_positions_itrig，第13行[批次启动行0]
链接服务器“ sql_database”的OLE DB提供商“ MSOLEDBSQL”报告了错误。提供者报告了一个或多个论点。msg 7391，第16级，状态2，过程tc_table_itrig，第13行[批次启动行0]
无法执行该操作，因为链接服务器的Ole DB提供商“ MSOLEDBSQL” SQL_DATABASE“无法开始分布式事务。
我衷心希望有人可以为此提供指导。我一直在挣扎了一个多月。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
SQL Server（Express）中的分布式交易依赖于MSDTC，而MSDTC作为Azure SQL数据库不存在的MSDTC。在同一交易中，您不能在本地数据库和云数据库中包含插入物。也许您最好是基于更改跟踪？
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?
要运行分布式事务，您需要在两台机器中进行设置。
在SQL Server上配置MSDTC
To run distributed transaction, you need to make the settings in both machines.
configure msdtc on sql server