June 27, 2011 at 9:32 am
Hi,
I have created an sp which returns a table which i want to send as file attachement.
Within stored procedure i am printing different debug/informational message (using PRINT) which appears in SSMS in messages window and tale appears in Results window.
Procedure and sp_send_dbmail is working fine.
Problem is, its sending print messages from messages window in the email as well.
Can we control this, so that only output from Results window gets attached with the email.
I have used the @exclude_query_output=1 as well but still the results are same.
SET NOCOUNT is ON;
Any reason OR suggestion please.
Thanks.
June 27, 2011 at 9:34 am
Add optional parameter @debug BIT = 0
Then if @debug = 1 PRINT 'Whatever'
June 27, 2011 at 9:34 am
I'd have to see your code to really get what you're running into.
I've never had a problem with error messages, et al, ending up in the message anywhere, but maybe I'm not properly visualizing what you're doing.
- 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
June 27, 2011 at 9:35 am
GSquared (6/27/2011)
I'd have to see your code to really get what you're running into.I've never had a problem with error messages, et al, ending up in the message anywhere, but maybe I'm not properly visualizing what you're doing.
Yup if I didn't understand your request then post the sp and mail code.
June 27, 2011 at 10:10 am
HI,
Please check this simple logic
CREATE proc dbo.test
as
begin
SET NOCOUNT ON
if 1=1
begin
print 'counting objects'
select count(1) from sys.objects
print 'printed successfully'
end
else
print 'Invalid request'
select count(1) from information_schema.routines
end
----DROP PROC dbo.test
And its printing following in messages
counting objects
printed successfully
But in email you will get following
counting objects
6748
printed successfully
4005
So can we avoid these print messages in email?
I am using following sp_send_dbmail for sending email.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DB_Email_Profile',
@recipients = 'YourEmail',
@subject = 'Subject',
@execute_query_database='YourDatabase',
@query = 'SET NOCOUNT ON; EXEC dbo.test',
@attach_query_result_as_file = 0,
@query_attachment_filename = 'FileName',
@query_result_header = 0,
@exclude_query_output = 1
Thanks.
June 27, 2011 at 10:12 am
My first answer will solve your problem.
June 27, 2011 at 10:37 am
Avoid the extra outputs in the proc. What are they meant to do?
- 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
June 27, 2011 at 3:26 pm
So there is no setting OR option available to avoid these messages???
Actual procedure logic is quite complex and messages gives detail of each row in the results table. so i need that for debugging/detailed information.
AND what exactly the purpose of @exclude_query_output=1? OR any other logic/setting to avoid these messages?
Thanks.
June 27, 2011 at 4:48 pm
Dude take 2 freaking minutes and apply my solution. You'd be done by now!
June 28, 2011 at 2:03 am
Yes, i already know that handling with bit logic but thought was if anything OR any setting already available to control such behaviour.
Anyways, thanks for your response.
Cheers 🙂
June 28, 2011 at 6:24 am
No. Since those are part of the results of the proc, they are assumed to be something you want. If you don't want them, you need to tell it so.
Otherwise, SQL Server would have to somehow telepathically know which results you do want and which ones you don't.
- 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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply