Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Stored Procedure Skipping Code

Posted on 2012-08-22
5
Medium Priority
?
288 Views
Last Modified: 2012-08-23
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!
0
Comment
Question by:Skip House
5 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38323005
is it returning any error message while running from access Ado, also can you ensure that you are connecting to the proper database ?
0
 

Author Comment

by:Skip House
ID: 38323111
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
 
LVL 9

Accepted Solution

by:
selva_kongu earned 1500 total points
ID: 38323440
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38324577
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
 

Author Closing Comment

by:Skip House
ID: 38324938
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Screencast - Getting to Know the Pipeline
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question