Help - Script that checks for OFFLINE Database

Hello ,

I have got a script which checks for the databases that are offline. and mails the dba when any of the database is offline.

When I schedule the job , for every hour , it gives me an blank mail only with the subject ' status of database on testsql' even though no databases are offline.

So how can I change the script , so that it mails the dba only when a database is offline even though its scheduled every hour or half an hour.

The script is:

Set NoCount on
DECLARE @.dbname VARCHAR(100)
deCLARE @.Status varchar(100)
Declare @.Message VARCHAR(8000)
DECLARE @.date varchar(100)
set @.date = convert(varchar(100), getdate(),109)
set @.Message = ''DECLARE dbname_cursor
CURSOR FOR SELECT [name], CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Statu s'))
as [Status] FROM master..sysdatabases WHERE CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Statu s')) = 'OFFLINE'
order by name
OPEN dbname_cursor
FETCH NEXT FROM dbname_cursor INTO @.dbname, @.Status
WHILE
@.@.FETCH_STATUS = 0
BEGIN select @.message = @.message + @.@.Servername + '-' + @.dbname + ' - ' + @.Status + Char(13)+ - + @.date
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 the Database on Testsqlserver!',
@.type = N'text/html',
@.message = @.message

ThanksThe problem is there is no conditional around the exec master.dbo.xp_smtp_sendmail. Try this. At the beginning, check to see if there are any databases offline with something like:

if exists (select * from sysdatabases where databasepropertyex (name, 'Status') = 'OFFLINE'
begin
>>your code<<
end|||sorry to ask, but can you be more clear on what you have mentioned. Like after giving the statement that you have given, what I should be giving next as part of my code.

Thanks|||Cut-and-paste the code from your post, and place it into the code from MCrowley's post in place of the ">>your code<<" marker.

-PatP|||It doesnot seem to be working. It still gives out the blank email , when no database is offline.

Thanks|||Looks like I missed a close parenthesis, but since you got it to parse, looks like you found that problem with ease. Post what you have, now.|||The code that now I am executing is :

if exists (select * from sysdatabases where databasepropertyex (name, 'Status') = 'OFFLINE')
--Set NoCount on
DECLARE @.dbname VARCHAR(100)
deCLARE @.Status varchar(100)
Declare @.Message VARCHAR(8000)
DECLARE @.date varchar(100)
set @.date = convert(varchar(100), getdate(),109)
set @.Message = ''
--DECLARE dbname_cursor
--CURSOR FOR SELECT [name], CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Statu s'))
--as [Status] FROM master..sysdatabases WHERE CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Statu s')) = 'OFFLINE'
--order by name
--OPEN dbname_cursor
--FETCH NEXT FROM dbname_cursor INTO @.dbname, @.Status
--WHILE
--@.@.FETCH_STATUS = 0
BEGIN select @.message = @.message + @.@.Servername + '-' + @.dbname + ' - ' + @.Status + Char(13)+ '- ' + @.date
--FETCH NEXT FROM dbname_cursor INTO @.dbname, @.Status
--END
--CLOSE dbname_cursor
--DEALLOCATE dbname_cursor
if (@.message <> '')
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 the Database on Testsqlserver!',
@.type = N'text/html',
@.message = @.message
end

So ,if the database is offline I am getting the mail correctly.

But even if no database is offline, it is saying....command completed successfully and I am getting a blank mail.

Thanks|||How about this

if exists (select * from sysdatabases where databasepropertyex (name, 'Status') = 'OFFLINE')
begin
Set NoCount on
DECLARE @.dbname VARCHAR(100)
deCLARE @.Status varchar(100)
Declare @.Message VARCHAR(8000)
DECLARE @.date varchar(100)
set @.date = convert(varchar(100), getdate(),109)
set @.Message = ''DECLARE dbname_cursor
CURSOR FOR SELECT [name], CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Statu s'))
as [Status] FROM master..sysdatabases WHERE CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Statu s')) = 'OFFLINE'
order by name
OPEN dbname_cursor
FETCH NEXT FROM dbname_cursor INTO @.dbname, @.Status
WHILE
@.@.FETCH_STATUS = 0
BEGIN select @.message = @.message + @.@.Servername + '-' + @.dbname + ' - ' + @.Status + Char(13)+ ‘- ‘ + @.date
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 the Database on Testsqlserver!',
@.type = N'text/html',
@.message = @.message
END|||Hello Mcrowley,

I have got the script working. But I am using a different one.The script I have got was,

SET NOCOUNT ON

DECLARE @.Msg VARCHAR(8000)

SELECT @.Msg = ISNULL(@.Msg + CHAR(13), '') + 'Database ' + z.Name + ' on machine ' + z.ServerName + ' is ' + z.Status + ' at ' + z.Now + '.'
FROM (
SELECT TOP 100 PERCENT @.@.SERVERNAME ServerName,
Name,
CONVERT(VARCHAR, DATABASEPROPERTYEX(Name, 'Status')) Status,
CONVERT(VARCHAR, GETDATE(), 109) Now
FROM master..sysdatabases
WHERE status & 512 = 512
ORDER BY Name
) z

PRINT @.Msg

IF @.Msg IS NOT NULL
EXEC master.dbo.xp_smtp_sendmail
@.FROM = N'from address',
@.TO = N'to address',
@.server = N'smtpaddress',
@.subject = N'Status of the Database on Testsqlserver!',
@.type = N'text/html',
@.message = @.Msg