View Issue Details

IDProjectCategoryView StatusLast Update
0004726SymmetricDSBugpublic2024-07-31 17:21
ReporterJaiGanesh S Assigned To 
Priorityurgent 
Status closedResolutionunable to reproduce 
Product Version3.12.5 
Summary0004726: SQL Server Trigger NoCount On is working when inserting large number of records in a transaction
DescriptionHi,

We are getting the following error when inserting large number of records in a transaction.

A trigger returned a resultset and/or was running with SET NOCOUNT OFF while another outstanding result set was active.

While inserting 20 records in a transaction, we dont get the above error and everything works fine. But while inserting hundreds of records in a transaction, we get the sql trigger error. This is a blocker for us to use the symmertic ds.
Tagsdialect: sql-server, trigger

Activities

JaiGanesh S

2020-12-25 15:01

reporter   ~0001872

I tried updating the trigger by moving the set nocount on statement to the first line of the trigger, then it seems to work.

josh-a-hicks

2021-01-06 18:31

developer   ~0001877

It should not matter if it's the first line or not. Did you make any other adjustments?

elong

2021-01-06 18:32

developer   ~0001878

Trigger text looks like below. We save the nocount setting, then set nocount on. That way we can restore the nocount setting at the end.

declare @NCT int
set @NCT = @@OPTIONS & 512
set nocount on

JaiGanesh S

2021-01-21 06:50

reporter   ~0001881

I tried replicating this issue through direct sql scripts. But I could not. This issue happens only when I do through the application while updating in bulk.
But the issue seems to disapper if I move the set nocount on as the first statement of the trigger.
Is this something customizable?

josh-a-hicks

2021-01-27 18:26

developer   ~0001883

Can you try modifying trigger text to see if this will work instead?

declare @NCT int
set @NCT = @@OPTIONS & 512
if (@NCT = 0) set nocount on

JaiGanesh S

2021-03-25 11:56

reporter   ~0001906

It only works when we have set nocount on as the first line in the trigger.

Issue History

Date Modified Username Field Change
2020-12-25 15:00 JaiGanesh S New Issue
2020-12-25 15:00 JaiGanesh S Tag Attached: dialect: sql-server
2020-12-25 15:00 JaiGanesh S Tag Attached: trigger
2020-12-25 15:01 JaiGanesh S Note Added: 0001872
2021-01-06 18:31 josh-a-hicks Note Added: 0001877
2021-01-06 18:32 elong Note Added: 0001878
2021-01-21 06:50 JaiGanesh S Note Added: 0001881
2021-01-27 18:26 josh-a-hicks Note Added: 0001883
2021-03-25 11:56 JaiGanesh S Note Added: 0001906
2024-07-31 17:21 jvanmeter Status new => closed
2024-07-31 17:21 jvanmeter Resolution open => unable to reproduce