February 24, 2011 at 9:31 am
I have the below script in a job, however, when it gets to a database that doesn't have the table (Errors_table), the job fails and quits. How can I make it skip these databases that are missing this table or make it continue to the other databases?
----------script--------------
Declare
@db_name nvarchar(50)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name like ('CW_%')
OPEN db_cursor
DECLARE @strSQL VARCHAR(4000)
FETCH NEXT FROM db_cursor INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @strSQL = 'DELETE [' + @db_name + '].dbo.Errors_table where TimeUtc < ' + CHAR(39) +CONVERT(VARCHAR(23),(GETDATE())-30, 121)+ CHAR(39)
EXECUTE (@strSQL)
FETCH NEXT FROM db_cursor INTO @db_name
END
CLOSE db_cursor
DEALLOCATE db_cursor
February 24, 2011 at 9:48 am
aroatenberry (2/24/2011)
I have the below script in a job, however, when it gets to a database that doesn't have the table (Errors_table), the job fails and quits. How can I make it skip these databases that are missing this table or make it continue to the other databases?----------script--------------
Declare
@db_name nvarchar(50)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name like ('CW_%')
OPEN db_cursor
DECLARE @strSQL VARCHAR(4000)
FETCH NEXT FROM db_cursor INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @strSQL = 'DELETE [' + @db_name + '].dbo.Errors_table where TimeUtc < ' + CHAR(39) +CONVERT(VARCHAR(23),(GETDATE())-30, 121)+ CHAR(39)
EXECUTE (@strSQL)
FETCH NEXT FROM db_cursor INTO @db_name
END
CLOSE db_cursor
DEALLOCATE db_cursor
You could add something like the following in your "SELECT @strSQL = string;
IF EXISTS (
SELECT [Name] FROM sys.objects WHERE [name] = 'Errors_Table'
)
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 24, 2011 at 9:51 am
Oops - didn't read that all the way. You would also have to build in a "Use" @db_name in there as well so that the string was something like;
USE DBName;
if exists .....
delete....
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 24, 2011 at 12:29 pm
It worked...thanks so much!
February 24, 2011 at 12:39 pm
Thanks for following up and you're welcome. 😀
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply