13

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?

  • 2
    All stored procedures should specify the behavior you rely on, which is much safer than relying on user options. I would even potentially argue that every stored procedure should always start with SET NOCOUNT ON;, always. – Aaron Bertrand Feb 27 at 22:01
  • @AaronBertrand I agree; the last project I was on had a template that had set nocount on, but that isn’t an option in this project at this time – UnhandledExcepSean Feb 27 at 22:44
  • Note that you can write a script to alter all stored procedures and append SET NOCOUNT ON; at the start of the stored procedure (after BEGIN). The number of stored procedures shouldn't really be an issue. – Erik A Feb 28 at 11:37
  • 1
    @Erik-A - you can't be certain a stored procedure starts with BEGIN - it's nice, but not required. – Max Vernon Feb 28 at 11:48
16

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.