Hidden error kills my transaction. Example.

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Miroo_news (miroo_at_USUNTO.poczta.fm)
Date: 03/11/04 

Date: Thu, 11 Mar 2004 11:55:34 +0100

It is continuation to my other thread with similar subject.

I'm using Delphi 7 and MSDE with sp3
(@@VERSION = Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Desktop Engine on
Windows NT 5.1 (Build 2600: Service Pack. 1))
MDAC version is 2.6 or 2.7 (how to check?)

There is small procedure on database:

CREATE PROCEDURE A_TEST
  @ID_DOC VARCHAR(36)
AS
DECLARE
  @U UNIQUEIDENTIFIER
BEGIN
  SET NOCOUNT ON
  SET @U = @ID_DOC
  RETURN 0
END

There is sp: TADOStoredproc in Delphi. It is connected
to db with standard options (as user "sa").
sp.ProcedureName is set to 'A_TEST;1'
Parameters are retrieved automatically.

There is the only button on form, which runs
procedure as below:

procedure TForm1.Button1Click(Sender: TObject);
begin
  sp.Parameters.ParamByName('@ID_DOC').Value :=
'00000000-0000-0000-0000-000000000000';
  sp.ExecProc;
  sp.ExecProc;
  sp.ExecProc;
  sp.ExecProc;
end;

When we run Profiler and press button on a form of this simple
application we can see that first ExecProc works fine,
second raises an error:
"Error: 8169, Severity: 16, State: 2 = 'Syntax error converting from a
character string to uniqueidentifier.'",
third and fourth (and so on) Execprocs work fine again.
The error isn't raised in Delphi.
There is no transaction (if it was - it would be rollbacked by the error).

The error appears always on the second ExecProc (starting from
opening connection to DB).
You can try it using my description.

Regards,
Miroo

Quantcast