As I mess with SQL at work, I learnt about some feature which is called, NOCOUNT. This feature tells the server to return the number of affected rows after each query a client does, thus most of the times wasting bandwidth for unused/unwanted data. I was interested in setting this option server-wide. Although, some people will say it’s a bad move, because if you later have more DB’s on the server which need this feature, bla bla… But I know what I want and that’s what I will do.
The problem was how to set it programatically, I searched the inet for a bit, but didn’t come up with anything useful. So I decided to write that snippet on my own. It’s a bit tricky, but once you see it, it looks ok.
USE master;
DECLARE @CurrentValue AS INT;
CREATE TABLE #tmp (v VARCHAR(50), w INT, x INT, y INT, z INT);
INSERT INTO #tmp
EXEC sp_configure 'User Options';
SELECT @CurrentValue = y FROM #tmp;
DROP TABLE #tmp;
SET @CurrentValue = @CurrentValue | 512; -- 512=NOCOUNT
EXEC sp_configure 'User Options', @CurrentValue;
RECONFIGURE WITH OVERRIDE;
The trick was how to get the results from the EXEC sp_configure so you can read them yourself, the solution was to create a temporary table which you instruct SQL to insert the results of sp_configure into that table. Later on, you can simply read from this table and do whatever you’re up to. This way we can preserve the original value and in addition set the NOCOUNT flag (512, as a bit field…).