SQL Server Performance Forum – Threads Archive

Help with SMTP Script

Hello I have got a script which gives the database name and status to the mail box. How can I modify it , to get the mail only when a database is offline.
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’) 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’[email protected]‘,
@TO = N’[email protected]‘,
@server = N’smtp.depaul.edu’,
@subject = N’Status of sqlserver!’,
@type = N’text/html’,
@message = @message
Thanks

---

Please don’t multi-post. You’ve got an answer here:http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=16298 —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

---

]]>