Re: Stored procedure fails without reason

That isn't much to go on Diana but it is my guess you are running across a
constraint violation probably a PK constraint.If the error is severe enough
(which constraint violations usually are) the batch will halt and no further
code will be executed, even your error checking code. It looks like you have
a bunch of Left Joins, maybe you have a NULL where the column does not
accept nulls?

--
Andrew J. Kelly SQL MVP


"Diana M" <PleaseNoJunk@com> wrote in message
news:2650B3F7-E077-4FD7-9FD0-C12265DB5C78@xxxxxxxxxxxxxxxx
> Hello everybody
> I have stored procedure SP1 (sql server 2000), that calls stored procedure
> SP2 and SP3. SP2 has 10 queries in it.
> In most cases SP2 runs without problems, however sometimes 9th
> query(insert
> query) fails and it doesn't go to 10th query and it does not go stored
> procedure SP3 at all.
> If I try to recreate the problem (have same data and same user
> credentials)
> I can't reproduce it.
> 8th query is: insert db.dbo.into table1 ... from tempdb.dbo...... join
> left db.dbo.table2 left left left left
>
> SET @Err_Nr = @@ERROR
> IF @Err_Nr<>0
> BEGIN
> SET @Status_Msg = 'SP: spname Status(8): Failed to insert into table1,
> Error Nr: ' + CAST(@Err_Nr as varchar)
> INSERT INTO UserLog SELECT HOST_NAME(),SYSTEM_USER,@Status_Msg
> ,CURRENT_TIMESTAMP,'SP'
> RETURN -1
> END
> ELSE
> BEGIN
> SET @Status_Msg = 'SP: spname(8): Insert into table1 was successful'
> INSERT INTO UserLog SELECT HOST_NAME(),SYSTEM_USER,@Status_Msg
> ,CURRENT_TIMESTAMP,'SP'
> END
>
> 9th query is very similar as 8th trying to insert data into the same table
> from same tables
> 9th query is: insert db.dbo.into table1 ... from tempdb.dbo......
> join
> left left left left left
> SET @Err_Nr = @@ERROR
> IF @Err_Nr<>0
> BEGIN
> SET @Status_Msg = 'SP: spname Status(9): Failed to insert into table1,
> Error Nr: ' + CAST(@Err_Nr as varchar)
> INSERT INTO UserLog SELECT HOST_NAME(),SYSTEM_USER,@Status_Msg
> ,CURRENT_TIMESTAMP,'SP'
> RETURN -1
> END
> ELSE
> BEGIN
> SET @Status_Msg = 'SP: spname(9): Insert into table1 was successful'
> INSERT INTO UserLog SELECT HOST_NAME(),SYSTEM_USER,@Status_Msg
> ,CURRENT_TIMESTAMP,'SP'
> END
>
> in case the procedure fails, I have the record in UserLog:
> SP: spname(8): Insert into table1 was successful
> and I do not have anymore records.
> I would appreciate any suggestions on this issue
> Thanks,
> Diana M


.

Loading