Invalid column name in #temp table

I've got a query that dumps data to a temp table, then uses a WHILE loop to update a field in that temp table.  When I execute the SQL in Query Analyzer everything runs just fine, but when I call the stored procedure I get "Invalid column name 'notificationGroupName'" pointing to the initial insert into the temp table.  I also get another of the same error pointing to my ORDER BY clause when I'm finally selecting all the data in the end.  Can anyone point me in the right direction?

Thanks!
ALTER PROCEDURE [dbo].[sproc_dashboard_select_notifications]
	
	@UserID int

AS

SET NOCOUNT ON;

CREATE TABLE #TempNotifications
(
	ID int IDENTITY,
	notificationID int,
	notificationDate datetime,
	notificationGroupName varchar(250),
	processStepID int,
	coID varchar(50),
	suiteID int,
	leaseID int,
	assetID int,
	woID int,
	notificationRelatesTo varchar(250)
)

--	Save all applicable notifications to a temp table
INSERT INTO #TempNotifications (notificationID, notificationDate, notificationGroupName, processStepID, coID, suiteID, leaseID, assetID, woID)
	SELECT notificationID, notificationDate, notificationGroupName, processStepID, coID, suiteID, leaseID, assetID, woID
	FROM tblNotifications
	JOIN tblNotificationGroupLevel ON tblNotifications.notificationGroupLevelID = tblNotificationGroupLevel.notificationGroupLevelID
	JOIN tblNotificationGroup ON tblNotificationGroupLevel.notificationGroupID = tblNotificationGroup.notificationGroupID
	WHERE tblNotifications.userID = @UserID AND notificationStatus = 0

SELECT *
FROM #TempNotifications
ORDER BY notificationDate ASC, notificationGroupName ASC

SET NOCOUNT OFF;

Open in new window

LVL 8
saoirse1916Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris LuttrellSenior Database ArchitectCommented:
I tested what I could on your temp table and the syntax works fine.  I am thinking it is something about your Select that it is not liking.  If you are testing it can you comment out your insert and just run the select?  Check the table and column names and is there some difference in permissions when you run the SQL yourself and when you execute the statement to create the Stored Procedure?
0
LowfatspreadCommented:
try qualifiying all the column names on the select statement....
are you sure you haven't got a duplicate?

are you also sure that you dropped the temp table before executing the procedure?

INSERT INTO #TempNotifications
 (notificationID, notificationDate, notificationGroupName, processStepID, coID, suiteID, leaseID, assetID, woID)
      SELECT notificationID, notificationDate, ng.notificationGroupName
               , processStepID, coID, suiteID, leaseID, assetID, woID
      FROM tblNotifications as n
      JOIN tblNotificationGroupLevel as NGL
          ON N.notificationGroupLevelID = ngl.notificationGroupLevelID
      JOIN tblNotificationGroup as NG
          ON NGL.notificationGroupID = NG.notificationGroupID
      WHERE N.userID = @UserID
          AND notificationStatus = 0
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
saoirse1916Author Commented:
Well, it must have had something to do with dropping the table -- I just added in a DROP TABLE #TempNotifications and it said that it didn't exist, so I took out that line and ran the SP and it worked fine.  Odd...thanks for the suggestions!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.