August 5, 2010 at 8:57 am
This a the basic statement
USE master;
GO
SET NOCOUNT ON;
GO
DECLARE AllDatabases CURSOR FOR
SELECT name FROM sys.databases WHERE database_id > 4
OPEN AllDatabases
DECLARE @DBNameVar NVARCHAR(128)
DECLARE @Statement NVARCHAR(600)
DECLARE @ADATE DATETIME
FETCH NEXT FROM AllDatabases INTO @DBNameVar
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @ADATE =(DATEADD(d,-60,getdate()))
PRINT N'CHECKING DATABASE ' + @DBNameVar
SET @Statement=N'DELETE FROM WORKLISTLOG WHERE CREATIONTIME <= @ADATE'
EXEC sp_executesql @Statement
PRINT CHAR(13) + CHAR(13)
FETCH NEXT FROM AllDatabases INTO @DBNameVar
END
CLOSE AllDatabases
DEALLOCATE AllDatabases
GO
SET NOCOUNT OFF;
GO
This is the error:
CHECKING DATABASE ReportServer
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@ADATE".
August 5, 2010 at 9:32 am
DECLARE @ParmDefinition nvarchar(500);
SET @ParmDefinition = N'@Date datetime'
DECLARE @DBNameVar NVARCHAR(128)
DECLARE @Statement NVARCHAR(600)
DECLARE @ADATE DATETIME
SET @ADATE =(DATEADD(d,-60,getdate()))
SET @Statement=N'SELECT @DATE'
EXEC sp_executesql @Statement, @ParmDefinition, @Date = @ADate
August 5, 2010 at 9:58 am
I appreciate the suggestion by I need to be able to apply this to all my database in one statement. Can you help my original statement
August 5, 2010 at 10:02 am
I suppose I could have done this just as easily to your original, but sometimes a simple example shows the technique better. Just as long as you understand what changes were made and whey, that's the important thing.
This should do:
USE master;
GO
SET NOCOUNT ON;
GO
DECLARE AllDatabases CURSOR FOR
SELECT name FROM sys.databases WHERE database_id > 4
OPEN AllDatabases
DECLARE @DBNameVar NVARCHAR(128)
DECLARE @Statement NVARCHAR(600)
DECLARE @ADATE DATETIME
DECLARE @ParmDefinition nvarchar(500);
FETCH NEXT FROM AllDatabases INTO @DBNameVar
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @ADATE =(DATEADD(d,-60,getdate()))
PRINT N'CHECKING DATABASE ' + @DBNameVar
SET @Statement=N'DELETE FROM WORKLISTLOG WHERE CREATIONTIME <= @DATE'
SET @ParmDefinition = N'@Date datetime'
EXEC sp_executesql @Statement, @ParmDefinition, @Date = @ADate
PRINT CHAR(13) + CHAR(13)
FETCH NEXT FROM AllDatabases INTO @DBNameVar
END
CLOSE AllDatabases
DEALLOCATE AllDatabases
GO
SET NOCOUNT OFF;
GO
August 5, 2010 at 10:16 am
I worked the solution in but I get this error
CHECKING DATABASE jaime
Msg 208, Level 16, State 1, Line 1
Invalid object name 'WORKLISTLOG'.
August 5, 2010 at 10:18 am
It's because you are connected to your master DB. Build out your SQL statement to reference your table with databasename.schemaname.tablename.
August 5, 2010 at 10:25 am
I undestand that part but this is what the cursor is built for, it when check the database in that instance. How can I get though this. The concept is to execute it from any database.
Please correct me where needed.
August 5, 2010 at 10:27 am
Right, but nowhere in your code do you reference the target databases (outside of your print statement). Your DELETE statement needs to reference the target database name using the naming convention Database.schema.table.
Change your SET @Statement statement to include the database reference.
August 5, 2010 at 10:30 am
Got it, Thanks
August 5, 2010 at 10:41 am
This solution work but it only deletes the rows from the database being reference.
USE master;
GO
SET NOCOUNT ON;
GO
DECLARE AllDatabases CURSOR FOR
SELECT name FROM sys.databases WHERE database_id > 4
OPEN AllDatabases
DECLARE @DBNameVar NVARCHAR(128)
DECLARE @Statement NVARCHAR(600)
DECLARE @ADATE DATETIME
DECLARE @ParmDefinition nvarchar(500);
FETCH NEXT FROM AllDatabases INTO @DBNameVar
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @ADATE =(DATEADD(d,-60,getdate()))
PRINT N'CHECKING DATABASE ' + @DBNameVar
SET @Statement=N'DELETE FROM dbo.dbo.WORKLISTLOG' --WHERE CREATIONTIME <= @DATE'
SET @ParmDefinition = N'@Date datetime'
EXEC sp_executesql @Statement, @ParmDefinition, @Date = @ADate
PRINT CHAR(13) + CHAR(13)
FETCH NEXT FROM AllDatabases INTO @DBNameVar
END
CLOSE AllDatabases
DEALLOCATE AllDatabases
GO
SET NOCOUNT OFF;
GO
August 5, 2010 at 10:57 am
okay, I appreciate all the time an effort from everyone who participated, this is what I ended up with as a test.
USE master;
GO
SET NOCOUNT ON;
GO
DECLARE AllDatabases CURSOR FOR
SELECT name FROM sys.databases WHERE name not in ('model','master','msdb','tempdb','ReportServer','ReportServerTempDB')--database_id > 4
OPEN AllDatabases
DECLARE @DBNameVar NVARCHAR(128)
DECLARE @Statement NVARCHAR(600)
DECLARE @ADATE DATETIME
DECLARE @ParmDefinition nvarchar(500);
FETCH NEXT FROM AllDatabases INTO @DBNameVar
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @ADATE =(DATEADD(d,-60,getdate()))
PRINT N'CHECKING DATABASE ' + @DBNameVar
SET @Statement=N'DELETE FROM '+ @DBNameVar+'.dbo.WORKLISTLOG' --WHERE CREATIONTIME <= @DATE''
SET @ParmDefinition = N'@Date datetime'
EXEC sp_executesql @Statement, @ParmDefinition, @Date = @ADate
PRINT CHAR(13) + CHAR(13)
FETCH NEXT FROM AllDatabases INTO @DBNameVar
END
CLOSE AllDatabases
DEALLOCATE AllDatabases
GO
SET NOCOUNT OFF;
GO
August 5, 2010 at 11:01 am
Jaime , how many databases do u have in your system??
August 5, 2010 at 11:02 am
I appreciate the help from everyone who participated, this is what I end with as a solution, please note or make any suggestion for improvement.
USE master;
GO
SET NOCOUNT ON;
GO
DECLARE AllDatabases CURSOR FOR
SELECT name FROM sys.databases WHERE name not in ('model','master','msdb','tempdb','ReportServer','ReportServerTempDB')--database_id > 4
OPEN AllDatabases
DECLARE @DBNameVar NVARCHAR(128)
DECLARE @Statement NVARCHAR(600)
DECLARE @ADATE DATETIME
DECLARE @ParmDefinition nvarchar(500);
FETCH NEXT FROM AllDatabases INTO @DBNameVar
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @ADATE =(DATEADD(d,-60,getdate()))
PRINT N'CHECKING DATABASE ' + @DBNameVar
SET @Statement=N'DELETE FROM '+ @DBNameVar+'.dbo.WORKLISTLOG' --WHERE CREATIONTIME <= @DATE''
SET @ParmDefinition = N'@Date datetime'
EXEC sp_executesql @Statement, @ParmDefinition, @Date = @ADate
PRINT CHAR(13) + CHAR(13)
FETCH NEXT FROM AllDatabases INTO @DBNameVar
END
CLOSE AllDatabases
DEALLOCATE AllDatabases
GO
SET NOCOUNT OFF;
GO
August 5, 2010 at 11:05 am
In this server I have around 24 databases with similar schema.
August 5, 2010 at 11:45 am
Jaime E. Maccou (8/5/2010)
...please note or make any suggestion for improvement.
How about simply:
execute sp_MSforeachdb 'IF ''?'' NOT IN (''model'',''master'',''msdb'',''tempdb'',''ReportServer'',''ReportServerTempDB'') BEGIN PRINT ''Checking Database ?'';DELETE FROM [?].dbo.WORKLISTLOG;END;'
Note: those are all single-quotes in this code.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply