SQL Server 2000 is correctly dropping the temporary stored procedure when the connection is terminated.
One gotcha that you might want to watch for: connection pooling.
-Mike
>For my understanding, any temp SP like #test#567 in Tempdb will drop automatically when user does not make connection with SQL SERVER.
>
>We have one SQL 6.5, all the temp SP are still here even nobody makes connection. Until we restart SQL, those temp SP will be dropped.
>
>1. Does anybody know this? Any 7 or 2000 experience is useful.
>
>2. Could we write script to drop those SP?
>
>
>Thanks
>
>
>Jim