View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0004726 | SymmetricDS | Bug | public | 2020-12-25 15:00 | 2021-03-25 11:56 |
| Reporter | JaiGanesh S | Assigned To | |||
| Priority | urgent | ||||
| Status | new | Resolution | open | ||
| Product Version | 3.12.5 | ||||
| Summary | 0004726: SQL Server Trigger NoCount On is working when inserting large number of records in a transaction | ||||
| Description | Hi, 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. | ||||
| Tags | dialect: sql-server, trigger | ||||
|
|
I tried updating the trigger by moving the set nocount on statement to the first line of the trigger, then it seems to work. |
|
|
It should not matter if it's the first line or not. Did you make any other adjustments? |
|
|
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 |
|
|
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? |
|
|
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 |
|
|
It only works when we have set nocount on as the first line in the trigger. |
| 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 |