July 25, 2008 at 12:24 pm
I have a job set up with the following configration (below) - basically it emails the results of a query as an attachment. I want the column headings to show, but not the underlines that appear directly under the column headings after the attachment is opened. Can these be removed without losing the column headings?
@profile_name = 'profile',
@recipients = email address,
@query = 'EXEC stored procedure',
@subject = 'title',
@attach_query_result_as_file = 1,
@query_result_separator = '' ,
@query_result_no_padding = 1,
@query_result_header =1,
@query_attachment_filename = 'filename.CSV'
July 25, 2008 at 12:33 pm
I don't think there's a way to customize that.
Personally, I've found attaching the query results pretty useless. The formatting is horrible, and it ends up being pretty unreadable.
When I need to send query results, I use the FOR XML option on the query, then build it into the body of an HTML e-mail. That way, I can control the layout and appearance.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 25, 2008 at 12:36 pm
Interesting... do you have any good tutorials that have helped you with this?
GSquared (7/25/2008)
I don't think there's a way to customize that.Personally, I've found attaching the query results pretty useless. The formatting is horrible, and it ends up being pretty unreadable.
When I need to send query results, I use the FOR XML option on the query, then build it into the body of an HTML e-mail. That way, I can control the layout and appearance.
July 28, 2008 at 7:37 am
Nope. I figured it out on my own. Which is one of the reasons I say it's probably not an ideal solution, but it does work.
The way I do it is, in the proc that sends the e-mail, I define a varchar variable, then assign the results of the query to it with For XML in the query, then run a series of replace commands to replace the XML with HTML. Then I add that to the body of the e-mail.
declare @Grid varchar(max)
select @Grid =
(select Col1, Col2
from dbo.Table1
for XML RAW)
select @grid = '(table border="0" width="50%")' -- HTML table
+ '(tr)(td)Col1(/td)(td)Col2(/td)(/tr)' -- Header row
+replace(
replace(
replace(@grid, '(dbo.table1 col1="', '(tr)(td)'),
'" col2="', '(/td)(td)'),
'"/)', '(/td)(/tr)')
+ '(/table)'
select @body = @body + '
' + @grid
(Of course, the forum software won't allow the HTML tags in it, so I've use parentheses instead of the carets in this sample.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 26, 2020 at 2:57 pm
Use union all to construct your own columns headers
--here is the example:
declare @ProfileName as varchar(100)
SET @ProfileName = 'yourSQLprofilename'
DECLARE @tab CHAR(1) = CHAR(9)
declare @fileName varchar(100)
SET @fileName = 'filename' + convert(varchar(10),getdate(),110) + '.CSV'
EXEC msdb.dbo.sp_send_dbmail @profile_name = @ProfileName
,@recipients = 'emailaddress'
,@subject = 'query'
,@body_format = 'TEXT'
,@query = N'
SET ANSI_WARNINGS OFF
SET NOCOUNT ON
SELECT ''Instance_Name'' as Instance_Name, ''IP_Address'' as IP_Address, ''Port'' as Port
UNION ALL
SELECT SERVERPROPERTY(''ServerName'') as ''Instance_Name'',
exc.local_net_address as ''IP_Address'', convert(varchar(10),exc.local_tcp_port) as ''Port''
FROM sys.dm_exec_connections exc
--WHERE exc.session_id = @@SPID
'
,@attach_query_result_as_file = 1
,@query_attachment_filename = @fileName
,@query_result_separator = @tab
,@query_result_width = 32767
,@query_result_no_padding = 1
,@exclude_query_output = 1
,@query_result_header = 0
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply