Script related to SMTP mail - SQL Server Forums

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

Transact-SQL (2000)

Script related to SMTP mail

Author

Topic

vaddi
Posting Yak Master

145 Posts

Posted - 2006-08-23 : 12:18:46

Hello

Below down I have a script which gives the database name and its status , but in the result I am only getting the databasename. Can anyone suggest me how to get the status of the database also in the output .

GO
USE MASTER

DECLARE @dbname VARCHAR(100)
DECLARE dbname_cursor CURSOR FOR
SELECT name FROM sysdatabases
OPEN dbname_cursor
FETCH NEXT FROM dbname_cursor
INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @dbname AS DBName,
Databasepropertyex(@dbname,'Status') AS Status
FETCH NEXT FROM dbname_cursor
INTO @dbname
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 = @dbname
END
CLOSE dbname_cursor
DEALLOCATE dbname_cursor

Thanks
Dushyanth

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2006-08-23 : 12:28:16

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

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

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

vaddi
Posting Yak Master

145 Posts

Posted - 2006-08-23 : 12:35:42

I am getting Command completed succesfully in the sql analyzer.But in the mail , I am just receving the heading , status of sqlserver.

I am not able to either get the database name or status

Thanks for the help

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2006-08-23 : 12:39:04

Having never used xp_smtp_sendmail I'm not sure.
Add a
print @message before the xp_smtp_sendmail.
Chack and see if it contains the desired output.
Also I made a change or 2 try the new version.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

vaddi
Posting Yak Master

145 Posts

Posted - 2006-08-23 : 12:45:54

Thanks a lot for the Help. It is working

I have never used the xp_smtp_sendmail.
Now only I am trying it and get different messages from the database like, the status of the database , disk space on the server , whether the backups were successful or not.

Are there any suggestions you can give me regarding this.

Thanks once again

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2006-08-23 : 15:43:56

You WILL run into a size issue on @message if you have a bunch of databases but here is a quick hack

USE MASTER
GO

-- Now only I am trying it and get different messages from the database like,
-- the status of the database ,
-- disk space on the server ,
-- whether the backups were successful or not.

DECLARE @dbname VARCHAR(100)
Declare @Status varchar(100)
Declare @Message VARCHAR(8000)
create table #disk (
drive varchar(10),
mb_free varchar(200)
)

set nocount on

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

insert #disk
exec Master.dbo.xp_fixeddrives

select @message = @message + char(13) + Char(13) + 'Drive Space Available' + Char(13)

DECLARE GetDriveSize CURSOR FOR
select drive, mb_free from #disk

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

WHILE @@FETCH_STATUS = 0
BEGIN
select @message = @message + @dbname + ' has ' + @Status + ' MB Free Space' + Char(13)
FETCH NEXT FROM GetDriveSize INTO @dbname, @Status
END
Close GetDriveSize
Deallocate GetDriveSize

drop table #disk

select
bmf.physical_device_name,
RIGHT(bmf.physical_device_name, CHARINDEX('\', REVERSE(bmf.physical_device_name))-1) as physical_device_file,
bs.database_name,
bs.backup_start_date,
bs.backup_finish_date,
bs.type,
bs.first_lsn,
bs.last_lsn,
bs.checkpoint_lsn,
bs.database_backup_lsn
into #backup
from
msdb.dbo.backupset bs,
msdb.dbo.backupmediafamily bmf
where bmf.media_set_id = bs.media_set_id
and bs.backup_finish_date is not null
AND bs.type = 'D'
AND bs.backup_start_date = (select max(backup_start_date) from msdb.dbo.backupset WHERE type = bs.type and database_name = bs.database_name)
order by bs.database_name, bs.backup_start_date asc

select @message = @message + char(13) + Char(13) + 'Backup Status' + Char(13)

DECLARE GetBackup CURSOR FOR
select database_name, backup_finish_date from #backup order by database_name

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

WHILE @@FETCH_STATUS = 0
BEGIN
select @message = @message + @dbname + ' backup up on ' + @Status + Char(13)
FETCH NEXT FROM GetBackup INTO @dbname, @Status
END
Close GetBackup
Deallocate GetBackup

drop table #backup

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

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

vaddi
Posting Yak Master

145 Posts

Posted - 2006-08-24 : 12:47:14

Thanks for the scripts.

I was looking at the script to be fired , when the database is offline , the space on the disk is less than a particular amount of free space and if the backup fails.

What changes can I make.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources