July 26, 2011 at 12:56 pm
Hello Everybody,
I am using sp_send_dbmail to send email to the user for the result of some query without header or result output such as (number of rows affected) on the text file as a attachment. I was able to remove the header by providing the parameter @query_result_header=0 and it worked. But tried exclude_query_output=1 to remove the result output but its not working.
My ultimate intetion here is to generate text file from the query (TSQL) with data only(no header or result) and send it to the user.
Please help me on this.
Thanks in advace for your time.
July 26, 2011 at 1:02 pm
If you use SET NOCOUNT ON; at the top of the query the n rows affected message should be gone.
I'm not sure I understand the rest of the request, can you clarify?
July 26, 2011 at 1:13 pm
I just want to avoid the rows affected message on the text file generated by the query. But set nocount on remove the rows affected message in SSMS but doen't remove from the text file.
My TSQL look like the following:
DECLARE @cmd VARCHAR(300)
SET @cmd=N'SELECT * FROM TABLE'
EXEC msdb.dbo.sp_send_dbmail
@recipients = email_address ,
@subject = subject,
@body = body,
@query =@cmd,
@attach_query_result_as_file=1,
@query_attachment_filename='name_of_the_file.txt',
@query_result_header=0,
@query_result_width=127,
@exclude_query_output=1,
@append_query_error=0,
@query_no_truncate=1
I want data only on the file 'name_of_the_file.txt' (no rows affected message on this)
July 26, 2011 at 1:22 pm
That works for me :
@query = 'SET NOCOUNT ON; SELECT * FROM [DB].dbo.RPT_Space_Warnings WHERE DateWarning = DATEADD(D , 0 , DATEDIFF(D , 0 , GETDATE()))' ,
July 26, 2011 at 2:53 pm
That exactly what I was looking for. Thanks Ninja's_RGR'us for your quick response and time.
As this one is solved I came up with the other issue 😀 . As I was trying to generate the file with fixed length(which will have space at the end to make the string that long(for eg 127 character long)). But the problem is when i do @query_result_no_padding=1 it truncates all the spaces that I need to make it 127 long and when i do @query_result_no_padding=0 it gives me 127 long character with spaces but will have blank line between two line which is not desired. Please help if possbile 🙁
Thanks Again.
July 26, 2011 at 3:07 pm
Never tried this, no idea how to do it at the moment.
July 27, 2011 at 5:23 am
So far as I know (and I could be wrong) Padding Off is Padding Off. You can't have padding in the query result and not between the lines. It's a binary decision.
A few options to try:
1) Create the file with SSIS and then use a Send Mail Task to send the file. Not only will it allow blank spaces in the file (Ragged Right works better than Fixed Width, BTW), but there will be no blank lines between query results.
2) Leave the padding off, but replace your spaces with another character (like a "hidden" ansi character). This option, however, could cause problems when the other guy tries to import the file into another system.
July 27, 2011 at 9:30 am
Thanks Brandie.
I think i need to go with the option 1. But I am curious about the hidden ansi character. Would you please explain what is ansi hidden character and how do we implement it?
Thanks in advance.
July 27, 2011 at 11:04 am
An ANSI character set is a set of numbers that represent every letter in the alphabet and every number from 0-9. They also represent punctuation marks, spaces, tabs, line breaks, etc.
The concept of the "hidden ANSI character" is that there are some characters that you just can't see in text (such as spaces and tabs and breaks) that exist in the text. This has caused no end of problems with SQL Server and data where an ANSI character has gotten embedded in a string that the DBA can't see and isn't able to find because of that. The solution to that problem is to do a search on the string to find the "hidden ANSI character" and fix it.
In your case, you might be deliberately adding in the ANSI to fix a problem. But, as I said earlier, this can cause problems.
More details on ANSI can be found here: http://www.alanwood.net/demos/ansi.html, http://ascii-table.com/ansi-table.php, and http://ascii-table.com/ansi-codes.php
I don't know if the following BOL link will work, but ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_2devguide/html/86a8adda-c5ad-477f-870f-cb370c39ee13.htm <- try that.
July 28, 2011 at 6:40 am
Walton (7/27/2011)
Thanks Brandie.I think i need to go with the option 1. But I am curious about the hidden ansi character. Would you please explain what is ansi hidden character and how do we implement it?
Thanks in advance.
Have you tried playing with HTML body format messages? The flexibility it offers is a real eye opener:
-- make some data to play with:
SET NOCOUNT ON
DROP TABLE #Table1
CREATE TABLE #Table1 (SupplierID INT, SupplierName VARCHAR(50), Location VARCHAR(20))
INSERT INTO #Table1 (SupplierID, SupplierName, Location)
SELECT 1, 'Manzis Pie And Mash ', 'London' UNION ALL
SELECT 2, 'Zillis Deep-fried Mars Bars', 'Edinburgh'
DROP TABLE #Table2
CREATE TABLE #Table2 (TastingNotes VARCHAR(50), [Count] INT, Percentage DECIMAL(5,2))
INSERT INTO #Table2 (TastingNotes, [Count], Percentage)
SELECT 'Traditional South-East fare', 233, 75 UNION ALL
SELECT 'Feel your arteries thickening as you eat', 3, 12
-- set up the mail job:
DECLARE @tableHTML VARCHAR(MAX)
SET @tableHTML =
'<H3>First query output</H3>' +
'<table border="1">' +
'<tr>
<th>SupplierID</th>
<th>SupplierName</th>
<th>Location</th>
</tr>' +
CAST((
SELECT
td = SupplierID, '',
td = SupplierName, '',
td = Location, ''
FROM #Table1
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX)) +
'</table>' +
'<H3>Second query output</H3>' +
'<table border="1">' +
'<tr>
<th>TastingNotes</th>
<th>Number of Exceptions</th>
<th>Percentage Satisfaction</th>
</tr>' +
CAST((
SELECT
td = TastingNotes, '',
td = [Count], '',
td = Percentage, ''
FROM #Table2
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX)) +
'</table>'
-- run the mail job:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = '', -- your value
@recipients='', -- your value
@subject = 'A nicely-formatted email body with embedded data',
@body = @tableHTML,
@body_format = 'HTML'
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 28, 2011 at 6:54 am
ChrisM@Work (7/28/2011)
Walton (7/27/2011)
Thanks Brandie.I think i need to go with the option 1. But I am curious about the hidden ansi character. Would you please explain what is ansi hidden character and how do we implement it?
Thanks in advance.
Have you tried playing with HTML body format messages? The flexibility it offers is a real eye opener:
-- make some data to play with:SET NOCOUNT ON
DROP TABLE #Table1
CREATE TABLE #Table1 (SupplierID INT, SupplierName VARCHAR(50), Location VARCHAR(20))
INSERT INTO #Table1 (SupplierID, SupplierName, Location)
SELECT 1, 'Manzis Pie And Mash ', 'London' UNION ALL
SELECT 2, 'Zillis Deep-fried Mars Bars', 'Edinburgh'
DROP TABLE #Table2
CREATE TABLE #Table2 (TastingNotes VARCHAR(50), [Count] INT, Percentage DECIMAL(5,2))
INSERT INTO #Table2 (TastingNotes, [Count], Percentage)
SELECT 'Traditional South-East fare', 233, 75 UNION ALL
SELECT 'Feel your arteries thickening as you eat', 3, 12
-- set up the mail job:
DECLARE @tableHTML VARCHAR(MAX)
SET @tableHTML =
'<H3>First query output</H3>' +
'<table border="1">' +
'<tr>
<th>SupplierID</th>
<th>SupplierName</th>
<th>Location</th>
</tr>' +
CAST((
SELECT
td = SupplierID, '',
td = SupplierName, '',
td = Location, ''
FROM #Table1
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX)) +
'</table>' +
'<H3>Second query output</H3>' +
'<table border="1">' +
'<tr>
<th>TastingNotes</th>
<th>Number of Exceptions</th>
<th>Percentage Satisfaction</th>
</tr>' +
CAST((
SELECT
td = TastingNotes, '',
td = [Count], '',
td = Percentage, ''
FROM #Table2
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX)) +
'</table>'
-- run the mail job:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = '', -- your value
@recipients='', -- your value
@subject = 'A nicely-formatted email body with embedded data',
@body = @tableHTML,
@body_format = 'HTML'
Wow that's so nice. I had heard about it but never played with it. It's like the Loch Ness Monster, so glad I finally saw it 😉
July 28, 2011 at 7:36 am
Ninja's_RGR'us (7/28/2011)
Wow that's so nice. I had heard about it but never played with it. It's like the Loch Ness Monster, so glad I finally saw it 😉
It's actually the Loch DBA EMons. Slightly different beastie.
July 28, 2011 at 7:42 am
Brandie Tarvin (7/28/2011)
Ninja's_RGR'us (7/28/2011)
Wow that's so nice. I had heard about it but never played with it. It's like the Loch Ness Monster, so glad I finally saw it 😉It's actually the Loch DBA EMons. Slightly different beastie.
Got a picture?
July 28, 2011 at 7:46 am
Ninja's_RGR'us (7/28/2011)
Brandie Tarvin (7/28/2011)
Ninja's_RGR'us (7/28/2011)
Wow that's so nice. I had heard about it but never played with it. It's like the Loch Ness Monster, so glad I finally saw it 😉It's actually the Loch DBA EMons. Slightly different beastie.
Got a picture?
It's kind of blurry and tends to disappear whenever I attach it to posts. We'll see if it attaches to this one.
Notice the polka dots on the ldf fin, and how it uses DBCC Timewarp() as a method of locomotion. It has a highly sophisticated alert system, but I've never been able to intercept the notification packets to learn more information.
July 28, 2011 at 7:50 am
Wow, looks awesome :-P.
And the picture too.
(Just saw your home site ;-))
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply