Options

SQL Trigger error on Modify: SET NOCOUNT ON

rcverbeekrcverbeek Member Posts: 32
edited 2014-05-02 in NAV Three Tier
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

Best Answer

  • Options
    rcverbeekrcverbeek Member Posts: 32
    Answer ✓
    The MODIFY statement was part of a repeat until loop. Adding a simple COMMIT after each MODIFY seemed to have resolved the issue.

Answers

  • Options
    rcverbeekrcverbeek Member Posts: 32
    Answer ✓
    The MODIFY statement was part of a repeat until loop. Adding a simple COMMIT after each MODIFY seemed to have resolved the issue.
Sign In or Register to comment.