Stored Procedure Skipping Code

I have a stored procedure that I use to clear certain records from defined temporary (not TempDB) tables.  For each table, the code first determines if the table exists and if so, deletes certain records.

Curiously, this stored procedure works perfectly when step through the code using VS 2008.  When I execute the procedure, either using an Access ADO command or by executing it from another stored procedure, only the first table is processed.

This has me really stumpted, so I would appreciate any insight to the cause of this behavior.

Following is the stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  PROC [dbo].[LOHCo_TC_ClearTempTables] (
            @UserID VARCHAR(10),
            @RetCode INT = NULL OUTPUT,
            @RetMsg VARCHAR(255) = NULL OUTPUT
            )
AS
  BEGIN

      SET NOCOUNT ON
      DECLARE @ErrMsg VARCHAR(255)

-- Clear this user's records from all temp tables.
      SET @ErrMsg = 'Error clearing tmpWKE.'
      IF OBJECT_ID('tmpLOHCoTCWKE','U') IS NOT NULL
        BEGIN
            DELETE FROM tmpLOHCoTCWKE WHERE UserID = @UserID
            IF @@ERROR <> 0 GOTO ERROR_HANDLER
            SET @RetMsg = 'Cleared tmpWKE'
        END

      SET @ErrMsg = 'Error clearing tmpWOM'
      IF OBJECT_ID('tmpLOHCoTCWOM','U') IS NOT NULL
        BEGIN
            DELETE FROM tmpLOHCoTCWOM WHERE UserID = @UserID
            IF @@ERROR <> 0 GOTO ERROR_HANDLER
            SET @RetMsg = @RetMsg + ', cleared tmpWOM'
        END

      SET @ErrMsg = 'Error clearing tmpWCL'
      IF OBJECT_ID('tmpLOHCoTCWCL','U') IS NOT NULL
        BEGIN
            DELETE FROM tmpLOHCoTCWCL WHERE UserID = @UserID
            IF @@ERROR <> 0 GOTO ERROR_HANDLER
            SET @RetMsg = @RetMsg + ', cleared tmpWCL'
        END

      SET @ErrMsg = 'Error clearing tmpWOO'
      IF OBJECT_ID('tmpLOHCoTCWOO','U') IS NOT NULL
        BEGIN
            DELETE FROM tmpLOHCoTCWOO WHERE UserID = @UserID
            IF @@ERROR <> 0 GOTO ERROR_HANDLER
            SET @RetMsg = @RetMsg + ', cleared tmpWOO'
        END

      SET @ErrMsg = 'Error clearing tmpWKO'
      IF OBJECT_ID('tmpLOHCoTCWKO','U') IS NOT NULL
        BEGIN
            DELETE FROM tmpLOHCoTCWKO WHERE UserID = @UserID
            IF @@ERROR <> 0 GOTO ERROR_HANDLER
            SET @RetMsg = @RetMsg + ', cleared tmpWKO'
        END

      SET @ErrMsg = 'Error clearing tmpGL_Trans'
      IF OBJECT_ID('tmpLOHCoTCGL_Trans','U') IS NOT NULL
        BEGIN
            DELETE FROM tmpLOHCoTCGL_Trans WHERE UserID = @UserID
            IF @@ERROR <> 0 GOTO ERROR_HANDLER
            SET @RetMsg = @RetMsg + ', cleared tmpGL_Trans'
        END

      SET @ErrMsg = 'Error clearing tmpGL_TransLine'
      IF OBJECT_ID('tmpLOHCoTCGL_TransLine','U') IS NOT NULL
        BEGIN
            DELETE FROM tmpLOHCoTCGL_TransLine WHERE UserID = @UserID
            IF @@ERROR <> 0 GOTO ERROR_HANDLER
            SET @RetMsg = @RetMsg + ', cleared tmpGL_TransLine'
        END

      SET @ErrMsg = 'Error clearing tmpEAT'
      IF OBJECT_ID('tmpLOHCoTCEAT','U') IS NOT NULL
        BEGIN
            DELETE FROM tmpLOHCoTCEAT WHERE UserID = @UserID
            IF @@ERROR <> 0 GOTO ERROR_HANDLER
            SET @RetMsg = @RetMsg + ', cleared tmpEAT'
        END

      SET @ErrMsg = 'Error clearing tmpBreak'
      IF OBJECT_ID('tmpLOHCoTCBreak','U') IS NOT NULL
        BEGIN
            DELETE FROM tmpLOHCoTCBreak WHERE UserID = @UserID
            IF @@ERROR <> 0 GOTO ERROR_HANDLER
            SET @RetMsg = @RetMsg + ', cleared tmpBreak'
        END

      SET @ErrMsg = 'Error clearing tmpPriorWOs'
      IF OBJECT_ID('tmpLOHCoTCPriorWOs','U') IS NOT NULL
        BEGIN
            DELETE FROM tmpLOHCoTCPriorWOs WHERE UserID = @UserID
            IF @@ERROR <> 0 GOTO ERROR_HANDLER
            SET @RetMsg = @RetMsg + ', cleared tmpPriorWOs'
        END
  END

-- Set return values to indicate successful completion.
SET @RetMsg = @RetMsg + '.  Successfull completion.'
SET @RetCode = 0
RETURN 0  

ERROR_HANDLER:
      SET @RetMsg = @ErrMsg
      SET @RetCode = 99  -- to indicate a problem.
      RETURN 99

Thanks in advance for your help!
Skip HouseConsultantAsked:
Who is Participating?
 
selva_konguCommented:
Is your stored procedure have execute permission to your application user or user role
Eg:
GRANT EXECUTE ON [dbo].[LOHCo_TC_ClearTempTables] TO [USER_GRP]
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
is it returning any error message while running from access Ado, also can you ensure that you are connecting to the proper database ?
0
 
Skip HouseConsultantAuthor Commented:
No error message.  You will notice that I add an abbreviated name of each table to RetMsg as each table is processed and a final message when processing is complete.
The return message is "Cleared tmpWKE.  Successfull completion."  Which leads me to believe all tables after the first one has been skipped.  

Yes I know that I am linking to the correct database.

I have stopped execution just prior to executing this stored procedure to determine if there might be any locks on any of the tables, but there are none.  The only lock is a shared lock on the database.
0
 
Anthony PerkinsCommented:
The only rational explanation is that the following (as well as all subsequent checks) is not true:
IF OBJECT_ID('tmpLOHCoTCWOM', 'U') IS NOT NULL

Add an ELSE to all those IF statements and return a value from them so that you are convinced.
0
 
Skip HouseConsultantAuthor Commented:
This was not the solution, but was on the right track.  

I had failed to grant SELECT, DELETE, INSTERT and UPDATE permissions for these new temp tables to the user_group underwhich is application is run.

Moral: remember debugging store procedures with VS does not necessarily involve the same user permissions and @@ERROR does not pick up this error.

Thanks all for the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.