Devart Forums

Posted: **Tue 28 May 2013 09:44**

Hi,

Following up on a question I posted months ago ... I created a stored procedure which checks a few things before performing some database actions. If the checks fail ... the stored procedure raises an error with RAISEERROR which I was hoping to catch in my Delphi code. The StoredProcedure looks like this :

Code: Select all

-- =============================================
-- Author:		sLesage
-- Create date: 20121106
-- Description:	Deletes a dagtaak, its details and maybe also the Opdracht
-- =============================================
ALTER PROCEDURE [dbo].[P_DELETE_DAGTAAK]
  -- Add the parameters for the stored procedure here
  @DagtaakId INT
, @Message   NVARCHAR(MAX) OUTPUT
AS
BEGIN
  DECLARE @OpdrachtId INT
  DECLARE @ArtikelId INT
  DECLARE @Omschrijving NVARCHAR(MAX)
  DECLARE @Aantal udtQuantity
  DECLARE @ReturnCode INT = 0
  DECLARE @TranCounter INT = 0

  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;
  SET @TranCounter = @@trancount

  /* First check rerequisites for all DagtaakDetail lines */
  DECLARE @DagtaakDetailId INT;

  DECLARE crDagtaakDetails CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT TDD.DagtaakDetailId
         , TDD.ArtikelId
         , am.omschr_intern
         , TDD.AantalToDo
      FROM T_DAGTAAK_DETAIL TDD
          JOIN Artikel_magazijn am
            ON TDD.ArtikelId = am.Id
      WHERE TDD.DagtaakId = @DagtaakId

  BEGIN TRY

    IF (@TranCounter > 0)
    BEGIN
      SAVE TRANSACTION ProcedureSavePoint
    END
    ELSE
    BEGIN
      BEGIN TRANSACTION
      END

      /* First check all PreRequisites for Dagtaak Detail */

      OPEN crDagtaakDetails;

      FETCH NEXT FROM crDagtaakDetails INTO @DagtaakDetailId, @ArtikelId, @Omschrijving, @Aantal;

      WHILE (@@FETCH_STATUS = 0) AND (@ReturnCode = 0)
      BEGIN
        EXEC @ReturnCode = P_DELETE_DAGTAAK_DETAIL @DagtaakDetailId = @DagtaakDetailId
                                                 , @Message = @Message OUTPUT

        IF (@ReturnCode <> 0)
        BEGIN
          SET @Message = 'Het wissen van dagtaak ' + CONVERT( VARCHAR(20), @DagtaakId ) + ' faalde! ' + CHAR( 13 ) + CHAR( 10 ) + CHAR( 13 ) + CHAR( 10 ) +
          'Kon volgende detail lijn niet wissen : ' + CHAR( 13 ) + CHAR( 10 ) + CHAR( 13 ) + CHAR( 10 ) +
          'DagtaakDetailId : ' + CONVERT( VARCHAR(20), @DagtaakDetailId ) + CHAR( 13 ) + CHAR( 10 ) +
          'Artikel Id      : ' + CONVERT( VARCHAR(20), @ArtikelId ) + CHAR( 13 ) + CHAR( 10 ) +
          'Omschrijving    : ' + +@Omschrijving + CHAR( 13 ) + CHAR( 10 ) +
          'Aantal          : ' + CONVERT( VARCHAR(20), @Aantal ) + CHAR( 13 ) + CHAR( 10 ) +
          CHAR( 13 ) + CHAR( 10 ) +
          @Message
        END

        FETCH NEXT FROM crDagtaakDetails INTO @DagtaakDetailId, @ArtikelId, @Omschrijving, @Aantal;
      END

      CLOSE crDagtaakDetails;
      DEALLOCATE crDagtaakDetails;

      IF (@ReturnCode <> 0)
      BEGIN
        RAISERROR (@Message, 16, 2)
      END

      /* If we got here we can delete the DagtaakDetail Lines without any problems so we should check prerequisites for deleting the Dagtaak Itself */
      EXEC @ReturnCode = P_DELETE_DAGTAAK_PREREQ @DagtaakId = @DagtaakId
                                               , @Message = @Message OUTPUT

      IF (@ReturnCode <> 0)
      BEGIN
        SET @Message = 'Het wissen van dagtaak ' + CONVERT( VARCHAR(20), @DagtaakId ) + ' faalde ' + @Message
        RAISERROR (@Message, 16, 2)
      END

      DELETE FROM T_OPDRACHT
        WHERE DagtaakId = @DagtaakId
      DELETE FROM T_DAGTAAK
        WHERE DagtaakId = @DagtaakId

      IF (@TranCounter = 0)
      BEGIN
      COMMIT TRANSACTION
    END

  END TRY
  BEGIN CATCH

    IF (@TranCounter = 0)
    BEGIN
      ROLLBACK TRANSACTION
    END
    ELSE
    BEGIN

      IF (XACT_STATE( ) <> -1)
      BEGIN
        ROLLBACK TRANSACTION ProcedureSavePoint
      END

    END

    -- After the appropriate rollback, echo error
    -- information to the caller.
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE( );
    SELECT @ErrorSeverity = ERROR_SEVERITY( );
    SELECT @ErrorState = ERROR_STATE( );

    --PRINT 'Error ' + CONVERT( VARCHAR(50), ERROR_NUMBER( ) ) +
    --', Severity ' + CONVERT( VARCHAR(5), ERROR_SEVERITY( ) ) +
    --', State ' + CONVERT( VARCHAR(5), ERROR_STATE( ) ) +
    --', Line ' + CONVERT( VARCHAR(5), ERROR_LINE( ) )
    --PRINT ERROR_MESSAGE( );

    RAISERROR (@ErrorMessage, -- Message text.
    @ErrorSeverity, -- Severity.
    @ErrorState -- State.
    );

    RETURN @ReturnCode
  END CATCH;
END

Now ... I have a TMStoredProc which I would like to use to execute that piece of code, and I wrote a little routine for it :

Code: Select all

procedure TdtmDagtaak.DeleteDagtaak(const aDagtaakId: Integer);
var
  aMessage : String;
begin
  stpDeleteDagtaak.ParamByName( 'DagtaakId' ).AsInteger := aDagtaakId;
  stpDeleteDagtaak.ExecProc;

  if ( stpDeleteDagtaak.ParamByName( 'RETURN_VALUE' ).AsInteger <> 0 ) then
  begin
    aMessage := stpDeleteDagtaak.ParamByName( 'Message' ).AsString;
    raise EWMSDatabaseError.Create(aMessage);
  end;
end;

But when executing the stored procedure it does not raise a Delphi exception when a RAISEERROR occurs in the code within the Stored Procedure. Hence me picking up the message and raising my own exception. I was kinda hoping it would end up throwing an exception in Delphi ... but somehow it doesn't.

This is pretty strange, because If I call that same stored procedure in the SQLDelete property of a query :

Code: Select all

EXEC P_DELETE_DAGTAAK @DagtaakId = :Old_DagtaakId, @Message = ''

Then here it does perfectly raise a Delphi Exception with the message it received through the RAISEERROR from the database.

Is there any reason which it doens't behave the same way using a TMSStoredProc component ? Should I maybe change some settings or properties ?

It is very strange, since replacing the TMSStoredProc by a TMSQuery and using EXECSQL on that Query does result in a Delphi Exception.

Posted: **Tue 28 May 2013 13:04**

Hello,

SQL Server generates errors on the client only with severity level higher than 10. Please make sure you generate needed errors with severity level higher than 10. If you do but the error does not occur on the client, please specify the following:
- the exact version of SDAC. You can learn it from the About sheet of TMSConnection Editor;
- the exact version of your IDE;
- the exact version of SQL Server server and client. You can learn it from the Info sheet of TMSConnection Editor.

Posted: **Tue 28 May 2013 13:08**

Hi,

We are raising errors with severity 16 :

Code: Select all

        RAISERROR (@Message, 16, 2)

The errors do generate Delphi Exceptions when using your TMSQuery component, but not when using TMSStoredProc component. Additional info :

Delphi XE2 Version 16.0.4504.48759 (Update 4 Hotfix1)
SDAC 6.6.12
Microsoft SQL Server: 10.50.2500
Microsoft OLE DB Provider for SQL Server: 06.01.7601

Posted: **Tue 28 May 2013 13:46**

I cannot reproduce the problem. Please specify the script to create all server objects that are used in the P_DELETE_DAGTAAK stored procedure (udtQuantity, P_DELETE_DAGTAAK_DETAIL, P_DELETE_DAGTAAK_PREREQ, etc.).

Posted: **Tue 28 May 2013 15:32**

Meanwhile I'm back home with no access to the database ... but I think you could simply go ahead and create a stored procedure with one command :

RAISERROR ( "Raising an error", 16, 2)

I will see if I can reproduce this here at home too.

Posted: **Tue 28 May 2013 15:57**

Hi again,

I couldn't simulate it with a simple RAISEERROR in my stored procedure, but I did it using this Stored Procedure :

Code: Select all

-- =============================================
-- Author:      sLesage
-- Create date: 20121106
-- Description:   Deletes a dagtaak, its details and maybe also the Opdracht
-- =============================================
ALTER PROCEDURE [dbo].[P_DELETE_DAGTAAK]-- Add the parameters for the stored procedure here
  @DagtaakId INT,
  @Message   NVARCHAR(MAX)    OUTPUT
AS
BEGIN
  DECLARE @TranCounter INT
  DECLARE @ReturnCode  INT = 0
  
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;
  SET @TranCounter = @@trancount

  /* First check rerequisites for all DagtaakDetail lines */

  BEGIN TRY

    IF (@TranCounter > 0)
    BEGIN
      SAVE TRANSACTION ProcedureSavePoint
    END
    ELSE
    BEGIN BEGIN TRANSACTION
    END
    
    SET @ReturnCode = -1
    
    IF ( @ReturnCode <> 0 )
    BEGIN
      SET @Message = 'RaiseError from SQL Server'
      RAISERROR (@Message, 16, 2)
    END
    
    BEGIN
      COMMIT TRANSACTION
    END

  END TRY
  BEGIN CATCH

    IF (@TranCounter = 0)
    BEGIN
      ROLLBACK TRANSACTION
    END
    ELSE
    BEGIN

      IF (xact_state() <> -1)
      BEGIN
        ROLLBACK TRANSACTION ProcedureSavePoint
      END

    END

    -- After the appropriate rollback, echo error
    -- information to the caller.
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = error_message();
    SELECT @ErrorSeverity = error_severity();
    SELECT @ErrorState = error_state();

    --PRINT 'Error ' + CONVERT( VARCHAR(50), ERROR_NUMBER( ) ) +
    --', Severity ' + CONVERT( VARCHAR(5), ERROR_SEVERITY( ) ) +
    --', State ' + CONVERT( VARCHAR(5), ERROR_STATE( ) ) +
    --', Line ' + CONVERT( VARCHAR(5), ERROR_LINE( ) )
    --PRINT ERROR_MESSAGE( );

    RAISERROR (@ErrorMessage, -- Message text.
    @ErrorSeverity, -- Severity.
    @ErrorState -- State.
    );

    RETURN @ReturnCode
  END CATCH;
END

And then created an empty project with an MSConnection / MSQuery / MSStoredProc :

Code: Select all

unit Unit1;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls, MSAccess, Data.DB, MemDS,
  DBAccess;

type
  TForm1 = class(TForm)
    con1: TMSConnection;
    qry1: TMSQuery;
    stp1: TMSStoredProc;
    btn1: TButton;
    btn2: TButton;
    procedure btn1Click(Sender: TObject);
    procedure btn2Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.btn1Click(Sender: TObject);
begin
  stp1.ParamByName( 'DagtaakId' ).AsInteger := 0;
  stp1.ExecProc;
end;

procedure TForm1.btn2Click(Sender: TObject);
begin
  qry1.ParamByName( 'DagtaakId' ).AsInteger := 0;
//  qry1.ParamByName( 'DagtaakId' ).AsInteger := 0;
  qry1.ExecSQL;
end;

end.

Code: Select all

object Form1: TForm1
  Left = 0
  Top = 0
  Caption = 'Form1'
  ClientHeight = 389
  ClientWidth = 693
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object btn1: TButton
    Left = 40
    Top = 120
    Width = 123
    Height = 25
    Caption = 'Stored Procedure'
    TabOrder = 0
    OnClick = btn1Click
  end
  object btn2: TButton
    Left = 248
    Top = 120
    Width = 75
    Height = 25
    Caption = 'Query'
    TabOrder = 1
    OnClick = btn2Click
  end
  object con1: TMSConnection
    Database = 'BOSTOEN_WMS_DEV'
    Username = 'stefaan.lesage'
    Server = '(local)'
    Connected = True
    LoginPrompt = False
    Left = 88
    Top = 24
  end
  object qry1: TMSQuery
    Connection = con1
    SQL.Strings = (
      
        'EXEC P_DELETE_DAGTAAK @DagtaakId = :DagtaakId, @Message = :Messa' +
        'ge')
    Left = 288
    Top = 24
    ParamData = <
      item
        DataType = ftInteger
        Name = 'DagtaakId'
        ParamType = ptInput
      end
      item
        DataType = ftWideString
        Name = 'Message'
        ParamType = ptInputOutput
      end>
  end
  object stp1: TMSStoredProc
    StoredProcName = 'P_DELETE_DAGTAAK;1'
    Connection = con1
    SQL.Strings = (
      '{:RETURN_VALUE = CALL P_DELETE_DAGTAAK;1 (:DagtaakId, :Message)}')
    Left = 176
    Top = 24
    ParamData = <
      item
        DataType = ftInteger
        Name = 'RETURN_VALUE'
        ParamType = ptResult
      end
      item
        DataType = ftInteger
        Name = 'DagtaakId'
        ParamType = ptInput
      end
      item
        DataType = ftWideMemo
        Name = 'Message'
        ParamType = ptInputOutput
        Value = ''
      end>
    CommandStoredProcName = 'P_DELETE_DAGTAAK;1'
  end
end

Clicking the StoredProcedure button doesn't trigger the Delphi Exception, clicking the Query one does. This should allow you to simulate the same thing.

Posted: **Wed 29 May 2013 08:24**

The point is that SQL Server does not support output parameters which are of stream types. You can read about this at http://msdn.microsoft.com/en-us/library ... .105).aspx . Here is a quote from this article:

Variables that are initialized or updated to null, DateTime, or stream types cannot be used as output parameters.

NVARCHAR(MAX) is considered as a stream type. We cannot influence such SQL Server behaviour.
To avoid the problem, you can use one of the two ways:
- set the DataType property of the Message parameter to ftWideString:

Code: Select all

stp1.ParamByName('Message').DataType := ftWideString;

- set the ParamType property of the Message parameter to ptInput:

Code: Select all

stp1.ParamByName('Message').ParamType := ptInput;

Posted: **Wed 29 May 2013 11:23**

Hi,

Thanks for checking it out ... Simply changing that parameter to NVARCHAR( 4000 ) solved the problem too. But since we're now able to raise the error in SQL and make that trigger en exception in Delphi we probably won't be needing that Message parameter anymore.

Thanks for the feedback...

Posted: **Wed 29 May 2013 11:55**

I am glad to help. If any other questions come up, please contact us.

Posted: **Thu 30 May 2013 12:33**

Hi again Andrey,

I have changed everything as requested and I'm having another issue at the moment. This is my TMSQuery object again :

Code: Select all

object qryList: TMSQuery
  SQLDelete.Strings = (
    'EXEC P_DELETE_DAGTAAK @DagtaakId = :Old_DagtaakId, @Message = '#39#39)
  Connection = dtmMain.conWMS
  SQL.Strings = (
    'SELECT'
    '  DagtaakId'
    ' ,KlantNr'
    ' ,SupportId'
    ' ,StatusId'
    ' ,DatumUitvoering'
    ' ,Info'
    ' ,Referentie'
    ' ,DagtaakTypeId'
    ' ,DoelMagazijnId'
    ' ,MedewerkerId'
    ' ,PloegId'
    ' ,MagazijnGroepId'
    ' ,AangemaaktOp'
    ' ,AangemaaktDoor'
    ' ,AangepastOp'
    ' ,AangepastDoor'
    ' ,DatumShipping'
    '  FROM T_DAGTAAK '
    '&WHERECLAUSE'
    '&ORDERBYCLAUSE')
  RefreshOptions = [roAfterInsert, roAfterUpdate]
  BeforePost = qryListBeforePost
  AfterPost = qryListAfterPost
  AfterCancel = qryListAfterCancel
  OnNewRecord = qryListNewRecord
  Left = 40
  Top = 24
  MacroData = <
    item
      Name = 'WHERECLAUSE'
    end
    item
      Name = 'ORDERBYCLAUSE'
      Value = 'ORDER BY KlantNr, StatusId, AangemaaktOp DESC, DagtaakId DESC'
    end>
  object fldListDagtaakId: TIntegerField
    AutoGenerateValue = arAutoInc
    FieldName = 'DagtaakId'
    ReadOnly = True
  end
  object fldListKlantNr: TIntegerField
    DisplayLabel = 'Klant Nr'
    FieldName = 'KlantNr'
    OnValidate = fldListKlantNrValidate
  end
  object fldListSupportId: TIntegerField
    DisplayLabel = 'Support Id'
    FieldName = 'SupportId'
  end
  object fldListStatusId: TIntegerField
    FieldName = 'StatusId'
  end
  object fldListInfo: TWideMemoField
    FieldName = 'Info'
    BlobType = ftWideMemo
  end
  object fldListLookupStatus: TStringField
    DisplayLabel = 'Status'
    FieldKind = fkLookup
    FieldName = 'LookupStatus'
    LookupDataSet = dtmLookup.qryStatusDagtaak
    LookupKeyFields = 'StatusId'
    LookupResultField = 'Status'
    KeyFields = 'StatusId'
    Size = 50
    Lookup = True
  end
  object fldListDatumUitvoering: TDateTimeField
    DisplayLabel = 'Uitvoeren op'
    FieldName = 'DatumUitvoering'
    Required = True
  end
  object fldListReferentie: TWideStringField
    FieldName = 'Referentie'
    Size = 50
  end
  object fldListDagtaakTypeId: TIntegerField
    Alignment = taLeftJustify
    FieldName = 'DagtaakTypeId'
    OnValidate = fldListDagtaakTypeIdValidate
  end
  object fldListDoelMagazijnId: TIntegerField
    FieldName = 'DoelMagazijnId'
  end
  object fldListMedewerkerId: TIntegerField
    FieldName = 'MedewerkerId'
    OnValidate = fldListMedewerkerIdValidate
  end
  object fldListPloegId: TIntegerField
    FieldName = 'PloegId'
    OnValidate = fldListPloegIdValidate
  end
  object fldListLookupMedewerker: TStringField
    FieldKind = fkLookup
    FieldName = 'LookupMedewerker'
    LookupDataSet = dtmLookup.qryWerknemers
    LookupKeyFields = 'MedewerkerId'
    LookupResultField = 'FullName'
    KeyFields = 'MedewerkerId'
    Size = 150
    Lookup = True
  end
  object fldListLookupDoelMagazijn: TStringField
    FieldKind = fkLookup
    FieldName = 'LookupDoelMagazijn'
    LookupDataSet = dtmLookup.qryMagazijnen
    LookupKeyFields = 'MagazijnId'
    LookupResultField = 'Omschrijving'
    KeyFields = 'DoelMagazijnId'
    Size = 50
    Lookup = True
  end
  object fldListLookupPloeg: TStringField
    FieldKind = fkLookup
    FieldName = 'LookupPloeg'
    LookupDataSet = dtmLookup.qryPloeg
    LookupKeyFields = 'PloegId'
    LookupResultField = 'Ploeg'
    KeyFields = 'PloegId'
    Size = 50
    Lookup = True
  end
  object fldListMagazijnGroepId: TIntegerField
    FieldName = 'MagazijnGroepId'
    OnValidate = fldListMagazijnGroepIdValidate
  end
  object fldListLookupMagazijnGroep: TStringField
    FieldKind = fkLookup
    FieldName = 'LookupMagazijnGroep'
    LookupDataSet = dtmLookup.qryMagazijnGroepen
    LookupKeyFields = 'MagazijnGroepId'
    LookupResultField = 'MagazijnGroep'
    KeyFields = 'MagazijnGroepId'
    Size = 50
    Lookup = True
  end
  object fldListAangemaaktOp: TDateTimeField
    FieldName = 'AangemaaktOp'
  end
  object fldListAangemaaktDoor: TWideStringField
    FieldName = 'AangemaaktDoor'
    Size = 256
  end
  object fldListAangepastOp: TDateTimeField
    FieldName = 'AangepastOp'
  end
  object fldListAangepastDoor: TWideStringField
    FieldName = 'AangepastDoor'
    Size = 256
  end
  object fldListDatumShipping: TDateTimeField
    DisplayLabel = 'Verscheept Op'
    FieldName = 'DatumShipping'
  end
  object fldListLookupDagtaakType: TStringField
    DisplayLabel = 'Type Dagtaak'
    FieldKind = fkLookup
    FieldName = 'LookupDagtaakType'
    LookupDataSet = dtmLookup.qryDagtaakType
    LookupKeyFields = 'DagtaakTypeId'
    LookupResultField = 'Omschrijving'
    KeyFields = 'DagtaakTypeId'
    Size = 255
    Lookup = True
  end
end

As you can see I have added the SQLDelete statement which will execute the Stored Procedure. My stored procedure raises an exception if something is not OK and deletes the necessary records if everything is fine. But now my Delphi application is raising an EDatabaseError with message : "Update failed: Found 0 records"

Call Stack :

Data.DB.DatabaseError('Update failed. Found 0 records',???)
:006a5505 TDADataSetUpdater.PerformSQL + $8A9
:006e4ddf TCustomMSDataSetUpdater.PerformSQL + $27
:006a60f4 TDADataSetUpdater.PerformUpdateDelete + $148
:006a621a TDADataSetUpdater.PerformDelete + $A
:0061095c TMemDataSet.DoPerformDelete + $18
:0069aaa7 TCustomDADataSet.InternalDelete + $1B
Data.DB.TDataSet.Delete

The record gets deleted from the database, but I think the error is happening when SDAC is trying to refresh the record or something. Is there any easy way to fix this ? I have already tried setting the FullRefresh to TRUE and the DMLRefresh to true but nothing changed. I have also tried by removing the MESSAGE parameter from the Stored Procedure ... same results.

I can fix it by using a TDataSetDelete action and make it do something else instead :

Code: Select all

procedure TdtmDagtaak.acDataSetDeleteExecute(Sender: TObject);
const
  cDeleteConfirmation = 'Weet u zeker dat u het geselecteerde dagtaak wenst te wissen ?';
begin
  if ( MessageDlg( cDeleteConfirmation, mtConfirmation,
                   [ mbYes, mbNo ], 0 ) = mrYes ) then
  begin
    stpDeleteDagtaak.ParamByName( 'DagtaakId' ).AsInteger := aDagtaakId;
    stpDeleteDagtaak.ExecProc;
    qryList.Refresh;
  end;
end;

But this means I have added some code which I will have to maintain in the future too. So I was hoping I could remove that code by using the SQLDelete instead.

Would you have any idea on why I am getting this error and how I could fix it ?

Regards,

Stefaan

Posted: **Fri 31 May 2013 07:12**

The 'Update failed. N records found' error occurs if the number of updated or deleted records is not equal to 1. This problem usually occurs when a table has no Primary Key. Please check that your table has Primary Key.
Also, this error can occur if you have a trigger that influences the number of updated or deleted records. To solve the problem in this case, you should add the 'SET NOCOUNT ON;' line to the beginning of the trigger code. The same can be done in your stored procedure if it influences the number of affected rows.

Posted: **Mon 03 Jun 2013 12:42**

Hi Andrej,

I have just double checked everything ... The tables in Question do have a primary key and all triggers do start with the SET NOCOUNT ON; statement.

Not sure what is causing this, but maybe it's related to a master / detail releationship or maybe even a Refresh opiton ?

Posted: **Mon 03 Jun 2013 13:39**

Please try creating a small sample to demonstrate the problem and send it to andreyz*devart*com, including the script to create all needed server objects. Without this sample I will not be able to investigate the problem and give you a valid answer.

Posted: **Mon 03 Jun 2013 13:43**

Hi,

Just have done some more testing ... in our case this error appears when we DO use a SET NOCOUNT ON at the beginning of the stored procedure. It does NOT happen if we don't use that. I have a very isolated test project which shows you the problem :

Creating the SQL Server Objects :

Code: Select all

/****** Object:  Table [dbo].[T_TEST]    Script Date: 06/03/2013 15:37:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[T_TEST](
	[TestId] [int] IDENTITY(1,1) NOT NULL,
	[TestNaam] [nvarchar](50) NULL,
	[AangemaaktOp] [datetime] NULL,
	[AangemaaktDoor] [nvarchar](256) NULL,
	[AangepastOp] [datetime] NULL,
	[AangepastDoor] [nvarchar](256) NULL,
 CONSTRAINT [PK_T_TEST] PRIMARY KEY CLUSTERED 
(
	[TestId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[P_DELETE_TEST_PREREQ]    Script Date: 06/03/2013 15:37:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Date 2013-04-30 13:50:54
-- Database 'BOSTOEN_WMS_DEV'

CREATE PROCEDURE [dbo].[P_DELETE_TEST_PREREQ]
(
  @TestId INT
, @Message   NVARCHAR(4000) OUTPUT
)
AS
BEGIN
  SET NOCOUNT ON

  --IF (EXISTS (SELECT *
  --      FROM T_DAGTAAK
  --      WHERE DagtaakId = @DagtaakId
  --        AND StatusId >= 8)
  --  )
  --BEGIN
  --  SET @Message = 'Kan de dagtaak ' + CONVERT( VARCHAR( 10 ), @DagtaakId ) + ' niet verwijderen omdat ze reeds volledig werd gepicked'
  --  RETURN -1
  --END

  RETURN 0
END
GO

/****** Object:  Trigger [dbo].[TR_TEST_AANGEPAST]    Script Date: 06/03/2013 15:38:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE TRIGGER [dbo].[TR_TEST_AANGEPAST]
ON [dbo].[T_TEST]
AFTER UPDATE
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  -- Insert statements for trigger here
  UPDATE T_TEST
    SET AangepastOp   = GETDATE( )
      , AangepastDoor = SUSER_SNAME( )
  FROM T_TEST
  JOIN INSERTED
    ON INSERTED.TestId = T_TEST.TestId
  JOIN deleted
    ON deleted.TestId = T_TEST.TestId;

END

GO

/****** Object:  StoredProcedure [dbo].[P_DELETE_TEST]    Script Date: 06/03/2013 15:37:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[P_DELETE_TEST]
(
  @TestID  INT
 ,@Message NVARCHAR( 4000 ) OUTPUT
)
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  DECLARE @ReturnCode INT = 0
  DECLARE @TranCounter INT = 0
  --DECLARE @Message NVARCHAR(4000)


  /* If we got here we can delete the DagtaakDetail Lines without any problems so we should check prerequisites for deleting the Dagtaak Itself */
  EXEC @ReturnCode = P_DELETE_TEST_PREREQ @TestID = @TestId
                                        , @Message = @Message 

  IF (@ReturnCode <> 0)
  BEGIN
    RAISERROR (@Message, 16, 2)
    RETURN @ReturnCode
  END

  DELETE FROM T_TEST
    WHERE TestId = @TestID
    
  RETURN 0
END
GO
/****** Object:  Default [DF_T_TEST_AangemaaktOp]    Script Date: 06/03/2013 15:37:54 ******/
ALTER TABLE [dbo].[T_TEST] ADD  CONSTRAINT [DF_T_TEST_AangemaaktOp]  DEFAULT (getdate()) FOR [AangemaaktOp]
GO
/****** Object:  Default [DF_T_TEST_AangemaaktDoor]    Script Date: 06/03/2013 15:37:54 ******/
ALTER TABLE [dbo].[T_TEST] ADD  CONSTRAINT [DF_T_TEST_AangemaaktDoor]  DEFAULT (suser_sname()) FOR [AangemaaktDoor]
GO

Populating the table with test data :

Code: Select all

TRUNCATE TABLE T_TEST

DECLARE @Qty INT

SET @Qty = 1

WHILE @Qty < 50
BEGIN
  INSERT INTO T_TEST
      ( TestNaam )
    VALUES
      ( 'Test ' + CONVERT( VARCHAR(10), @Qty ) )
  SET @Qty = @Qty + 1
END

SELECT * FROM T_TEST

Then create a new VCL Application with the following :

Code: Select all

object Form1: TForm1
  Left = 0
  Top = 0
  Caption = 'Form1'
  ClientHeight = 300
  ClientWidth = 876
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object dbgrd1: TDBGrid
    Left = 192
    Top = 0
    Width = 684
    Height = 275
    Align = alRight
    DataSource = src1
    TabOrder = 0
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'Tahoma'
    TitleFont.Style = []
  end
  object dbnvgr1: TDBNavigator
    Left = 0
    Top = 275
    Width = 876
    Height = 25
    DataSource = src1
    Align = alBottom
    Kind = dbnHorizontal
    TabOrder = 1
    ExplicitWidth = 635
  end
  object btnDelete: TButton
    Left = 8
    Top = 152
    Width = 75
    Height = 25
    Action = acDelete
    TabOrder = 2
  end
  object con1: TMSConnection
    Database = 'BOSTOEN_WMS_DEV'
    Username = 'stefaan.lesage'
    Server = 'BOS-SQL02'
    Connected = True
    LoginPrompt = False
    Left = 40
    Top = 16
  end
  object qryBestelling: TMSQuery
    SQLDelete.Strings = (
      'EXEC P_DELETE_TEST @TestId = :Old_TestId, @Message = '#39#39)
    Connection = con1
    SQL.Strings = (
      'SELECT [TestId]'
      '      ,[TestNaam]'
      '      ,[AangemaaktOp]'
      '      ,[AangemaaktDoor]'
      '      ,[AangepastOp]'
      '      ,[AangepastDoor]'
      '  FROM [T_TEST]')
    RefreshOptions = [roAfterInsert, roAfterUpdate]
    Active = True
    Left = 120
    Top = 16
    object fldBestellingTestId: TIntegerField
      AutoGenerateValue = arAutoInc
      DisplayWidth = 12
      FieldName = 'TestId'
      ReadOnly = True
    end
    object fldBestellingTestNaam: TWideStringField
      DisplayWidth = 24
      FieldName = 'TestNaam'
      Size = 50
    end
    object fldBestellingAangemaaktOp: TDateTimeField
      DisplayWidth = 22
      FieldName = 'AangemaaktOp'
    end
    object fldBestellingAangemaaktDoor: TWideStringField
      DisplayWidth = 20
      FieldName = 'AangemaaktDoor'
      Size = 256
    end
    object fldBestellingAangepastOp: TDateTimeField
      DisplayWidth = 22
      FieldName = 'AangepastOp'
    end
    object fldBestellingAangepastDoor: TWideStringField
      DisplayWidth = 307
      FieldName = 'AangepastDoor'
      Size = 256
    end
  end
  object src1: TDataSource
    DataSet = qryBestelling
    Left = 120
    Top = 72
  end
  object al1: TActionList
    Left = 40
    Top = 72
    object acFirst: TDataSetFirst
      Category = 'Dataset'
      Caption = '&First'
      Hint = 'First'
      ImageIndex = 0
      DataSource = src1
    end
    object acPrior: TDataSetPrior
      Category = 'Dataset'
      Caption = '&Prior'
      Hint = 'Prior'
      ImageIndex = 1
      DataSource = src1
    end
    object acNext: TDataSetNext
      Category = 'Dataset'
      Caption = '&Next'
      Hint = 'Next'
      ImageIndex = 2
      DataSource = src1
    end
    object acLast: TDataSetLast
      Category = 'Dataset'
      Caption = '&Last'
      Hint = 'Last'
      ImageIndex = 3
      DataSource = src1
    end
    object acInsert: TDataSetInsert
      Category = 'Dataset'
      Caption = '&Insert'
      Hint = 'Insert'
      ImageIndex = 4
      DataSource = src1
    end
    object acDelete: TDataSetDelete
      Category = 'Dataset'
      Caption = '&Delete'
      Hint = 'Delete'
      ImageIndex = 5
      OnExecute = acDeleteExecute
      DataSource = src1
    end
    object ac7: TDataSetEdit
      Category = 'Dataset'
      Caption = '&Edit'
      Hint = 'Edit'
      ImageIndex = 6
    end
    object ac8: TDataSetPost
      Category = 'Dataset'
      Caption = 'P&ost'
      Hint = 'Post'
      ImageIndex = 7
    end
    object ac9: TDataSetCancel
      Category = 'Dataset'
      Caption = '&Cancel'
      Hint = 'Cancel'
      ImageIndex = 8
    end
    object ac10: TDataSetRefresh
      Category = 'Dataset'
      Caption = '&Refresh'
      Hint = 'Refresh'
      ImageIndex = 9
    end
  end
end

Code: Select all

unit untMain;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Data.DB, MemDS, DBAccess, MSAccess,
  Vcl.ExtCtrls, Vcl.DBCtrls, Vcl.Grids, Vcl.DBGrids, Vcl.StdCtrls, Vcl.DBActns,
  Vcl.ActnList;

type
  TForm1 = class(TForm)
    con1: TMSConnection;
    qryBestelling: TMSQuery;
    src1: TDataSource;
    dbgrd1: TDBGrid;
    dbnvgr1: TDBNavigator;
    fldBestellingTestId: TIntegerField;
    fldBestellingTestNaam: TWideStringField;
    al1: TActionList;
    acFirst: TDataSetFirst;
    acPrior: TDataSetPrior;
    acNext: TDataSetNext;
    acLast: TDataSetLast;
    acInsert: TDataSetInsert;
    acDelete: TDataSetDelete;
    ac7: TDataSetEdit;
    ac8: TDataSetPost;
    ac9: TDataSetCancel;
    ac10: TDataSetRefresh;
    btnDelete: TButton;
    fldBestellingAangemaaktOp: TDateTimeField;
    fldBestellingAangemaaktDoor: TWideStringField;
    fldBestellingAangepastOp: TDateTimeField;
    fldBestellingAangepastDoor: TWideStringField;
    procedure acDeleteExecute(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.acDeleteExecute(Sender: TObject);
begin
//  if ( Assigned( aDataSet ) ) then
//  begin
    if ( MessageDlg( 'Are you sure you want to delete?', mtConfirmation,
                     [ mbYes, mbNo ], 0 ) = mrYes ) then
    begin
      qryBestelling.Delete;
//    end;
  end;
end;

end.

If SET NOCOUNT ON is used in P_DELETE_TEST then deleting a record causes that error message about 'Update filed. Found 0 records'. Removing that statement doesn't result in that error message.

Regards,

Stefaan

Posted: **Tue 04 Jun 2013 08:13**

As I wrote earlier, the 'Update failed. N records found' error occurs if the number of updated or deleted records is not equal to 1. When you call the Delete method of the dataset, it means that you want to delete the current record only. Therefore, the number of deleted records must be 1. In your case, the P_DELETE_TEST stored procedure returns 0, which means that no records were deleted on the server. That is why, SDAC generates the error.
As you are using the code 'DELETE FROM T_TEST WHERE TestId = @TestID' where TestId is PRIMARY KEY, its execution will affect only one record. Therefore, to avoid the problem, you should remove the 'SET NOCOUNT ON;' code in the beginning of the P_DELETE_TEST stored procedure.

Devart Forums

Possible bug with TMSStoredProc and RAISEERROR / Exceptionsq

Possible bug with TMSStoredProc and RAISEERROR / Exceptionsq

Re: Possible bug with TMSStoredProc and RAISEERROR / Exceptionsq

Re: Possible bug with TMSStoredProc and RAISEERROR / Exceptionsq

Re: Possible bug with TMSStoredProc and RAISEERROR / Exceptionsq

Re: Possible bug with TMSStoredProc and RAISEERROR / Exceptionsq

Re: Possible bug with TMSStoredProc and RAISEERROR / Exceptionsq

Re: Possible bug with TMSStoredProc and RAISEERROR / Exceptionsq

Re: Possible bug with TMSStoredProc and RAISEERROR / Exceptionsq

Re: Possible bug with TMSStoredProc and RAISEERROR / Exceptionsq

Re: Possible bug with TMSStoredProc and RAISEERROR / Exceptionsq

Re: Possible bug with TMSStoredProc and RAISEERROR / Exceptionsq

Re: Possible bug with TMSStoredProc and RAISEERROR / Exceptionsq

Re: Possible bug with TMSStoredProc and RAISEERROR / Exceptionsq

Re: Possible bug with TMSStoredProc and RAISEERROR / Exceptionsq

Re: Possible bug with TMSStoredProc and RAISEERROR / Exceptionsq