SET NOCOUNT Error in handling SQL call after upgrade

Question

We are upgrading our test environment with a new server and updated version of Microsoft SQL Server and have run into an issue.

On the new server, our old code will get "operation is not allowed when the object is closed" when executing some stored procedures. This message never appeared on the old server. When we tracked it down, the issue can be resolved by adding SET NOCOUNT ON; to the stored procedure.

I looked at the defaults on the database and saw no settings that were different (SQL Server 2008 vs SQL Server 2014) related to defaults.

What setting should I be looking at to resolve this globally without needing to add SET NOCOUNT ON to a thousand stored procs?

Answer 1

SQL Server Configuration has an option called, appropriately, user options, which can be set using the sp_configure system stored procedure. I wrote a blog post on SQL Server Science showing how to inspect and set the user options.

In brief, you can get the "config value" from the old server, using this:

EXEC sys.sp_configure 'user options';

Then, set the new server to use the same options via this:

EXEC sys.sp_configure 'user options', <config value>;
RECONFIGURE

(replace the <config value> with the value from the old server).