October 23, 2007 at 12:34 pm
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @@SERVERNAME,
@recipients = 'xxx@email.com',
@query= 'SELECT * FROM master..sysdatabases',
@exclude_query_output = 0
I am still getting a (xxx Rows affected) message...
How do you get RID of that message in the query results??? I thought it was supposed to be done by the @exclude_query_output = 0 but that doesn't seem to be working...
Any help is appreciated...Google turned up snake eyes...I have tried setting up SET NOCOUNT ON at the beginning of the stored proc as well and that doesn't do it either like it would in SQL 2000.
October 23, 2007 at 12:53 pm
By default, SQL runs the query you specified before putting the message into the service broker queue so you get immediate feedback from sp_send_dbmail if your query is bad.
If you specify @append_query_error = 1 in your call to sp_send_dbmail, it will defer the running of the query to the time the mail is sent from the broker queue and put any messages in the email body.
October 23, 2007 at 1:00 pm
Thanks for the reply...however I don't think that is what I was looking for...I have a general process that fills some tables then a
sp_send_dbmail call that will send an email off with the query results embedded into the email....problem is I am also getting the (xxx Rows Affected) notification which I don't want.
In 2000 you simply entered SET NOCOUNT ON and that got rid of it...that doesn't seem to work in 2005.
I looked at BOL and it said something about the @exclude_query_output = 1 parameter and that was supposed to get rid of it...well it didn't.
For an example here is a mail call that gives me the name of all the databases...
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @@SERVERNAME,
@recipients = 'xxx@email.com',
@query= 'SELECT name FROM master..sysdatabases',
@exclude_query_output = 1
Which returns this...
name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
DB_UTILS
PDQ_BEMIS
PDQ_BEMIS_BUGS
(7 rows affected)
this gives me the (xxx Rows Affected) message still...which I DON'T want...
Leeland
October 23, 2007 at 1:07 pm
Without specifying @append_query_error = 1
sp_send_dbmail runs your query immediately to make sure the query does not fail before the query is put into the mail queue. This is just a precaution in case you wrote a bad query.
With it, sp_send_dbmail is just a call to the service broker. Since it will not actually run the query you specified until the mail is sent from the queue, you will not see any feedback from your query. It will include any feedback from your query in the body of the email, but it should just be error messages, not rowcounts.
October 23, 2007 at 1:18 pm
Michael Earl (10/23/2007)
Without specifying @append_query_error = 1sp_send_dbmail runs your query immediately to make sure the query does not fail before the query is put into the mail queue. This is just a precaution in case you wrote a bad query.
With it, sp_send_dbmail is just a call to the service broker. Since it will not actually run the query you specified until the mail is sent from the queue, you will not see any feedback from your query. It will include any feedback from your query in the body of the email, but it should just be error messages, not rowcounts.
Michael,
First off I figured it out...I will explain at the end...
Thanks for the reply and what you wrote makes sense I did test it and I believe I got what you are describing above...I took the same query as above:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @@SERVERNAME,
@recipients = 'lrhart@bemis.com',
@query= 'SELECT name FROM master..sysdatabases',
@exclude_query_output = 1,
@append_query_error = 1
Everything works great...get output however it isn't want I want...still have xxx rows affected message
Then I run the same query with a mispelling in the query
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @@SERVERNAME,
@recipients = 'lrhart@bemis.com',
@query= 'SELECT name FROM master..sysdatabasesss',
@exclude_query_output = 1,
@append_query_error = 1
Get the following error in an email...
Msg 208, Level 16, State 1, Server DS071035, Line 1 Invalid object name 'master..sysdatabasesss'.
when I had the @append_query_error = 0 the execution said it completed however I got no notification and no error message.
SO I am right back to where I was before...still getting the xxx rows affected....
I then decided to embed the SET NOCOUNT ON within the query and it works...I don't know why or how but I am guessing it has something to do with the "specific" session or action of sending the sp_send_dbmail...
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @@SERVERNAME,
@recipients = 'lrhart@bemis.com',
@query= 'SET NOCOUNT ON; SELECT name FROM master..sysdatabases',
@exclude_query_output = 1,
@append_query_error = 1
Works and gives me no issues...and I get no xxx Rows affected message.
Thanks for the help Michael,
Lee
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply