Script for the database offline

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Script for the database offline

Author

Topic

vaddi
Posting Yak Master

145 Posts

Posted - 2006-08-23 : 15:31:27

Hello

I have got a script for checking the status of the database. When I check it for database which are offline, it is not working I mean its not showing up the result.what changes can be made.

USE MASTER
GO

DECLARE @dbname VARCHAR(100)
deCLARE @Status varchar(100)
Declare @Message VARCHAR(8000)

set @Message = ''

DECLARE dbname_cursor CURSOR FOR
SELECT name, Cast(Databasepropertyex(name,'Status=offline') as varchar) as status FROM sysdatabases order by name

OPEN dbname_cursor
FETCH NEXT FROM dbname_cursor INTO @dbname, @Status

WHILE @@FETCH_STATUS = 0
BEGIN
select @message = @message + @dbname + ' - ' + @Status + Char(13)
FETCH NEXT FROM dbname_cursor INTO @dbname, @Status
END
CLOSE dbname_cursor
DEALLOCATE dbname_cursor

print @message
EXEC master.dbo.xp_smtp_sendmail
@FROM = N'testsql2000@is.depaul.edu',
@TO = N'dvaddi@depaul.edu',
@server = N'smtp.depaul.edu',
@subject = N'Status of sqlserver!',
@type = N'text/html',
@message = @message

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-23 : 15:57:42

You aren't using DATABASEPROPERTYEX correctly. Here's how to do with that function:

SELECT name, DATABASEPROPERTYEX(name, 'Status')
FROM sysdatabases

Use a CASE statement or a WHERE clause to find those with Status = OFFLINE.

Here's how to do it with DATABASEPROPERTY:
SELECT name, DATABASEPROPERTY(name, 'IsOffline')
FROM sysdatabases

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources