Order by SP_SEND_MAIL

Question

Order by SP_SEND_MAIL

kirk.hughes 13236

SSC Journeyman

Points: 80
More actions
May 17, 2013 at 4:10 am

#293979

Morning
I am attempting to send a query result via DB mail and where as it is working fine, I want to order the file by a particular column. When I add the order by clause in I get the below error
Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 504
Query execution failed: Msg 207, Level 16, State 1, Server PROMETHEUS, Line 33
Invalid column name 'trantype'.
Msg 104, Level 16, State 1, Server PROMETHEUS, Line 33
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
My Query is as follows:
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'',
--@copy_recipients=N'',
@subject='',
@body='
',
@profile_name ='standard',
@query ='SET NOCOUNT ON; select ''[Polsdate]''
,''[Affinity]''
,''[Title]''
,''[Forename]''
,''[Surname]''
,''[Address1]''
,''[Address2]''
,''[Address3]''
,''[Address4]''
,''[Postcode]''
,''[DOB]''
,''[Cover]''
,''[Reg]''
,''[Cover Description]''
,''[trantype]'' union all
select top 100
cast([Polsdate] as varchar)
,cast([Affinity] as varchar)
,cast([Title] as varchar)
,cast([Forename] as varchar)
,cast([Surname] as varchar)
,cast([Address1] as varchar)
,cast([Address2] as varchar)
,cast([Address3] as varchar)
,cast([Address4] as varchar)
,cast([Postcode] as varchar)
,cast([DOB] as varchar)
,cast([Cover] as varchar)
,cast([Reg] as varchar)
,cast([Cover Description] as varchar)
,cast([Trantype] as varchar)
from db
where insertdate = CONVERT(VARCHAR(10), GETDATE(), 105) order by [trantype]' ,
@attach_query_result_as_file = 1,
@query_attachment_filename ='TEST.CSV',
@query_result_header=0,
@query_result_separator='',
@query_result_no_padding=1

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

your issue is from the union of the query;

the aliases for column names always come from the first query in the union, and right now all the columns have no alias, so you can't order by then.

substitute this query for your parameter:

this minor tweak would fix your issue:

@query ='SET NOCOUNT ON; select ''[Polsdate]'' AS Polsdate

,''[Affinity]'' AS Affinity

,''[Title]'' AS Title

,''[Forename]'' AS Forename

,''[Surname]'' AS Surname

,''[Address1]'' AS Address1

,''[Address2]'' AS Address2

,''[Address3]'' AS Address3

,''[Address4]'' AS Address4

,''[Postcode]'' AS Postcode

,''[DOB]'' AS DOB

,''[Cover]'' AS Cover

,''[Reg]'' AS Reg

,''[Cover Description]'' AS [Cover Description]

,''[trantype]'' AS trantype

union all

select top 100

cast([Polsdate] as varchar)

,cast([Affinity] as varchar)

,cast([Title] as varchar)

,cast([Forename] as varchar)

,cast([Surname] as varchar)

,cast([Address1] as varchar)

,cast([Address2] as varchar)

,cast([Address3] as varchar)

,cast([Address4] as varchar)

,cast([Postcode] as varchar)

,cast([DOB] as varchar)

,cast([Cover] as varchar)

,cast([Reg] as varchar)

,cast([Cover Description] as varchar)

,cast([Trantype] as varchar)

from db

where insertdate = CONVERT(VARCHAR(10), GETDATE(), 105) order by [trantype]' ,

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sony Francis @EY SSCommitted Points: 1539 More actions · Answer 2

Sony Francis @EY

SSCommitted

Points: 1539

May 17, 2013 at 5:32 am

#1616122

Give alias to to your set set of query

kirk.hughes 13236 SSC Journeyman Points: 80 More actions · Answer 3

That is brilliant. Thanks Lowell for this. Just replaced the code as you suggested and works an absolute treat. Means I can get this finished and no longer have to dial when on holiday to send the data.

Just need to add it to my overnight routines and job done

Massive thanks again