Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
HenryFulmer
Posting Yak Master
110 Posts |
 Posted - 2011-12-28 : 12:47:31
|
I am using sp_send_dbmail to email a result set to a user.However, the query text file that is being attached to the email containsa line at the bottom stating (XX rows affected)Is there a way to suppress this line? DECLARE @subss VARCHAR(1024)DECLARE @bodys VARCHAR(1024) DECLARE @filename VARCHAR(50) SET @subss = 'TEXT file from: '+ CONVERT(VARCHAR, GETDATE()) SET @bodys = 'Text File Attached , '+ CONVERT(VARCHAR, GETDATE()) SET @filename = 'TF_' + CONVERT(CHAR(8),GETDATE(),112)+ '.txt'EXEC msdb.dbo.sp_send_dbmail@blind_copy_recipients=N'recipient@mail.com',@body=@bodys, @subject = @subss,@profile_name ='Database mail',@query ='select * FROM table', @query_result_header=0,@attach_query_result_as_file = 1,@query_attachment_filename =@filename |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-12-28 : 12:50:04
|
insert this at the beginning of your codeSET NOCOUNT ON; |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-12-28 : 12:56:37
|
Why, thank you Brett!  (Taking a bow and walking off stage) |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
HenryFulmer
Posting Yak Master
110 Posts |
Posted - 2011-12-28 : 13:09:17
|
| GREAT - Thanks! |
|
|
|
HenryFulmer
Posting Yak Master
110 Posts |
Posted - 2012-01-13 : 17:40:54
|
How can I define the format of the ouput file? I'd like it to be a tab delimited file with a new row for each record.DECLARE @subss VARCHAR(1024)DECLARE @bodys VARCHAR(1024) DECLARE @filename VARCHAR(50) SET @subss = 'File from: '+ CONVERT(VARCHAR, GETDATE()) SET @bodys = 'attached file for the date '+ CONVERT(VARCHAR, GETDATE()) SET @filename = 'file.txt'EXEC msdb.dbo.sp_send_dbmail@blind_copy_recipients=N'name@address.com',@body=@bodys, @subject = @subss,@profile_name ='FileSender',@query ='SET NOCOUNT ON; SELECT t1.Number, t1.Name, t2.Location, t2.Status FROM Table1 t1 INNER JOIN Table2 t2 ON t1.Number = t2.Number',@attach_query_result_as_file = 1,@query_attachment_filename =@filename |
|
|
|
michael.malmgren
Starting Member
3 Posts |
Posted - 2012-01-16 : 07:50:40
|
quote:
Originally posted by HenryFulmer
How can I define the format of the ouput file? I'd like it to be a tab delimited file with a new row for each record.DECLARE @subss VARCHAR(1024)DECLARE @bodys VARCHAR(1024) DECLARE @filename VARCHAR(50) SET @subss = 'File from: '+ CONVERT(VARCHAR, GETDATE()) SET @bodys = 'attached file for the date '+ CONVERT(VARCHAR, GETDATE()) SET @filename = 'file.txt'EXEC msdb.dbo.sp_send_dbmail@blind_copy_recipients=N'name@address.com',@body=@bodys, @subject = @subss,@profile_name ='FileSender',@query ='SET NOCOUNT ON; SELECT t1.Number, t1.Name, t2.Location, t2.Status FROM Table1 t1 INNER JOIN Table2 t2 ON t1.Number = t2.Number',@attach_query_result_as_file = 1,@query_attachment_filename =@filename
Hello Henry, I think your formatting options are very limited but you can try to convert to char i.e. "SELECT convert(char(10), t1.Number), convert(char(30), t1.Name), "You might want to check out our framework Boomerang for these and other types of notifications. With Boomerang you can easily attach a PDF, excel or any other type of reporting server format to an email. You can read more here: [url]http://www.fuel9.com/home/content/Getting-Started[/url][url]http://www.fuel9.com/home/content/Email-Formatting[/url]/Michael |
|
|
|
|
|
|
|
|
sp_send_dbmail remove footer