Hi greenie,
DB Mail objects cannot be scripted via Scripter class. We're looking into adding this functionality in a future release.
|||Thanks - I would certainly appreciate the functionality given the DB Mail configuration can get fairly complex. I guess my workaround will be to query the tables and output to my recovery fileHi greenie,
DB Mail objects cannot be scripted via Scripter class. We're looking into adding this functionality in a future release.
|||Thanks - I would certainly appreciate the functionality given the DB Mail configuration can get fairly complex. I guess my workaround will be to query the tables and output to my recovery filesqlHello,
I would like to have a script , that sends a mail to the dba mail box when the database backup fails . The mail should be sent to the SMTP server.
I have the script which gives the whole output of the backup status but I would like it to change so that it fires only when a backup fails. Please suggest me what to do..
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
Thanks
How are you performing the backup? If you are using say SQLAgent jobs then why don't you just include an additional step at the end after the backup step which fires only if the backup fails. This can email the details and you don't even have to use extended SPs etc. I believe that SQLAgent has ways to send emails. Otherwise, you will have to check for status of backup also in the msdb tables since you cannot create triggers on system tables.Is there a way I can on any exception, call my .NET code using System.Mail in SSIS 2005 and use my custom code to send emails rather than the Mail component? The mail component sucks, you can't change the look & feel of the email being sent using CSS but if I can fire off my own code to send the email, then that would work great.
To ensure your script task fails on any error, you would create an On Error event handler and add the task in there.
The script task can use just about any .Net component but the system stuff is there already. Try System.Net.Mail for a start and see if that does what you want. If not you may have to get a third-party component, then just add a reference to it. For new components you will need to place them in the framework folder to be able to add a reference e.g. C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\
|||So I would just do an OnError then a script component to call my .Net email function? Then just pass through the System variables to the script variable? cool.|||Yes, event handlers are great for this. No more messing about with workflow constraints for this type of package (container) wide handling.