how to set dynamic path statement in sql server 2000

Question

how to set dynamic path statement in sql server 2000

SQL Galaxy

One Orange Chip

Points: 29472
More actions
October 11, 2008 at 4:39 am

#71363

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 1

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

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

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

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 3

hi sqldba,

pls give me sql script...

Thanks for help me...

HanShi SSC-Dedicated Points: 33507 More actions · Answer 4

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'

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 5

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.

HanShi SSC-Dedicated Points: 33507 More actions · Answer 6

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.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

jorn.jensen SSC Journeyman Points: 95 More actions · Answer 7

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.