Attach file to email with current date stamp?

Question

Post reply

Attach file to email with current date stamp?

sean.r.mason

SSC Veteran

Points: 245

April 11, 2019 at 6:39 pm

#3131967

I’m trying to attach a file that is generated from an SSIS package and when created has the current date added to it.

Example: BH_2019-04-11.xlsx

I keep getting a syntax error when I add in the code to account for the date stamp.

Example: BH_' + 'CONVERT(VARCHAR(12), GETDATE(), 107' + '.xlsx

Error:

Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 517
Query execution failed: Msg 102, Level 15, State 1, Server HIDSCMSQL003, Line 1
Incorrect syntax near 'files'.

This code attaches files without the time stamp so I’m hoping someone can help me figure out how to get this to work.

Full code:

DECLARE @body VARCHAR(1000);
   DECLARE @bodyQuery VARCHAR(1000);
   DECLARE @file_attachments VARCHAR(MAX);
   DECLARE @Attachments VARCHAR(MAX);

   /* The table name needs to be fully qualified */
   SET @bodyQuery = 'See attached files'
   SET @Attachments = 'E:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Scripts\QMfiles\MedQuest\BH_' + 'CONVERT(VARCHAR(12), GETDATE(), 107' + '.xlsx) '

   EXEC msdb.dbo.sp_send_dbmail
   @profile_name = 'QM_Mail',
   @recipients = '##########',
   @from_address= '##########',
   --@copy_recipients ='##########',
   @subject = 'Provider Notification - Files past initial credentialing',
   @body = '',
   @query = @bodyQuery,
   @execute_query_database ='msdb',
   @file_attachments = @Attachments

Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 1

Well what happens when you run this,

DECLARE @Attachments VARCHAR(MAX);

SET @Attachments = 'E:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Scripts\QMfiles\MedQuest\BH_' + 'CONVERT(VARCHAR(12), GETDATE(), 107' + '.xlsx) '

SELECT @Attachments

sean.r.mason SSC Veteran Points: 245 More actions · Answer 2

It's reading the code as code not executing the date stamp.

(No column name)

E:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Scripts\QMfiles\MedQuest\BH_CONVERT(VARCHAR(12), GETDATE(), 107.xlsx)

sean.r.mason SSC Veteran Points: 245 More actions · Answer 3

When I run this code

DECLARE @Attachments VARCHAR(MAX);

SET @Attachments = 'E:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Scripts\QMfiles\MedQuest\BH_' + CONVERT(VARCHAR(12), GETDATE(), 107 + '.xlsx)'

SELECT @Attachments

I get a syntax error at the select statement.

Msg 156, Level 15, State 1, Procedure MSOW_AttachEmail_TEST, Line 8
Incorrect syntax near the keyword 'SELECT'.

Sue_H SSC Guru Points: 90860 More actions · Answer 4

It's just missing the closing parenthesis after 107 on formatting the date - should be GETDATE(), 107) + '.xlsx)'

Sue