Attach file to email with current date stamp?

• sean.r.mason

  SSC Veteran

  Points: 245

  More actions

  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

• ZZartin

  SSC-Dedicated

  Points: 30921

  More actions

  April 11, 2019 at 6:45 pm

  #3132061

  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

  April 11, 2019 at 7:44 pm

  #3133537

  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

  April 11, 2019 at 7:54 pm

  #3133746

  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

  April 11, 2019 at 8:04 pm

  #3134037

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

   

  Sue