August 25, 2023 at 5:29 pm
It turns out that the error is in a trigger on the Transfers table, so this can be ignored. Sorry if you've wasted your time reading this.
Hi,
I've got two databases at distant locations that sync evey few minutes using the Ms Sync Framework 2.1. I'm getting this error occurring:
ERROR , SyncBPO, 1, 08/25/2023 17:15:14:813, Transaction has exited due to command execution failure, throwing exception
ERROR , SyncBPO, 1, 08/25/2023 17:15:14:861, Caught exception while applying changes: Microsoft.Synchronization.Data.DbSyncException: Failed to execute the command 'BulkUpdateCommand' for table 'Transfers'; the transaction was rolled back. Ensure that the command syntax is correct. ---> System.Data.SqlClient.SqlException: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
The BulkUpdateCommand stored procedure being called in this case is this:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Transfers_bulkupdate]
@sync_min_timestamp BigInt,
@sync_scope_local_id Int,
@changeTable [Transfers_BulkType] READONLY
AS
BEGIN
-- use a temp table to store the list of PKs that successfully got updated
declare @changed TABLE ([trn_pk] char(16), PRIMARY KEY ([trn_pk]));
-- update the base table
MERGE [Transfers] AS base USING
-- join done here against the side table to get the local timestamp for concurrency check
(SELECT p.*, t.update_scope_local_id, t.scope_update_peer_key, t.local_update_peer_timestamp FROM @changeTable p LEFT JOIN [Transfers_tracking] t ON p.[trn_pk] = t.[trn_pk]) as changes ON changes.[trn_pk] = base.[trn_pk]
WHEN MATCHED AND (changes.update_scope_local_id = @sync_scope_local_id AND changes.scope_update_peer_key = changes.sync_update_peer_key) OR changes.local_update_peer_timestamp <= @sync_min_timestamp THEN
UPDATE SET [trn_itmfk] = changes.[trn_itmfk], [trn_date] = changes.[trn_date], [trn_brnfk] = changes.[trn_brnfk], [trn_qty] = changes.[trn_qty], [trn_brnfkto] = changes.[trn_brnfkto], [trn_qtyreceived] = changes.[trn_qtyreceived], [usr_fk] = changes.[usr_fk]
OUTPUT INSERTED.[trn_pk] into @changed; -- populates the temp table with successful PKs
UPDATE side SET
update_scope_local_id = @sync_scope_local_id,
scope_update_peer_key = changes.sync_update_peer_key,
scope_update_peer_timestamp = changes.sync_update_peer_timestamp,
local_update_peer_key = 0
FROM
[Transfers_tracking] side JOIN
(SELECT p.[trn_pk], p.sync_update_peer_timestamp, p.sync_update_peer_key, p.sync_create_peer_key, p.sync_create_peer_timestamp FROM @changed t JOIN @changeTable p ON p.[trn_pk] = t.[trn_pk]) as changes ON changes.[trn_pk] = side.[trn_pk]
SELECT [trn_pk] FROM @changeTable t WHERE NOT EXISTS (SELECT [trn_pk] from @changed i WHERE t.[trn_pk] = i.[trn_pk])
END
I've got the trace showing the parameters passed in and I'm trying to work out what subquery/data is causing the problem. I've changed the UPDATE side SET statement to SELECT but I'm not sure what to do with the MERGE [Transfers] ... into @changed. Can I change that into a SELECT somehow?
This is what I've come up with but it runs through fine on both databases without any results showing.
declare @sync_min_timestamp BigInt = 34815228
declare @sync_scope_local_id Int = 10
declare @changeTable [Transfers_BulkType]
-- use a temp table to store the list of PKs that successfully got updated
declare @changed TABLE ([trn_pk] char(16), PRIMARY KEY ([trn_pk]));
-- update the base table
begin transaction
-- update the base table
MERGE [Transfers] AS base USING
-- join done here against the side table to get the local timestamp for concurrency check
(SELECT p.*, t.update_scope_local_id, t.scope_update_peer_key, t.local_update_peer_timestamp FROM @changeTable p LEFT JOIN [Transfers_tracking] t ON p.[trn_pk] = t.[trn_pk]) as changes ON changes.[trn_pk] = base.[trn_pk]
WHEN MATCHED AND (changes.update_scope_local_id = @sync_scope_local_id AND changes.scope_update_peer_key = changes.sync_update_peer_key) OR changes.local_update_peer_timestamp <= @sync_min_timestamp THEN
UPDATE SET [trn_itmfk] = changes.[trn_itmfk], [trn_date] = changes.[trn_date], [trn_brnfk] = changes.[trn_brnfk], [trn_qty] = changes.[trn_qty], [trn_brnfkto] = changes.[trn_brnfkto], [trn_qtyreceived] = changes.[trn_qtyreceived], [usr_fk] = changes.[usr_fk]
OUTPUT INSERTED.[trn_pk] into @changed; -- populates the temp table with successful PKs
SELECT * from @changed
/*
UPDATE side SET
update_scope_local_id = @sync_scope_local_id,
scope_update_peer_key = changes.sync_update_peer_key,
scope_update_peer_timestamp = changes.sync_update_peer_timestamp,
local_update_peer_key = 0
*/SELECT @sync_scope_local_id, changes.sync_update_peer_key, changes.sync_update_peer_timestamp, 0
FROM
[Transfers_tracking] side JOIN
(SELECT p.[trn_pk], p.sync_update_peer_timestamp, p.sync_update_peer_key, p.sync_create_peer_key, p.sync_create_peer_timestamp FROM @changed t JOIN @changeTable p ON p.[trn_pk] = t.[trn_pk]) as changes ON changes.[trn_pk] = side.[trn_pk]
SELECT [trn_pk] FROM @changeTable t WHERE NOT EXISTS (SELECT [trn_pk] from @changed i WHERE t.[trn_pk] = i.[trn_pk])
--END
rollback
August 26, 2023 at 6:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply