Hello
I have got a script which gives the mail to the dba mail box when database backup fails.
In the script I want to make a change so that I get the particular database name , on what ever database i implement.
Can you tell me some suggestions.
The script I am using is :
use master
go
alter PROCEDURE dbo.SendMail
@.to VARCHAR(255),
@.subject VARCHAR(255),
@.message VARCHAR(8000)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@.rv INT,
@.from VARCHAR(64),
@.server VARCHAR(255);
SELECT
@.from = 'testsql2000@.is.depaul.edu',
@.server = 'smtp.depaul.edu';
select @.message = @.message + char(13) + Char(13) + @.@.servername + '-'+ db_name()+ '-' + 'Backup Status Failed' + Char(13)
EXEC @.rv = dbo.xp_smtp_sendmail
@.to = @.to,
@.from = @.from,
@.message = @.message,
@.subject = @.subject,
@.server = @.server;
END
GO
After the above script is run the following should be given in the 2nd step when
the backup jobs are scheduled
exec master.dbo.sendmail
@.to = 'dvaddi@.depaul.edu',
@.subject =' Test sqlserver 2000',
@.message = '' ;
Thanks
Sorry I don′t understand you issue, what do you want to achieve ?HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||
I want to get the database name also in the mail I receive along with the server name.
For example if I execute it on Test database in the mail I should get : Test server (servername) - Test (database name ) - Backup status.
and if I execute on Test2000 database in the mail I should get : Testserver(servername ) - Test2000(database name)- bakcupstatus.
I am able to get the servername and the backup status. But for the database name I am getting just 'master'
Thanks
|||When I have to that, I always script up the job which I prepared for a particular database and prepares creation statements for them. So the script which is produced if you script the job from SQL Agent can be tweaked to take a variable which is substituted during the creation of the job, something like:
DECLARE DatabaseName VARCHAR(200)
sp_add_jobstep param1, @.Command = Here goes your command to execute with the appropiate statements including the param to be substitued with the databasename
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||There are three different commands in execute sql mail:
exec sys.xp_startmail
exec sys.xp_sendmail {[@.recipients =] 'recipients [;...n]'}
[,[@.message =] 'message']
[,[@.query =] 'query']
[,[@.attachments =] 'attachments [;...n]']
[,[@.copy_recipients =] 'copy_recipients [;...n]'
[,[@.blind_copy_recipients =] 'blind_copy_recipients [;...n]'
[,[@.subject =] 'subject']
[,[@.type =] 'type']
[,[@.attach_results =] 'attach_value']
[,[@.no_output =] 'output_value']
[,[@.no_header =] 'header_value']
[,[@.width =] width]
[,[@.separator =] 'separator']
[,[@.echo_error =] 'echo_value']
[,[@.set_user =] 'user']
[,[@.dbuse =] 'database']
exec sys.xp_stopmail
