Why does this sp work on one server but not another?

[Jerrycurl]

I have the following stored procedure running fine on a SQL Server 6.50.416 machine. <br>
<br>
if exists (select * from sysobjects where id = object_id('dbo.sp_search_person') and sysstat & 0xf = 4)<br>
drop procedure dbo.sp_search_person<br>
GO<br>
<br>
/****** Object: Stored Procedure dbo.sp_search_person Script Date: 9/14/99 9:14:50 PM ******/<br>
CREATE PROCEDURE sp_search_person AS<br>
<br>
select * from person p, #searchtemp st where p.person_id = st.person_id<br>
<br>
GO<br>
<br>
<br>
I need to get it on another machine running the same build of SQL Server. When I try to transfer to or recreate it on the other server, I get the error: <br>
Error 208: [SQL Server] Invalid object name &quot;#searchtemp&quot;<br>
<br>
What could be different about the servers that one would like it and the other would not?

 

[jnicho02]

Where does your temporary table #searchtemp get created? There may be another stored procedure that has to run first.

 

[BenClark]

All the message is telling you is that the temp<br>
table #searchtemp has not been created on the second<br>
server.<br>
You need to find the DDL for it on the first sever<br>
and run it on the second server.<br>
Your procedure should compile fine after that.<br>
Also you might want to change you procedure to something<br>
like:<br>
IF OBJECT_ID('dbo.sp_search_person') IS NOT NULL<br>
BEGIN<br>
DROP PROCEDURE dbo.sp_search_person<br>
END<br>
go<br>