SQL DELETE STATEMENT RETURN WITH A SQL CODE -523 SQLSTATE 37000

• lagouws

  Old Hand

  Points: 308

  More actions

  June 14, 2012 at 3:10 am

  #263000

  Hi

  I am executing a stored procedure to do a delete on a table with a key, and the SQLCODE -523 is returned.

  The error message is : F.A trigger returned a resultset and/or was running with SET NOCOUNT OFF

  On the this table there is a trigger that inserts the deleted record into another table.

  If I drop the trigger the delete works ok and the return code is 0.

  Here is the code of stored procedure :

  create procedure [PKG:BOKXB1].[TESTDELtttvKMG0$0] (@1 varchar(20)) AS

  DELETE FROM [P62XB1].XB1PPM WHERE PMIDA = @1

  GO

  Here is the code for the trigger :

  CREATE TRIGGER P62XB1.JPPMAD1 ON P62XB1.XB1PPM

  AFTER DELETE AS

  BEGIN

  SET NOCOUNT ON

  INSERT INTO P62XB1.XL1TPM SELECT CAST(SYSDATETIME() AS DATETIME2(6)) ,

  'B' ,NEWID(), 'D' , PMIDA , PMSTA , PMIDI , PMSTI , PMIDEST,

  ORIGINAL_LOGIN() , '0001-01-01 00:00:00.000000' , ' ' FROM DELETED

  SET NOCOUNT OFF

  END

  Any ideas will be welcome.

  Thanks

• okbangas

  SSChampion

  Points: 11773

  More actions

  June 14, 2012 at 4:19 am

  #1500878

  As far as I understand the error message, remove the SET NOCOUNT OFF from the trigger.

  ---

  
  Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

  Concatenating Row Values in Transact-SQL[/url]