I have written a query that uses temp tables. I don't usually like to use temp tables but the data is coming from an openquery to a MySQL database and needs indexing.The query works perfectly when run as a standalone query but when I convert it onto a stored procedure, I get an error.This is a trimmed down version of my code (my code in full contains three queries to build temp tables and indexes and a main query which runs against these):CREATE PROCEDURE [dbo].[SP_RPT_TestProc]AS SELECT [user_id] ,user_username as Username ,contact_first_name + ' ' + contact_last_name as FullNameINTO #UsersFROM OPENQUERY(Project,'SELECT u.user_id,u.user_contact,u.user_username,c.contact_id,c.contact_first_name,c.contact_last_name FROM Users u INNER JOIN contacts c ON c.contact_id=u.user_contact')GOCREATE UNIQUE INDEX IX_Users_UserID ON #Users([User_ID])GOSELECT * FROM #UsersDROP TABLE #Users
And this is the error:Msg 1088, Level 16, State 12, Line 2Cannot find the object "#Users" because it does not exist or you do not have permissions.Msg 208, Level 16, State 0, Line 2Invalid object name '#Users'.I've only just started using T-SQL 2008 but I'm pretty sure i've done tons of these types of SP in 2005.I'd appreciate some ideas on what the problem is with using temp tables inside SPs. Thanks.---------------------------------------------------------------------------------http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club
Problem using temp tables in a stored proc
Posted - 2011-03-03 : 05:25:37
