set nocount on

Question

Post reply

set nocount on

Shahin

Mr or Mrs. 500

Points: 538
More actions
November 24, 2015 at 4:37 am

#149141

Hi,
the result of my query send as csv by dbmail to my customer, when I open the csv file at the end of the file see (37 rows affected), I want to remove this from the csv file.
I did add the set nocount on to the begin of the query, but still after running the query I can see (37 rows affected) in my csv file
any ida?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

If you run the exact same query in a query window, do you get a row count in the Messages pane?

John

Shahin Mr or Mrs. 500 Points: 538 More actions · Answer 2

Thank you for the replay,

If I run the query directly with or without the set nocount on I see only this message:

Command(s) completed successfully.

but in the csv file I see (37 rows affected)

Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Please will you post the whole query, redacted or obfuscated as necessary?

Thanks

John

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

If I'm reading it correctly, I thin you're saying that your procedure contains the SET NOCOUNT ON and you're saying that you don't want the attachment (generated by a different query specified in the @query parameter of the call to sp_send_dbmail) to include a rowcount.

If this is correct, try putting the SET NOCOUNT ON inside the query you specify in the @query parameter. The separate query executed by sp_send_dbmail will be processed with NOCOUNT ON. Here's an example:

execute msdb.dbo.sp_send_dbmail

@profile_name = 'Your Profile Name',

@recipients = 'user@domain.com',

@query = 'set nocount on; select name from sys.objects where type = ''if'';',

@attach_query_result_as_file = 1,

@query_attachment_filename = 'list.txt';

I hope this solves your problem, but to go any further, we need to see the actual SQL that you're running. See the link in my signature for advice on how to post questions if you need to.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Shahin Mr or Mrs. 500 Points: 538 More actions · Answer 5

you are right I dont want to see the line that says (37 rows affected) in the csv file.

This the query that I run and it genrate the (37 rows affected) in the csv file.

DECLARE @tab char(1) = CHAR(9)

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'test profile',

@recipients = 'shahin@gmail.com',

@subject = 'Database Mail Test by T-SQL',

@execute_query_database = mydb,

@body = 'This is a test e-mail sent from Database Mail on.',

@query ='

SELECT ''COllectie'' AS Collectie, Users.Login, Users.DisplayName, SecurityGroups.SecurityGroup

FROM Users INNER JOIN

SecurityGroups ON Users.SecurityGroupID = SecurityGroups.SecurityGroupID

WHERE (NOT (Users.Login = N''Administrator''))

order by login;',

@query_attachment_filename = 'my.csv',

@query_result_separator = @tab,

@attach_query_result_as_file = 1,

@query_result_no_padding= 1,

@query_result_width = 9999,

@exclude_query_output =1,

@append_query_error = 0,

@query_result_header =1;

Ed Wagner SSC Guru Points: 287026 More actions · Answer 6

Shahin (11/24/2015)
you are right I dont want to see the line that says (37 rows affected) in the csv file.
This the query that I run and it genrate the (37 rows affected) in the csv file.
DECLARE @tab char(1) = CHAR(9)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'test profile',
@recipients = 'shahin@gmail.com',
@subject = 'Database Mail Test by T-SQL',
@execute_query_database = mydb,
@body = 'This is a test e-mail sent from Database Mail on.',
@query ='
SELECT ''COllectie'' AS Collectie, Users.Login, Users.DisplayName, SecurityGroups.SecurityGroup
FROM Users INNER JOIN
SecurityGroups ON Users.SecurityGroupID = SecurityGroups.SecurityGroupID
WHERE (NOT (Users.Login = N''Administrator''))
order by login;',
@query_attachment_filename = 'my.csv',
@query_result_separator = @tab,
@attach_query_result_as_file = 1,
@query_result_no_padding= 1,
@query_result_width = 9999,
@exclude_query_output =1,
@append_query_error = 0,
@query_result_header =1;

So add the SET NOCOUNT ON to your @query parameter like I did in my example.

@query ='SET NOCOUNT ON;

SELECT ''COllectie'' AS Collectie, Users.Login, Users.DisplayName, SecurityGroups.SecurityGroup

FROM Users INNER JOIN

SecurityGroups ON Users.SecurityGroupID = SecurityGroups.SecurityGroupID

WHERE (NOT (Users.Login = N''Administrator''))

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Shahin Mr or Mrs. 500 Points: 538 More actions · Answer 7

Thank you that solve the issue

I did add the SET NOCOUNT ON; to wrong place

Thank you again.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 8

No problem. Glad I could help. Thanks for the feedback.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions