Thanks for your input. I've tried (unsuccesfully) some variations of what you suggested, but to no avail. I have created two SP's which compile and appear to run without errors, but they also don't drop any tables.

You have already provided an ear which I appreciate, and I certaintly don't want to abuse your help, so If I'm asking too much I understand. In any event Here are my two SP's. If you have a spare couple of minutes to look them over, perhaps another set of eyes will spot what I'm overlooking.

Thanks again,
-Isaac

sp__drop_temptables_1

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.sp__drop_temptables_1') AND sysstat & 0xf = 4)
	DROP PROCEDURE dbo.sp__drop_temptables_1
GO

CREATE PROC sp__drop_temptables_1
AS
/*
	 Drop tables from all databases 
	 This SP creates a list of databases, then calls sp__drop_temptables_1
	 which does the dropping
	IR - 01/30/2001
*/
DECLARE @err INT
DECLARE @msg VARCHAR(255)
DECLARE @cmd VARCHAR(255)
DECLARE @db_name VARCHAR(255)

DECLARE dbList CURSOR FOR 
	SELECT name FROM sysdatabases
	WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb', 'pubs')

OPEN dbList
FETCH NEXT FROM dbList INTO @db_name
WHILE @@fetch_status <> -1
BEGIN

	PRINT @db_name
	SELECT @cmd = 'USE ' + @db_name + CHAR(13) + CHAR(10)
	EXEC (@cmd)
	SELECT @cmd = @cmd + 'EXECUTE master..sp__drop_temptables_2'
	EXEC (@cmd)
	
	FETCH NEXT FROM dbList INTO @db_name
END
CLOSE dbList
DEALLOCATE dbList

RETURN 0
GO

sp__drop_temptables_1

if exists (select * from sysobjects where id = object_id('dbo.sp__drop_temptables_2') and sysstat & 0xf = 4)
	drop procedure dbo.sp__drop_temptables_2
GO

create proc sp__drop_temptables_2
as
/* 
	Cleanup procedure to drop temp tables
	Drops tables from current database whose name is
	 like TEMP% and refdate < current_timestamp - 1
	Allways returns 0
*/
DECLARE @msg VARCHAR(255)
DECLARE @DropStatement VARCHAR(255)
DECLARE @db_name VARCHAR(255)
DECLARE @err INT

DECLARE tablenames CURSOR FOR
	SELECT DropStatement = 'DROP TABLE ' + user_name(uid) + '.' + o.name
	FROM sysobjects o, master.dbo.spt_values v, master.dbo.spt_values x
	WHERE o.sysstat & 0xf = v.number
		AND v.type = 'O'
		AND x.type = 'R'
		AND o.userstat & -32768 = x.number
		AND o.name like 'temp%'
		AND o.refdate < DATEADD(day, -1, current_timestamp)
		AND v.name + x.name = 'user table'
	ORDER BY DropStatement ASC

OPEN tablenames
FETCH NEXT FROM tablenames INTO @DropStatement

WHILE @@fetch_status <> -1
BEGIN
	IF @@fetch_status = 0
	BEGIN
		-- print @DropStatement
		exec (@DropStatement)
		SELECT @err = @@error
		IF @err <> 0
		BEGIN
			SELECT @msg = 'ERROR with command: ' + @DropStatement + ': ' + str(@err)
			print @msg
		END
	END
	FETCH NEXT FROM tablenames INTO @DropStatement
END
CLOSE tablenames
DEALLOCATE tablenames

RETURN 0
GO

---

>From within a stored procedure in master try something like this:
>
>DECLARE @dbname sysname
>DECLARE @cmd varchar(2000)
>-- some magic happens so that @dbname takes on the name of a database <s>
>SET @cmd = 'USE ' + @dbname + CHAR(13) + CHAR(10)
>SET @cmd = @cmd + 'EXECUTE master..sp_cleanup'
>
>Catching errors will be the difficult part. In the beginning I'd just write them to the log with xp_logevent.
>
>-Mike