Using sp_send_dbmail with file attachments

• mmullen 48200

  SSC Enthusiast

  Points: 155

  More actions

  December 2, 2014 at 11:50 am

  #313512

  I have a question on using sp_send_dbmail with file attachments. I know that you use this format:

  [ @file_attachments= ] 'file_attachments'

  How do you account for files that maybe named like NAMINGHERE_20141202_SomethingElse

  or of format *_YYYYMMDD_* ie to select this file in a batch file I would use something like *_2014*_* if it had to have the year 2014 in the name. How can you dynamically add file attachments when you may not know the full name ahead of time?

  Or could you use a query to populate results for what to add as file attachments? Or use entries into a table? Any suggestions would be great.

  Also, if you want to take characters as numbers and show padded zero's would I need to use a cast or convert? So far I have not had luck parsing date info out of another field and trying to get the 02 for the date rather than just 2.

• ZZartin

  SSC-Dedicated

  Points: 30900

  More actions

  December 2, 2014 at 12:38 pm

  #1762205

  The file attachment doesn't take wild cards so you'd need to know the exact file name somehow, there's a number of ways to get that.

  As far as date formatting http://msdn.microsoft.com/en-us/library/ms187928.aspx so if you wanted YYYYMMDD CONVERT(varchar, <date field>, 112)

  Or to left pad a field, RIGHT('00' + <field>, 2)