SQL Server Performance Forum – Threads Archive

Infinite Loop when Altering the Stored Proc

Goto my latest post for the main error : When i am trying to alter this sp and give the run cmmnd in query analyzer
this never stops.I have to stop it manually.I am unable to move on.Pls help
as to where the infinite loop is occuring.
Initially it runs fine but when i add a new query it goes into infinite loop.
even if i undo the last query and rerun it , same thing happen.
pls correct the syntax ALTER PROCEDURE P_INSP_HDR_INTFC_INS_Test
@DIWOIDdecimal(11,0),
@DILOANchar(35),
@DIDEPTchar(2),
@DIBNKGRPchar(3),
@DIJOBchar(4) = NULL,
@SOURCE int = NULL ,
@MSG_TXTvarchar(500) output
AS
SET NOCOUNT ON DECLARE @PROCESS_IDINT,@raiserrortext varchar(50)
DECLARE @ERR INT IF @SOURCE is NULL
BEGIN –1 INSERT INTO
DBO.INSP_HDR_INTFC
(
DIWOID,
DILOAN,
DIDEPT,
DIBNKGRP,
DIJOB
)
VALUES
(
@DIWOID,
@DILOAN,
@DIDEPT,
@DIBNKGRP,
@DIJOB
)
SELECT @ERR = @@ERROR
IF @ERR <> 0
BEGIN
— ROLLBACK TRANSACTION
SELECT @MSG_TXT = ‘Error Inserting into table HDR_INTFC_INS’
END END
IF @SOURCE = 1
BEGIN –2 SET @PROCESS_ID = @DIWOID
INSERT INTO
DBO.INSP_HDR_INTFC
(
DIWOID,
DILOAN,
DIDEPT,
DIBNKGRP,
DIJOB
)
VALUES
(
DBO.FN_ORDER_REC_NUM(@DIWOID),
@DILOAN,
@DIDEPT,
@DIBNKGRP,
@DIJOB
)
SELECT @ERR = @@ERROR
IF @ERR <> 0
BEGIN
SELECT @MSG_TXT = ‘Error Inserting into table HDR_INTFC_INS’
END
CREATE TABLE #INSP_HDR_INTFC_tmp
(
[DIWOID] [varchar] (30),
[DILOAN] [char] (15) ,
[DIDEPT] [varchar] (15),
[DIBNKGRP] [varchar] (5),
[DIJOB] [char] (4)
)
Declare
@DIWOIDtmp varchar (30),
@DILOANtmp char (15) ,
@DIDEPTtmp varchar (15),
@DIBNKGRPtmp varchar (5),
@DIJOBtmp char (4) Insert into #INSP_HDR_INTFC_tmp select * from INSP_HDR_INTFC
where process_id = @DIWOID select
@DIWOIDtmp = DIWOID,
@DILOANtmp = DILOAN,
@DIDEPTtmp = DIDEPT,
@DIBNKGRPtmp = DIBNKGRP,
@DIJOBtmp = DIJOB
from #INSP_HDR_INTFC_tmp IF EXISTS(SELECT * FROM DBO.PROPERTY1 WHERE MRTG_LOAN_NUMBER = @DILOANtmp
AND BANK_CLIENT = @DIBNKGRPtmp
AND BANK_DEPT = @DIDEPTtmp)
BEGIN
select * from #INSP_HDR_INTFC_tmp where process_iD = @Process_id
–SELECT @MSG_TXT = ‘NO VALUES IN table PROPERTY’
END
ELSE
SELECT @MSG_TXT = ‘Error Inserting into table PROPERTY’ END–2
go

---

From what you have posted, you are not using any loops to cause an infinite loop. Is this the entire code or are there other pieces that get called which you haven’t posted? Also, I am not exactly sure by what you mean when you say, "when I add a new query, it does into an infinite loop" … can you elaborate on this statement?

---

I was able to narrow down the problem.
There is some problem with the property table.
When i select the property table in query analyzer and
do a ALT+ F1 its behaving the same way as to not giving any information on
the table.Is the table locked ? any clue When i try to access this table from EM I have the error
: Lock request timeout period exceeded

---

]]>