[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Stored procedure issue

Posted on 2011-10-05
8
Medium Priority
?
286 Views
Last Modified: 2012-05-12
Hello experts. Please let me know what I am doing wrong on the attached SP.

Msg 207, Level 16, State 1, Procedure usp_QA_Defects_All_Business_Areas, Line 71
Invalid column name 'BA'.

BA is the second column on a temp table.
thanks
lance
All-defects-for-1-to-x-months-ac.txt
0
Comment
Question by:lancerxe
8 Comments
 
LVL 27

Assisted Solution

by:Shaun Kline
Shaun Kline earned 400 total points
ID: 36920925
Any possibility your temp table was created under a different owner/schema?
When you check for the table prior to delete you check for dbo.tmpQA_DefectsAllBusinessAreas.
You then delete but only reference tmpQA_DefectsAllBusinessAreas.
You also build and insert without the owner name.

And one suggestion, if you do not need the temp table after the stored procedure runs, consider using a true temp table (a table where the first character is a # symbol). Once the stored procedure ends the temp table will automatically be cleaned up.
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1200 total points
ID: 36920929
Maybe it is not actually dropping some old version of the tmpQA table.

Try:
IF OBJECT_ID(N'dbo.tmpQA_DefectsAllBusinessAreas') IS NOT NULL
   BEGIN
   DROP TABLE tmpQA_DefectsAllBusinessAreas;
   END

Open in new window


I do not have your table structure, but this line recognizes BA for me in SSMS as hoovering over tells me details from CREATE TABLE...maybe I am missing it, too, or as I said it is just the IF.
INSERT INTO tmpQA_DefectsAllBusinessAreas(MonthYear, BA, NumOfDefects)
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36920934
Good points from Shaun, I thought of that also. Works for me since my user's default schema is dbo, but would not if yours is not.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 61

Expert Comment

by:HainKurt
ID: 36920938
change name BA to BAXXXX and see if the message changes :)
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 36920953
and try this

INSERT INTO tmpQA_DefectsAllBusinessAreas(MonthYear, NumOfDefects)
SELECT @Month AS MonthYear, COUNT(*) AS BugNum
         FROM dbo.Bug b
where 1=2

just to see this works or not...
0
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 1200 total points
ID: 36920971
By the way, it would appear you have some duplication going on:
SET @MonthID = @MonthID + 1
        
        IF  @MonthID > @ToMonthID
                  BREAK
            ELSE
                  CONTINUE
       
       END


        SET @MonthID = @MonthID + 1
        
        IF  @MonthID > @ToMonthID
                  BREAK
            ELSE
                  CONTINUE
       
       END

Open in new window


Couple of suggestions:
- fix indentation so  you can more clearly see when each different level begins, i.e., procedure, while loop, if statements, etc.
- use the begin and end with IFs just to ensure the above is clarified in code.
- if you have WHILE @MonthID <= @ToMonthID, the check IF  @MonthID > @ToMonthID to use BREAK is redundant.
0
 
LVL 32

Assisted Solution

by:Ephraim Wangoya
Ephraim Wangoya earned 400 total points
ID: 36921598
why not use an actual temp table as follows
/****** Object:  StoredProcedure [dbo].[usp_QA_Defects_All_Business_Areas]    Script Date: 10/05/2011 16:35:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--********************************************************************************
		-- STORED PROCEDURE NAME: usp_QA_Defects_All_Business_Areas
		-- CREATED BY: X
		-- CREATED DATE: 10/05/2011
		-- DESCRIPTION: Query Project, Type, and number of defects (bugs) for each month
		-- within a range of months specified by 2 parameters of this stored procedure
		-- NOTE: An example to call this sproc:
		-- EXEC  usp_QA_Defects_All_Business_Areas '3/2011', '8/2011', 'CARA'
		-- SELECT * FROM dbo.tmpQA_DefectsAllBusinessAreas
		-- ORDER BY Id
		
		-- Note, for parameters enter '3/2011' not '03/2011'    
        --********************************************************************************
							
		--For testing        
                --DECLARE @FromMonth varchar(7)
		--DECLARE @ToMonth varchar(7)
		--DECLARE @MonthStr 
		--SET @FromMonth = '3/2011'   -- '3/2011' not '03/2011'
		--SET @ToMonth = '8/2011'	
                
		
ALTER PROCEDURE [dbo].[usp_QA_Defects_All_Business_Areas]
  @FromMonth varchar(7),
  @ToMonth varchar(7)
  
AS
	SET NOCOUNT ON
           
    -- Get MonthID
    DECLARE @FromMonthID smallint
    DECLARE @ToMonthID smallint
    DECLARE @Month varchar(7)
    DECLARE @MonthID smallint
                
    SELECT @FromMonthID = MonthID FROM dbo.QA_MonthIDLookup
    WHERE Month = @FromMonth
    -- testing
    --PRINT @FromMonthID  -- 15
    SELECT @ToMonthID = MonthID FROM dbo.QA_MonthIDLookup
    WHERE Month = @ToMonth
    -- testing
    --PRINT @ToMonthID  -- 20
    
    --Execute the following loop, insert results into a temp table after each iteration
    --At the beginning of each iteration, find value of @Month
    
    SET @MonthID = @FromMonthID

        --Create temp table to store results
    IF OBJECT_ID ('tempdb..##tmpQA_DefectsAllBusinessAreas') IS NOT NULL
		DROP TABLE ##tmpQA_DefectsAllBusinessAreas
	
	CREATE TABLE ##@tmpQA_DefectsAllBusinessAreas(
		Id int not null identity primary key,
		MonthYear varchar(7) not null,
		BA varchar(50) not null,
		NumOfDefects int not null
	)
                        
    -- LOOP STARTS HERE
            
    WHILE @MonthID <= @ToMonthID    
    BEGIN
    
		-- Find @Month based on @MonthID
		SELECT @Month = Month FROM dbo.QA_MonthIDLookup
		WHERE MonthID = @MonthID

		INSERT INTO ##tmpQA_DefectsAllBusinessAreas(MonthYear, BA, NumOfDefects)
	       
		--Calculate bugs
      
		SELECT @Month AS MonthYear, K.BusinessLineName, COUNT(*) AS BugNum
        FROM dbo.Bug b          
         INNER JOIN dbo.Iteration i ON i.IterationID = b.IterationID
		 INNER JOIN dbo.Project J ON J.Project = I.Project 
         INNER JOIN dbo.BusinessLine K ON K.BusinessLineCode = J.BusinessLineCode
         WHERE(b.Latest = 1)
         AND b.Deleted = 0
         AND b.CollectionID = 2
         --AND b.DateCreated >= @FromDate
         --AND DateCreated < DateAdd(d, 1, @ToDate)
         AND RTRIM(convert(char(2),DATEPART(M,DateCreated))) + '/' + convert(char(4),DATEPART(YEAR,DateCreated)) = @Month
         
         AND ((b.State = 'Active' AND  b.Triage = 'Triaged') OR b.State = 'Closed')
         GROUP BY K.BusinessLineName
	END

Open in new window

0
 

Author Comment

by:lancerxe
ID: 36924155
Thanks for your sugestions. The problem wes  that the temp file tmpQA_DefectsAllBusinessAreas existed already and it had been created with a column name other than BA. When I deleted the temp file and ran the SP again, it worked fine.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
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…

873 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