Stored procedure hangs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Stored procedure hangs

Author

Topic

Sybar
Starting Member

6 Posts

Posted - 2011-08-30 : 13:09:10

Hi
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 (@@Error<>0)
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
that performs OK in 3 subsequent update runs. At the fourth update run and when it has been used 133 times it hangs at the statement Execute. The program is in Delphi XE and I am using SDAC 6.0 Input data is OK so I am stucked. All advices are appreciated.
Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-30 : 13:17:39

it has been 133 times means its concurrent calling or calling repeatedly?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sybar
Starting Member

6 Posts

Posted - 2011-09-09 : 09:51:33

Well the procedure is updating an order table with orders. So after 133 orders have been processed it hangs on the 134th. I am beginning to think that some buffer areas are too small. Anyway it is very peculiar with no error message even in debugging mode in Delphi

quote:
Originally posted by visakh16

it has been 133 times means its concurrent calling or calling repeatedly?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-09 : 10:17:11

is it executing concurrently

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sybar
Starting Member

6 Posts

Posted - 2011-09-19 : 08:56:27

Hi
Repeatedly.
Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 09:18:16

in a loop?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sybar
Starting Member

6 Posts

Posted - 2011-09-20 : 07:52:04

Yes. I am reading data from an Excel file sequentially to update or insert table rows. It is the update procedure that hangs. However I have also sent the program to the provider of SQL components (Devart SDAC) and they cannot repeat my problem. The program runs in VMware Fusion/Win 7 and I start to think that the problem resides there.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 08:09:48

why sequential read? why cant you use OPENROWSET or OPENQUERY and do set based operation?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources