Email CSV file

• thunter 5669

  SSC-Addicted

  Points: 428

  More actions

  October 5, 2020 at 6:58 pm

  #3792997

  We have a stored procedure and job that will email out invoices daily. It all works great, however, we are trying to get the current date in the CSV filename.

  Here is the line we are using now for the filename:

  @query_attachment_filename = 'test.csv'

  We are thinking of 3 different formatting options:

  1. MMDDYYYY.csv
  2. Wynne-MMDDYYYY.csv
  3. MMDDYYYY-Wynne.csv

  Thank you for any help!

• Mike01

  SSChampion

  Points: 11395

  More actions

  October 5, 2020 at 8:02 pm

  #3793015

  Not sure what the question is.  If these files are saved from the end user, and they probably will be, then if they receive more than 1 day, they have the potential to overwrote the previous.  I like using YYYYMMDD as the format, so when they are saved, they can be sorted by data.  With that, I would also add HHMISS to the name.

  For better, quicker answers, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Steve Collins

  SSCrazy

  Points: 2157

  More actions

  October 6, 2020 at 12:22 am

  #3793054

  thunter 5669 wrote:

  We have a stored procedure and job that will email out invoices daily. It all works great, however, we are trying to get the current date in the CSV filename.

  Here is the line we are using now for the filename:

  @query_attachment_filename = 'test.csv'

  We are thinking of 3 different formatting options:

  <li style="list-style-type: none;">

  1. MMDDYYYY.csv
  2. Wynne-MMDDYYYY.csv
  3. MMDDYYYY-Wynne.csv

  <li style="list-style-type: none;">

  Thank you for any help!

  MMDDYYYY.csv

  select concat(replace(convert(char(10), getdate(), 101), '/', ''), '.csv');

  Wynne-MMDDYYYY.csv

  select concat('Wynne-', replace(convert(char(10), getdate(), 101), '/', ''), '.csv');

  MMDDYYYY-Wynne.csv

  select concat(replace(convert(char(10), getdate(), 101), '/', ''), '-Wynne.csv');

  Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

• Jeff Moden

  SSC Guru

  Points: 1004324

  More actions

  October 6, 2020 at 1:38 am

  #3793070

  Mike01 wrote:

  Not sure what the question is.  If these files are saved from the end user, and they probably will be, then if they receive more than 1 day, they have the potential to overwrote the previous.  I like using YYYYMMDD as the format, so when they are saved, they can be sorted by data.  With that, I would also add HHMISS to the name.

  @thunter 5669,

  THIS (above from Mike01) is the format that should be used if you're going to add the date file names (and I also urge you to include the time as suggested above, as well)!  You will seriously hate yourself (as will all others that have to work with the files) down the line if you follow the other formats that you propose.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)