Formatting query of TSQL

Question

I have the following TSQL query attempting to run the results and attach to an email but I am having some issues with the formatting of the query, can anyone advise as to why this is not working?

DECLARE @query_attachment_filename VARCHAR(100) = 'Reconciliation-Count-Ending-' + CONVERT(VARCHAR(10), GETDATE(), 112) + '.csv';

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'support',
@recipients = 'johnpaul@energy.co.uk;martin@energy.co.uk',
@subject = 'Reconciliation Count',
@query = N'SET ANSI_WARNINGS OFF;SET NOCOUNT ON;SELECT *
FROM (
    select a.New_MessageTypeCode
    from New_marketmessagein as a
    WHERE CreatedOn between DATEADD(DAY, –1, GETDATE()) and GETDATE()
) AS SRC
PIVOT (COUNT(New_MessageTypeCode ) FOR New_MessageTypeCode IN ([014R], [101], [101P], [101R], [102], [102P], [102R], [105],
[105L], [106D], [106E], [110], [111], [111A], [111L], [111R], [112], [112R], [112W], [114], [115], [115R], [116], [116A],
[116N], [116R],[117D],[117R], [122], [122R], [130D], [130R], [131], [137R], [261], [300], [300S], [300W], [301], [301N],
[303R], [305], [306], [306W], [307], [307W], [308], [310], [310W], [311], [320], [320W], [321], [330], [331], [332], [332W],
[341],[342], [352R], [591], [594], [595], [596], [597], [598], [601], [602], [700], [700W], [701], [701W])) As test ;',
@attach_query_result_as_file = 1,
@query_attachment_filename = @query_attachment_filename,
@query_result_header =0,
@query_result_separator = ' '

the error is:

Executed as user: NT AUTHORITY\NETWORK SERVICE. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed

Yes the error is: Executed as user: NT AUTHORITY\NETWORK SERVICE. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed. — Jay, Jul 25 '16 at 14:20
Does `NT AUTHORITY\NETWORK SERVICE` account have access to your database? — Chris Pickford, Jul 25 '16 at 15:01
Yes I am using same TSQL as from another job that is working only thing that I am changing is the query part of the code — Jay, Jul 25 '16 at 15:17
The minus sign in the DATEADD function is a different kind of minus (– vs -). — Razvan Socol, Jul 25 '16 at 15:53
Hi, see this similar question: http://stackoverflow.com/questions/15112849/sp-send-dbmail-executed-from-job-fails-with-query-result-attached-as-file — LuisR9, Jul 25 '16 at 17:08

score 0 · Answer 1 · answered Oct 16 '19 at 02:44

0

HI In your query remove the -1 in DATEADD(DAY, –1, GETDATE()) and type that again I think the character - is a non-ANSI character. Use Notepad to paste the query and edit the -1 and retype again.

answered Oct 16 '19 at 02:44

AmilaMGunawardana

1,604
2
13
32

Formatting query of TSQL

1 Answers1