You must change all existing user-created stored procedures that assume that temporary tables are always in tempdb.
For instance, a stored procedure that checks the sysobjects catalog of the system tempdb for the existence of a private temporary table is no longer a valid check since such a table exists in the assigned temporary database, which may be tempdb. The following example demonstrates this:
select db_name(@@tempdbid) go
------------------------------ a_tempdb1 (1 row affected)
create table #t1 (c1 int) go
#t1 is not found in the sysobjects catalog of the system tempdb:
use tempdb select name from sysobjects where name like "#%"
name ------------------------------ (0 rows affected)
Instead, the entry is in the catalog of their assigned tempdb.
declare @tempdb_name varchar(32) select @tempdb_name = db_name(@@tempdbid) use @tempdb_name go
(1 row affected)
select name from sysobjects where name like "#%" go
name ------------------------------ #t1__________00000270012069406 (1 row affected)
