SQL Trigger error on Modify: SET NOCOUNT ON

rcverbeek

Hi,

We are migrating our Dynamics NAV to version 2013R2. The database is partly replicated to another database on a webserver.

One custom table in particular is giving us problems. This one in also the only one that is part of a MERGE replication.

What happens is as follows:

• In a webapplication a record is inserted into this table
• When the replication agent completes, the record can be viewed in Dynamics NAV
• When we MODIFY the record, sometimes the following error is generated:

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

It strikes me that in SQL profiler the MODIFY statement translates to:

declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,N'@0 int,@1 nvarchar(20),@2 int,@3 binary(8)',N'UPDATE "Software2013R2_Test".dbo."Software Nar NAV7_2$Web Job Journal Line" SET "Invoice Type"=@0,"Invoice No_"=@1 WHERE ("Identification No_"=@2 AND "timestamp"=@3) SELECT @@DBTS',@0=1,@1=N'P12-18',@2=10854,@3=0x00000000000F2445
select @p1

@DBTS and the second the returned handle no.

Some further hints to our process and problem:

• The MODIFY statement is guaranteed to update only one record. We retrieve it with a GET. The primary key is btw "Identification No_"
• The MODIFY statement not always fails
• SQL added 3 MERGE triggers to this table, but all of them use the SET NOCOUNT ON. No other triggers are defined.

I have two actual questions:
Has anyone had a similar error message and if so how did you resolve it?

A rather technical question: why does the MODIFY statement result to a SQL statement that returns two sets?

If you need any further information, please let me know.

Thank you in advance!

Best regards Roeland

rcverbeek

The MODIFY statement was part of a repeat until loop. Adding a simple COMMIT after each MODIFY seemed to have resolved the issue.

rcverbeek

The MODIFY statement was part of a repeat until loop. Adding a simple COMMIT after each MODIFY seemed to have resolved the issue.