SQL Azure Data Sync not possible when a trigger is set

Question

I try to use SQL Azure Data Sync to sync between SQL-Azure-DB and on-premise SQL-Server 2008R2.

I have to sync only one table with now only 90 rows. The table on the Azure-side has a trigger set, but the table on my local sql-server doesn't have this trigger (because apart from the shared one table, the two databases have nothing in common).

When I try to sync it fails with this error:

Sync failed with the exception "GetStatus failed with exception:
Sync worker failed, checked by GetStatus method.

Failure details:
An unexpected error occurred when applying batch file C:\Resources\directory\61e9d741c80a47b4ae195f835e62fcda.NTierSyncServiceWorkerRole.LS1\DSS_syncjobmxd24sznwfq5idekfaopaery\a0e8b11a-a08c-4081-b929-e3f80b70f045.batch.
See the inner exception for more details.

Inner exception:
Failed to execute the command 'BulkInsertCommand' for table 'dbo.tblUser';
the transaction was rolled back. Ensure that the command syntax is correct.

Inner exception:
SqlException Error Code: -2146232060 - SqlError Number:512,
Message: Subquery returned more than 1 value.
This is not permitted when the subquery follows =, !=, <, <= , >, >=
or when the subquery is used as an expression.

SqlError Number:3621,
Message: The statement has been terminated. 
For more information, provide tracing id ‘bb8e3787-27c1-4b7e-9a26-6db2ff6724d3’ to customer support.

When I disable my trigger, the sync works!

My trigger:

    CREATE TRIGGER [dbo].[UpdateUsersTable]
    ON [dbo].[tblUser]
    AFTER INSERT AS BEGIN SET NOCOUNT ON;

    INSERT INTO  [dbo].[Users] ([userID], [PartnerOrganizationId])
    VALUES ((select [userID] from inserted), (select [country] from inserted))
    END

As an approach I thought maybe it's because my trigger and the sync-trigger start in the wrong order, so I tried:

    exec sp_settriggerorder @triggername = 'tblUser_dss_insert_trigger',
                        @order = 'first',
                        @stmttype = 'insert',
                        @namespace = null

    exec sp_settriggerorder @triggername = 'UpdateUsersTable',
                    @order = 'last',
                    @stmttype = 'insert',
                    @namespace = null
    go

But that didn't change anything. Still the same error.

Do I have any chance to sync that table WITH my trigger enabled?

AvkashChauhan · Accepted Answer · 2012-05-21T18:55:37.000

1

In your Trigger you are using INSERT which is causing this problem.

Please create separate triggers for INSERT, UPDATE and DELETE. System do need to compare the row counts between inserted and deleted table to determine which action fires the trigger. Having separate triggers for INSERT/DELETE should solve this problem.

A similar discussion shows exact same problem here

More Info

Your trigger has a bug, and will fail for any multi-row insert. It should be something like:

CREATE TRIGGER [dbo].[UpdateUsersTable]
ON [dbo].[tblUser]
AFTER INSERT AS BEGIN SET NOCOUNT ON;

INSERT INTO  [dbo].[Users] ([userID], [PartnerOrganizationId])
 select [userID], [country] from inserted
END

edited May 21 '12 at 18:55

answered May 18 '12 at 17:51

AvkashChauhan

20,495
3
34
65

Now I set seperate triggers for INSERT, UPDATE and DELETE. But that didn't solve my problem. And I think that it neither was a sucessfull solution in your link, unfortunately. Think I'm gonna try some way to go without triggers. Or do you have some more ideas? – Géza May 21 '12 at 13:31
Sorry if the suggestion did not work. Let me find some more info for you.. stay tuned – AvkashChauhan May 21 '12 at 17:40
1

I was told that your trigger has a bug, and will fail for any multi-row insert. please check above "More Info" section and try. – AvkashChauhan May 21 '12 at 18:53
Man, you saved my day!! After eliminating the bug the sync is just working perfect - with no need to define seperate triggers for INSERT, UPDATE and DELETE. Thanks a lot for not giving up my issue! – Géza May 23 '12 at 13:49

SQL Azure Data Sync not possible when a trigger is set

1 Answers1