September 5, 2012 at 10:45 am
Hi All,
i am trying to clean up my temp table sin tempdb database that are older than 24 hours.
during my research the following link gave me the information that was needed.
http://potomac9499.wordpress.com/2007/12/31/cleaning-up-tempdb/
the script create a Sp and a Table. when executing the stored procedure it will delete all the temporary tables older than 24hours.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N’[dbo].[TempTableToKeep]‘) AND OBJECTPROPERTY(id, N’IsUserTable’) = 1)
DROP TABLE [dbo].[TempTableToKeep]
GO
CREATE TABLE [dbo].[TempTableToKeep] (
[TempTable] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DateToDelete] [datetime] NOT NULL
) ON [PRIMARY]
GO
IF EXISTS (SELECT name FROM sysobjects WHERE name = N’sp_DropTempTables’ AND type = ‘P’)
DROP PROCEDURE sp_DropTempTables
GO
CREATE PROCEDURE sp_DropTempTables
AS
DECLARE @Cursor AS CURSOR
DECLARE @Name AS VARCHAR(100)
DECLARE @TableName AS SYSNAME
DECLARE @Owner AS VARCHAR(100)
DECLARE @sql AS NVARCHAR(200)
SET @Cursor = CURSOR SCROLL FOR
SELECT tempdb.dbo.sysobjects.name, tempdb.dbo.sysobjects.*
FROM TempTableToKeep
RIGHT OUTER JOIN tempdb.dbo.sysobjects ON TempTableToKeep.TempTable = tempdb.dbo.sysobjects.name
WHERE ((tempdb.dbo.sysobjects.crdate < DATEADD(hh, -12, GETDATE())) AND (tempdb.dbo.sysobjects.type = ‘U’) AND (TempTableToKeep.DateToDelete < GETDATE())) OR
((tempdb.dbo.sysobjects.crdate < DATEADD(hh, -12, GETDATE())) AND (tempdb.dbo.sysobjects.type = ‘U’) AND (TempTableToKeep.DateToDelete IS NULL))
OPEN @Cursor
FETCH FIRST FROM @Cursor
INTO @Name, @Owner
WHILE (@@FETCH_STATUS = 0)
BEGIN
If (@@FETCH_STATUS = 0)
BEGIN
IF EXISTS(SELECT name FROM tempdb..sysobjects WHERE name = @Name AND type = ‘U’)
BEGIN
SET @sql = ‘DROP TABLE tempdb..’ + @Name
–PRINT @sql
EXECUTE sp_executesql @sql
END
FETCH NEXT FROM @Cursor
INTO @Name
END
END
CLOSE @Cursor
DEALLOCATE @Cursor
GO
...............................................................................................
now my problem is when i execute the script it creates the table and Sp but when i execute the SP it gives the following error.
Msg 16924, Level 16, State 1, Procedure sp_DropTempTables, Line 15
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
(1 row(s) affected)
kindly help
Thanks in advance
September 5, 2012 at 12:07 pm
Duplicate post. Please do NOT cross-post.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply