nocount | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

nocount

I want to check whether SET NOCOUNT option for my db server is ON/OFF? What is the T-SQL command?
Thank You. I think, therefore I am
AFAIK, there is no general setting for NOCOUNT. You can only set it at runtime.
–Frank
http://www.insidesql.de

… and by default its OFF. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
How to check at perticular moment whether it is on or off? I think, therefore I am
BOL refers
Transact-SQL includes the SET NOCOUNT statement. 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.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>