October 11, 2008 at 4:39 am
hi friends,
Northwind Backup Plan4_200810111149.txt - this is for generating report file name whenever after completing database backup. so every day this file has been changed with date and time.
This report file i want include in email alert dynamically, so how to set the command in @messagefile at body of the content.
right now i am using static path like
@messagefile = N'c:\dbbackup\Northwind Backup Plan4_200810111149.txt '
how to add dynamic path above statement.
thanks
October 12, 2008 at 7:59 pm
are you getting the backup filename to the msdb database? or you want to get the date and time where the mail is to be delivered?
"-=Still Learning=-"
Lester Policarpio
October 12, 2008 at 10:51 pm
Hi thanks for reply me,
Through Database maintenance plan i had configured database backup and backup report text file , so backup was taken daily scuccessfully,and i want send email from server through SMTP.so i had execute the sql script for email alert, it was working fine,
i had execute the The following script
declare @rc int
exec @rc = master.dbo.xp_smtp_sendmail
@FROM= N'WJMN0130136',
@FROM_NAME= N'JG-IDC-SQL Server',
@TO= N'ananda.murugesan@zmail.ril.com',
@replyto = N'',
@cc= N'',
@BCC= N'',
@priority= N'NORMAL',
@subject= N' SQL Server Mail',
@message= N'Goodbye have a nice day, this is for test mail from MSSQL',
@messagefile= N'',
@type= N'text/plain',
@attachment= N'',
@attachments= N'',
@codepage= 0,
@server = N'servername'
select RC = @rc
go
i want script itself how to attach at @messagefile backup report text file automatically from particular disk path,
For example - @messagefile = N' C:\dbbackup\filename.txt', this file name has been changed daily whenever take backup. so i need send email the backup teport text file,
Thanks
October 13, 2008 at 12:25 am
hi sqldba,
pls give me sql script...
Thanks for help me...
October 13, 2008 at 5:32 am
ananda.murugesan (10/12/2008)
i want script itself how to attach at @messagefile backup report text file automatically from particular disk path,For example - @messagefile = N' C:\dbbackup\filename.txt', this file name has been changed daily whenever take backup. so i need send email the backup teport text file,
Thanks
I'm not sure how to do it with SQL-2000, because I'm more familiar with Database Mail in SQL-2005. I think you have to define the file at variable @Attachment instead of @MessageFile. Because you say the filename changes day-by-day, you have to define the filename dynamically. This will look similar to:
SET @Attachment = N' C:\dbbackup\filename' + convert(varchar(20), getdate(), 112) + '.txt'
October 14, 2008 at 12:28 am
Hi, thanks for reply
This is not working, pls tell me if any changes required. actuly file name like Northwind Backup Plan4_200810111149.txt it has been created after completion of backup every day.
I had execute the following script like
declare @rc int
declare @Attachment varchar(500)
SET @Attachment = N' D:\SQLmail\DB_Bkp\Northwind Backup Plan4_' + convert(varchar(20), getdate(), 112) + '.txt'
exec @rc = master.dbo.xp_smtp_sendmail
@FROM= N'jgsrvr28',
@FROM_NAME= N'JG-IDC-SQL Server',
@TO= N'ananda.murugesan@zmail.ril.com',
@replyto = N'',
@cc= N'',
@BCC= N'',
@priority= N'NORMAL',
@subject= N'Hello IDC SQL Server Mail @ jamnager',
@message= N'Goodbye have a nice day, this is for test mail from MSSQL',
@messagefile = N'@Attachment',
@type= N'text/plain',
--@attachment= N'',
@attachments= N'',
@codepage= 0,
@server = N'@servername'
select RC = @rc
go
thanx for reply.
October 14, 2008 at 12:57 am
You forgot to add the time to the filename. The CONVERT(VarChar(20),GetDate(),112) give you a date in notation in format yyyymmdd. You have to add the time to this part to create your full filename. When you create your backup with a scheduled job, that time will always be the same and you can hard-code that.
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = N'jgsrvr28',
@FROM_NAME = N'JG-IDC-SQL Server',
@TO = N'ananda.murugesan@zmail.ril.com',
@replyto = N'',
@cc = N'',
@BCC = N'',
@priority = N'NORMAL',
@subject = N'Hello IDC SQL Server Mail @ jamnager',
@message = N'Goodbye have a nice day, this is for test mail from MSSQL',
@messagefile = N'Attachment',
@type = N'text/plain',
@attachment = N'D:\SQLmail\DB_Bkp\Northwind Backup Plan4_' + convert(varchar(20), getdate(), 112) + '1149.txt',
@attachments = N'',
@codepage = 0,
@server = N'@servername'
select RC = @rc
go
P.S. Be sure to compare the generated string for the attachment with the actual path/filename.
October 14, 2008 at 1:03 am
It looks like you need to create the backup filename before you do the backup and then use the same filename.
In the example you copy and pasted from: convert(varchar(20), getdate(), 112) + '.txt'
The real backup filename was chosen when you started the backup.
Using a date function to recreate that same filename means you probably miss with some minutes.
So, you must find a way to create the backup filename before you start the backup, remember the filename, maybe in a variable, and then refer to that in the mail.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy