Stored procedure hangs

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Sybar
Posts: 3
Joined: Tue 30 Aug 2011 16:40
Location: Developer

Stored procedure hangs

Post by Sybar » Tue 30 Aug 2011 16:51

Hi
I am using Delphi XE and SDAC 6. In an update program I have a stored procedure:

/****** Object: StoredProcedure [Customer].[OrderUpdate] Script Date: 08/30/2011 18:26:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [Customer].[OrderUpdate]
-- Add the parameters for the stored procedure here
@MfgLoc nvarchar(10),
@OrderNo nvarchar(25),
@AckDate varchar(10),
@ReqDelDate varchar(10),
@PickListDate varchar(10),
@ChgAckdate smallint,
@ChgReqDelDate smallint,
@ChgPickListDate smallint,
@Status tinyint output
AS
BEGIN TRANSACTION
SET NOCOUNT ON;

UPDATE Customer.Orders SET Acknowledge_Date=CAST(@AckDate AS Smalldatetime),Requested_Del_Date=CAST(@ReqDelDate AS Smalldatetime),
Picklist_Date=CAST(@PickListDate AS Smalldatetime),Chg_Acknowledge_Date=@ChgAckdate,Chg_Reg_Date=@ChgReqDelDate,
Chg_Picklist_Date=@ChgPickListDate,Acknowledge_DayOfYear=DATEPART(DAYOFYEAR,@AckDate),
Acknowledge_Week=DATEPART(WK,@AckDate),Acknowledge_Month=DATEPART(M,@AckDate),Acknowledge_Year=DATEPART(YEAR,@AckDate),
PickList_DayOfYear=DATEPART(DAYOFYEAR,@PickListDate),PickList_Week=DATEPART(WK,@PickListDate),
PickList_Month=DATEPART(M,@PickListDate), PickList_Year=DATEPART(YEAR,@PickListDate)
WHERE Mfg_Loc=@MfgLoc AND Order_No=@OrderNo
IF (@@Error0)
BEGIN
ROLLBACK TRANSACTION
SELECT @Status = 1 --Cannot update order head table
END
ELSE
BEGIN
SELECT @Status = 0 --update order head table ok
COMMIT TRANSACTION
END
This procedure runs perfectly OK for several updates but in the fourth update it hangs after 133 uses at the statement after the Execute statement. I have run in debug mode and there is no error message. It just hangs!!! So I am stuccoed and appreciate any help I can get.
Regards

AndreyZ

Post by AndreyZ » Wed 31 Aug 2011 12:32

Hello,

I cannot reproduce the problem. Please specify the following:
- a script to create and fill the Orders table (you can post it here or send to andreyz*devart*com);
- the exact version of SDAC. You can learn it from the About sheet of TMSConnection Editor. Please note that the latest available version of SDAC for RAD Studio XE is 5.10.0.8. The SDAC version 6.0.0.0 Beta is available only for RAD Studio XE2;
- the exact version of SQL Server server and client. You can learn it from the Info sheet of TMSConnection Editor.

Sybar
Posts: 3
Joined: Tue 30 Aug 2011 16:40
Location: Developer

Stored procedure hangs

Post by Sybar » Wed 31 Aug 2011 17:26

Hello
Thanks for your prompt answer. I use SDAC 5.00.0.4. SQL Server according to Connection Editor is 10.50.1600 and OLE DB Provider for SQL Server 08.00.1132. My delphi code looks like this:

dmLogics.MSStoredProc2.StoredProcName := 'Customer.OrderUpdate;1';
dmLogics.MSStoredProc2.Params.ParamByName('@MfgLoc').AsString := MfgLoc;
dmLogics.MSStoredProc2.Params.ParamByName('@OrderNo').AsString := OrderNo;
dmLogics.MSStoredProc2.Params.ParamByName('@AckDate').AsString := AckDateDB;
dmLogics.MSStoredProc2.Params.ParamByName('@ReqDelDate').AsString := ReqDelDateDB;
dmLogics.MSStoredProc2.Params.ParamByName('@PickListDate').AsString := PickListDateDB;
dmLogics.MSStoredProc2.Params.ParamByName('@ChgAckDate').AsInteger := ChangeAckDateDB;
dmLogics.MSStoredProc2.Params.ParamByName('@ChgReqDelDate').AsInteger := ChangeReqDelDateDB;
dmLogics.MSStoredProc2.Params.ParamByName('@ChgPickListDate').AsInteger := ChangePickListDateDB;
dmLogics.MSStoredProc2.Execute;
RecStatus := dmLogics.MSStoredProc2.Params.ParamByName('@Status').AsInteger;
dmLogics.MSStoredProc2.Close;
The script including table data can be downloaded using the link https://files.me.com/erik.larsson1/d5vu7w

Regards

AndreyZ

Post by AndreyZ » Thu 01 Sep 2011 11:52

Unfortunately, the link you gave me leads to a file that is not available. Please send your script to andreyz*devart*com.

Post Reply