2

I am new to SQL administration and have been tasked with creating some nightly jobs that send an email with certain details contained within a spreadsheet and so far I have the following:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'support',
@recipients = 'test@mail.co.uk',
@subject = 'Post Code Analysis',
@query = N'SET ANSI_WARNINGS OFF;SET NO COUNT ON;select substring(PostOfficeBox,1,4) as ''Postcode''
, COUNT(CASE WHEN New_accounttype = 1 THEN New_AccountType ELSE NULL END) as ''New Connections''
, COUNT(CASE WHEN New_accounttype = 2 THEN New_AccountType ELSE NULL END) as ''Domestic Metered''
, COUNT(CASE WHEN New_accounttype = 3 THEN New_AccountType ELSE NULL END) as ''Commercial Metered Low''
, COUNT(CASE WHEN New_accounttype = 4 THEN New_AccountType ELSE NULL END) as ''Commerical Metered High''
, COUNT(CASE WHEN New_accounttype = 5 THEN New_AccountType ELSE NULL END) as ''Domestic Keypad''
, COUNT(CASE WHEN New_accounttype = 6 THEN New_AccountType ELSE NULL END) as ''Generator''
, COUNT(CASE WHEN New_accounttype = 7 THEN New_AccountType ELSE NULL END) as ''Commercial Keypad''
from be_crm4_MSCRM.dbo.AccountExtensionBase as a
INNER JOIN be_crm4_MSCRM.dbo.CustomerAddressBase as b ON a.AccountId =     b.ParentId
where New_AccountStage = 7
and AddressTypeCode = 1
and substring(PostOfficeBox,1,2) = ''BT''
group by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)
order by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4);',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'pca-test.csv',
@query_result_header =0,
@query_result_separator = ' '

In the @query_attachment_filename I would like to append the date and have tried using SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] and appending this to the file name as follows:

@query_attachment_filename = 'pca-test-' + SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] + '.csv'

Any advice would be greatly appreciated

5

Declare a variable first - but use format 112 for this. Like this:

DECLARE @query_attachment_filename NVARCHAR(255) = 
    N'pca-test-' + 
    CONVERT(NCHAR(8), GETDATE(), 112) + 
    N'.csv';

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'support',
...
@attach_query_result_as_file = 1,
@query_attachment_filename = @query_attachment_filename,
@query_result_header =0,
@query_result_separator = ' '
4

In SQL Server, a stored procedure's argument can be either a string literal or a variable. It certainly cannot be a string expression, like in your attempt. To resolve your issue, you could declare a variable, assign your expression to it and then pass the variable to the sp_send_dbmail stored procedure:

DECLARE @myfilename nvarchar(255);

SET @myfilename = 'pca-test-' + CONVERT(VARCHAR(10), GETDATE(), 103) + '.csv';

EXEC msdb.dbo.sp_send_dbmail
  …
  @query_attachment_filename = @myfilename,
  …

Note also that there are other issues with your method:

  1. You do not need to use SELECT with the CONVERT function. You can see above that I simply omitted both the SELECT and the [DD/MM/YYYY] column alias. However, if you do use a SELECT in an expression, you need to enclose it in round brackets, like this:

    … + (SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]) + …
    

    Aliasing the result of CONVERT is perfectly unnecessary in this case, though – so you can omit the AS [DD/MM/YYYY] part.

  2. CONVERT with the 103 style will return a string with slashes. However, since you are most likely doing this in Windows, you cannot specify slashes in a file name. You will need to either remove the slashes or replace them with a different delimiter. You can use the REPLACE function in both cases. The following, for instance, replaces the slashes with hyphens:

    REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '-')

    If you would like to just remove them, use '' as the third argument.

  • As this is 2015, the odds are pretty good that he is dealing with a 2012+ sever, in which case Format is a better option than CONVERT. – jmoreno Jul 6 '15 at 0:56
  • @jmoreno Do you have any idea how many people are still running 2000, 2005, 2008, and 2008 R2? Also, what makes FORMAT() "better"? It's certainly not performance. – Aaron Bertrand Jul 6 '15 at 1:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.