• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

SQL Server Store procedure runs infinitely

I have a SQL Server 2000 store procedure that reads and insert data to an AS400. Suddenly it stopped working properly. On the Query analyzer: if I call it using exec sp_name, it runs infinitely; if I run the store procedure code it runs properly. Any idea why when it is executed as a store procedure runs infinitely? Another thing is I cannot drop or alter it.
0
smurri
Asked:
smurri
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you say you cannot alter the procedure, but could you add the WITH RECOMPILE option to it?
0
 
SQL_SERVER_DBACommented:
so you have the connection timeout set to 0?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
@SQL_SERVER_DBA:
I am sorry to say, but I do NOT see how that can help to solve the "infinite" run, ie the underlying problem?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
smurriAuthor Commented:
I cannot drop, i cannot alter and i cannot add WITH RECOMPILE option. To fix the problem I created a new store procedure using the code of the not workingone.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
may I ask why you cannot alter the procedure?
0
 
smurriAuthor Commented:
I cannot alter it because after I press F5 it keeps running and never finishes.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>after I press F5
where do you press F5, actually?

to change the procedure, you would run some SQL code like this:

ALTER PROCEDURE your_procedure
  <parameter as defined>
WITH RECOMPILE
AS
 <procedure body as defined>


this should NOT take ages to complete, but a second or so...

0
 
smurriAuthor Commented:
F5 on Query Analizer . It takes ages, and that is the reason I posted this problem.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
is the procedure used all the time?
0
 
smurriAuthor Commented:
Every 5 minutes. I stopped the job that calls it before using ALTER. I tried twice after I modified the job to call the new store procedure. In both cases it took ages to run.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you show the code you have in QA that you try to "run"?
0
 
smurriAuthor Commented:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER  PROCEDURE [dbo].[sp_HIS_Import_Order_Detail] WITH RECOMPILE

AS

INSERT SB_HIS_ORDER_DETAIL
(Company_Number,Order_Number,Order_Line_item_Num,Substitution_Line_Num,Item_Code,Customer_Item_Ref,
Item_Description1,Item_Description2,Age_Code,Slip_Sheet,Shrink_Wrap,Palletize,Ship_From_Warehouse,
Ship_From_Whs_Type,Ship_To_Warehouse,Scheduled_Quantity,Scheduled_Weight,Max_Net_Weight,Min_Unit_Weight,
Max_Unit_Weight,Estimated_Pallets,Avg_Pallet_Tare,OldestOKDate,NewestOKDate,Status1,Status2,Status3,
Status4,Status5,Record_Status,Process_Status,Error_Reason,Create_Date,Create_Time,Change_Date,Change_Time,
TransactionDate)
Select
BCY1NX, BCZPNX,BCZXNX, BCZYNX, BCY2NX, BCGIT1, BCGGT1, BCGHT1, BCVFSX, BCVGSX, BCVHSX, BCVISX,
BCF8AA, BCV4SX, BCF9AA, BCZFNX, BCZZNX, BCZ0NX, BCBINY, BCBJNY, BCBKNY, BCATNY, BCALD8, BCAMD8,
BCZISX, BCZJSX, BCZKSX, BCZLSX, BCZMSX, BCUWSX, BCUXSX, BCV3SX, BCAHNY, BCAINY, BCAJNY, BCAKNY,
getdate()
FROM SBAS400.BIGBYTE.PRKFLIB.PPBCCPPGUY

DELETE SBAS400.BIGBYTE.PRKFLIB.PPBCCPPGUY

if exists(select 1 from SB_HIS_ORDER_DETAIL where Process_status = 0)
begin
      INSERT SBAS400.BIGBYTE.PRKFLIB.PPBCCPPSTS
      (BCY1NX,BCZPNX, BCZXNX, BCZYNX, BCY2NX, BCGIT1, BCGGT1, BCGHT1, BCVFSX, BCVGSX, BCVHSX, BCVISX,
      BCF8AA, BCV4SX, BCF9AA, BCZFNX, BCZZNX, BCZ0NX, BCBINY, BCBJNY, BCBKNY, BCATNY, BCALD8, BCAMD8,
      BCZISX, BCZJSX, BCZKSX, BCZLSX, BCZMSX, BCUWSX, BCUXSX, BCV3SX, BCAHNY, BCAINY, BCAJNY, BCAKNY)
      SELECT
      Company_Number, Order_Number, Order_Line_item_Num, Substitution_Line_Num, Item_Code, Customer_Item_Ref,
       Item_Description1, Item_Description2, Age_Code, Slip_Sheet, Shrink_Wrap, Palletize, Ship_From_Warehouse,
       Ship_From_Whs_Type, Ship_To_Warehouse, Scheduled_Quantity, Scheduled_Weight, Max_Net_Weight, Min_Unit_Weight,
       Max_Unit_Weight, Estimated_Pallets, Avg_Pallet_Tare, OldestOKDate, NewestOKDate, Status1, Status2, Status3,
       Status4, Status5, Record_Status, Process_Status, Error_Reason, Create_Date, Create_Time, Change_Date, Change_Time
      from SB_HIS_ORDER_DETAIL where Process_status = 0
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
> SBAS400.BIGBYTE.PRKFLIB.PPBCCPPGUY
this is a linked server, the on on the AS400, right?


let's see if this works any better:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER  PROCEDURE [dbo].[sp_HIS_Import_Order_Detail]
WITH RECOMPILE
AS

INSERT SB_HIS_ORDER_DETAIL
(Company_Number,Order_Number,Order_Line_item_Num,Substitution_Line_Num,Item_Code,Customer_Item_Ref,
Item_Description1,Item_Description2,Age_Code,Slip_Sheet,Shrink_Wrap,Palletize,Ship_From_Warehouse,
Ship_From_Whs_Type,Ship_To_Warehouse,Scheduled_Quantity,Scheduled_Weight,Max_Net_Weight,Min_Unit_Weight,
Max_Unit_Weight,Estimated_Pallets,Avg_Pallet_Tare,OldestOKDate,NewestOKDate,Status1,Status2,Status3,
Status4,Status5,Record_Status,Process_Status,Error_Reason,Create_Date,Create_Time,Change_Date,Change_Time,
TransactionDate)
Select l.*, getdate()
FROM OPENQUERY ( SBAS400, ' SELECT
BCY1NX, BCZPNX,BCZXNX, BCZYNX, BCY2NX, BCGIT1, BCGGT1, BCGHT1, BCVFSX, BCVGSX, BCVHSX, BCVISX,
BCF8AA, BCV4SX, BCF9AA, BCZFNX, BCZZNX, BCZ0NX, BCBINY, BCBJNY, BCBKNY, BCATNY, BCALD8, BCAMD8,
BCZISX, BCZJSX, BCZKSX, BCZLSX, BCZMSX, BCUWSX, BCUXSX, BCV3SX, BCAHNY, BCAINY, BCAJNY, BCAKNY
FROM BIGBYTE.PRKFLIB.PPBCCPPGUY ' ) l

DELETE FROM  ( SBAS400,  ' SELECT * FROM BIGBYTE.PRKFLIB.PPBCCPPGUY ' )

if exists(select 1 from SB_HIS_ORDER_DETAIL where Process_status = 0)
begin
      INSERT INTO OPENQUERY(SBAS400,
      ' SELECT BCY1NX,BCZPNX, BCZXNX, BCZYNX, BCY2NX, BCGIT1, BCGGT1, BCGHT1, BCVFSX, BCVGSX, BCVHSX, BCVISX,
      BCF8AA, BCV4SX, BCF9AA, BCZFNX, BCZZNX, BCZ0NX, BCBINY, BCBJNY, BCBKNY, BCATNY, BCALD8, BCAMD8,
      BCZISX, BCZJSX, BCZKSX, BCZLSX, BCZMSX, BCUWSX, BCUXSX, BCV3SX, BCAHNY, BCAINY, BCAJNY, BCAKNY
      FROM  BIGBYTE.PRKFLIB.PPBCCPPSTS ' )
      SELECT
      Company_Number, Order_Number, Order_Line_item_Num, Substitution_Line_Num, Item_Code, Customer_Item_Ref,
       Item_Description1, Item_Description2, Age_Code, Slip_Sheet, Shrink_Wrap, Palletize, Ship_From_Warehouse,
       Ship_From_Whs_Type, Ship_To_Warehouse, Scheduled_Quantity, Scheduled_Weight, Max_Net_Weight, Min_Unit_Weight,
       Max_Unit_Weight, Estimated_Pallets, Avg_Pallet_Tare, OldestOKDate, NewestOKDate, Status1, Status2, Status3,
       Status4, Status5, Record_Status, Process_Status, Error_Reason, Create_Date, Create_Time, Change_Date, Change_Time
      from SB_HIS_ORDER_DETAIL where Process_status = 0
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

0
 
smurriAuthor Commented:
We suspect there is a problem with the store procedure execution plan. Using the same code we created a new one. It takes ages to see the store procedure execution plan on the QA.
0
 
Computer101Commented:
PAQed with points refunded (125)

Computer101
EE Admin
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now