May 30, 2012 at 3:23 am
Hi Guys
I am trying to send a mail from SQL server via dbmail, but i am facing a issue with attaching a file to the mail because of the dynamic file name.
Below is the code i am using
[font="Comic Sans MS"] EXEC sp_send_dbmail
@profile_name='Realtime01_mail',
@recipients='xyz@abc.com',
@subject='callcenter Audit Report',
@body='An attachement has been included in this email.',
@file_attachments= '"C:\Files\Reports\Call_Center_Audit_Reports\\" + "Audit_Report_" + REPLACE(SUBSTRING((DT_WSTR, 50) GETDATE(), 1, 10), "-","") + ".xls"'[/font]
but i am getting the error when i am running this cmd.
"Msg 22051, Level 16, State 1, Line 0
Attachment file "C:\Files\Reports\Call_Center_Audit_Reports\\" + "Audit_Report_" + REPLACE(SUBSTRING((DT_WSTR, 50) GETDATE(), 1, 10), "-","") + ".xls" is invalid.
"
File name :-
audit_report_20120530.xls
So `audit_report_` is going to remain same but dates will change.
Could you please suggest how i can achieve this in SQL 2005.
Many thanks in advance for your flawless support as ever.
Regards
NU
May 30, 2012 at 4:10 am
Your using coding language as DT-WSTR is a SSIS data type not a T-SQL data type which is what sp_send_dbmail is expecting.
You need to look at something which is in T-SQL like converting the date to a string or using the datepart functions to strip out the information you need.
Something like this CONVERT(NVARCHAR,GETDATE(),112)
May 30, 2012 at 5:05 am
Thanks !!! but Same error :exclamation:
Msg 22051, Level 16, State 1, Line 0
Attachment file "C:\Files\Reports\Call_Center_Audit_Reports\\" + "Audit_Report_" + REPLACE(SUBSTRING CONVERT(NVARCHAR,GETDATE(),112), 1, 10), "-","") + ".xls" is invalid.
May 30, 2012 at 5:08 am
you will need to build it into a variable then pass in that variable
declare @attach nvarchar(max)
set @attach = 'C:\fjnrfrgrjnjnfg'+CONVERT(NVARCHAR,GETDATE(),112)+'.xls'
sp_send_dbmail ......, @file_attachments = @attach
May 30, 2012 at 6:06 am
Ace !!!
it worked... thanks for the support...
Working code :-
declare @attach nvarchar(max)
set @attach = 'C:\Files\Reports\Call_Center_Audit_Reports\CallCenter_Audit_Report_'+CONVERT(NVARCHAR,GETDATE(),112)+'.xls'
EXEC sp_send_dbmail
@profile_name='NASA01_mail',
@recipients='gfgfgf@eee.com.sa',
@subject='callcenter Audit Report',
@body='report',
@file_attachments= @attach
Viewing 5 posts - 1 through 5 (of 5 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