Stored procedure hangs
Posted: 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
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