In T-SQL, use sp_Send_DBMail (link) to send e-mails.

John, I...

You can potentially shortcut John's method. If you add or subtract 10000 to an INT date held as YYYYMMDD, you are effectively adding or subtracting a year.

(If one of your...

there are 2 things - historically we've been told to use table variables as they can "sometimes" be in memory (no one has ever given me details on when...

This is interesting ... I usually head from table variables to temp tables. Are you finding improvements across the board (ie, for all temp tables), or just in certain scenarios?

That's some bad code there Mike.  I've seen some similar, but add a couple UNION statements and all with in an SSRS report.  Talk about slow code.

I've also...

The date which appears in your subject will depend entirely on the date on which the job is run. In words: "Give me the date for Sunday of the preceding...

GETDATE() updates every time you call it.

Oops, I meant Feb.

Change your DECLARE @Sub line as follows:

DECLARE @Sub NVARCHAR(255)
   ...

So ... if the job ran today, would you like the subject to be

'Transfer Report for the week of 23 March to 29 March'

?

• This reply was modified 6 years, 7 months ago by Phil Parkin.
• This reply was modified 6 years, 7 months ago by Phil Parkin.

Please define the logic for determining the numbers to use.

My apologies. This is better:

DECLARE @FilePath NVARCHAR(MAX) = N'C:\inetpub\ftpuser\WESA\ftpwashingtondc\DCWeeklyEmail\';
DECLARE @Filenames NVARCHAR(MAX);
DECLARE @EmailAddress VARCHAR(MAX);

SET @EmailAddress = '?';
SET @Filenames = @FilePath + N'dc_weekly' + N'.csv';

DECLARE @Sub NVARCHAR(255) = CONCAT('Example...

Modify the line

@Subject = 'Transfer Report',

to

@Subject = CONCAT('Transfer Report for the week of ', CONVERT(CHAR(10), GETDATE(), 101)),

and see whether that is getting you close to what you need.

When I send e-mails from SSIS, I usually use an ExecuteSQL task which runs sp_send_dbmail (link).

If you configure this, you have all the control you should ever need.

If you...