September 20, 2002 at 12:40 pm
I have two different SQL Server 2000 servers. Both are running the same version of SQL Server. I have the same jobs on both of them. All the jobs begin with SET NOCOUNT ON. The jobs on one server (call it SQLA) run fine and return the results without the rowcount at the end. The jobs on the other server (call it SQLB) run fine, except they return the results WITH the rowcount at the end. As I said, it's the same jobs on both machines - I copied them from one (SQLA where they work) to the other (SQLB where they keep giving the rowcount).
By the way, by rowcount I mean that line that says:
(10 row(s) affected)
This is really frustrating me......I checked the queries for typos, but there aren't any - SET NOCOUNT ON is pretty easy to type.
One other thing, if I run the query in QA I don't get the rowcount when I use SET NOCOUNT ON.
-SQLBill
September 20, 2002 at 12:42 pm
OOPPPSSS, I guess I really should include my question....
Any ideas why SET NOCOUNT ON isn't working on my second server?
-Bill
September 20, 2002 at 1:05 pm
Some where in you stored procedure after you issue the "SET NOCOUNT ON" run this:
select @@OPTIONS&512
if the result is 0 then "Set nocount off" is applied. If the result is 512 the "Set nocount on" is applied.
I know this does not answer why, but at least it will give use read on the server setting after you issue the "SET NOCOUNT ON" command.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 23, 2002 at 8:07 am
Okay, I added the SELECT @@option&512 and ran it as part of the job. It returned 512 (SET NOCOUNT ON) but I still got the (1 row(s) affected) message. This is happening with every job that I have on the second server. It wouldn't bug me as much if it also happened using Query Analyzer, but in QA the SET NOCOUNT ON works as expected.
-SQLBill
September 23, 2002 at 8:50 am
I think it has something to do with odbc settings.... i am copying a block off books online which might help
......
When the NOCOUNT option is set on, SQL Server does not return the counts of the rows affected by a statement and SQLRowCount returns 0. The SQL Server ODBC driver version 3.7 introduces a driver-specific SQLGetStmtAttr option, SQL_SOPT_SS_NOCOUNT_STATUS, to report on whether the NOCOUNT option is on or off. Anytime SQLRowCount returns 0, the application should test SQL_SOPT_SS_NOCOUNT_STATUS. If SQL_NC_ON is returned, the value of 0 from SQLRowCount only indicates that SQL Server has not returned a row count. If SQL_NC_OFF is returned, it means that NOCOUNT is off and the value of 0 from SQLRowCount indicates that the statement did not affect any rows. Applications should not display the value of SQLRowCount when SQL_SOPT_SS_NOCOUNT_STATUS is SQL_NC_OFF. Large batches or stored procedures may contain multiple SET NOCOUNT statements so programmers cannot assume SQL_SOPT_SS_NOCOUNT_STATUS remains constant. The option should be tested each time SQLRowCount returns 0.
Regards,
Dave
Regards,
Dave
September 23, 2002 at 8:51 am
I rebooted the server, stopped and restarted SQL Server Agent service and SQL Server service. Still no luck. This is really bugging me since it works properly in Query Analyzer but not as a job in Enterprise Manager.
-Bill
September 23, 2002 at 9:11 am
Maybe it's time to call Microsoft.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 23, 2002 at 10:07 am
HermitD,
I'm not connecting via ODBC, I'm doing all this right on the SQL Server machine.
Greg,
I think you may be right. I've even posted this on someother sites and haven't gotten any help. It seems to have stumped everyone.
-Bill
September 23, 2002 at 11:59 am
I think I got the answer to my problem! It came from another site. Apparently it's documented bug.
It's in Microsofts Knowledge Base Articles (Q294412).
-Bill
June 22, 2016 at 8:51 am
Hi,
I am using SQL Server 2012.
I have multiple inserts and updates in my procedure. I have 'set nocount ON' in the beginning of the procedure. But, everytime I execute my procedure I end up getting number of rows affected message
Is there any way to switch it off or is this a bug to be rectified by Microsoft or is there a patch available that I can download to fix this or is anything wrong with my code 🙂 ?
Any help would be greatly appreciated?
Regards
Rathi
June 29, 2016 at 12:51 am
Run this query:
SELECT * from syscomments
where text like '%NOCOUNT OFF%'
Some people have a habit to add SET NOCOUNT OFF to the code of their procedures.
_____________
Code for TallyGenerator
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply