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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply