SET NOCOUNT Error in handling SQL call after upgrade
Clash Royale CLAN TAG#URR8PPP
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?
sql-server sql-server-2008 sql-server-2014 sp-configure
edited Feb 27 at 20:51
Max Vernon
51.9k13114230
asked Feb 27 at 20:34
UnhandledExcepSeanUnhandledExcepSean
23839
add a comment |
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?
sql-server sql-server-2008 sql-server-2014 sp-configure
edited Feb 27 at 20:51
Max Vernon
51.9k13114230
asked Feb 27 at 20:34
UnhandledExcepSeanUnhandledExcepSean
23839
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 withSET 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 appendSET NOCOUNT ON;at the start of the stored procedure (afterBEGIN). 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 withBEGIN- it's nice, but not required.
– Max Vernon
Feb 28 at 11:48
add a comment |
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?
sql-server sql-server-2008 sql-server-2014 sp-configure
edited Feb 27 at 20:51
Max Vernon
51.9k13114230
asked Feb 27 at 20:34
UnhandledExcepSeanUnhandledExcepSean
23839
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?
sql-server sql-server-2008 sql-server-2014 sp-configure
sql-server sql-server-2008 sql-server-2014 sp-configure
edited Feb 27 at 20:51
Max Vernon
51.9k13114230
asked Feb 27 at 20:34
UnhandledExcepSeanUnhandledExcepSean
23839
edited Feb 27 at 20:51
Max Vernon
51.9k13114230
asked Feb 27 at 20:34
UnhandledExcepSeanUnhandledExcepSean
23839
edited Feb 27 at 20:51
Max Vernon
51.9k13114230
edited Feb 27 at 20:51
Max Vernon
51.9k13114230
edited Feb 27 at 20:51
Max Vernon
51.9k13114230
51.9k13114230
asked Feb 27 at 20:34
UnhandledExcepSeanUnhandledExcepSean
23839
asked Feb 27 at 20:34
UnhandledExcepSeanUnhandledExcepSean
23839
asked Feb 27 at 20:34
UnhandledExcepSeanUnhandledExcepSean
23839
23839
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 withSET 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 appendSET NOCOUNT ON;at the start of the stored procedure (afterBEGIN). 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 withBEGIN- it's nice, but not required.
– Max Vernon
Feb 28 at 11:48
add a comment |
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 withSET 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 appendSET NOCOUNT ON;at the start of the stored procedure (afterBEGIN). 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 withBEGIN- it's nice, but not required.
– Max Vernon
Feb 28 at 11:48
2
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
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
@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
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
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
@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
add a comment |
1 Answer
1
active
oldest
votes
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).
answered Feb 27 at 20:42
Max VernonMax Vernon
51.9k13114230
That almost certainly is the cause (waiting on confirmation now). The old server had a value of 512 which is NOCOUNT according to MS (docs.microsoft.com/en-us/sql/database-engine/configure-windows/…). I didn't see this earlier as I was only looking at @@OPTIONS
– UnhandledExcepSean
Feb 27 at 20:49
That's definitely correct. IfSET NOCOUNT ONsolves the issue, setting user options to 512 will fix it.
– Max Vernon
Feb 27 at 20:51
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230905%2fset-nocount-error-in-handling-sql-call-after-upgrade%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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).
answered Feb 27 at 20:42
Max VernonMax Vernon
51.9k13114230
That almost certainly is the cause (waiting on confirmation now). The old server had a value of 512 which is NOCOUNT according to MS (docs.microsoft.com/en-us/sql/database-engine/configure-windows/…). I didn't see this earlier as I was only looking at @@OPTIONS
– UnhandledExcepSean
Feb 27 at 20:49
That's definitely correct. IfSET NOCOUNT ONsolves the issue, setting user options to 512 will fix it.
– Max Vernon
Feb 27 at 20:51
add a comment |
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).
answered Feb 27 at 20:42
Max VernonMax Vernon
51.9k13114230
That almost certainly is the cause (waiting on confirmation now). The old server had a value of 512 which is NOCOUNT according to MS (docs.microsoft.com/en-us/sql/database-engine/configure-windows/…). I didn't see this earlier as I was only looking at @@OPTIONS
– UnhandledExcepSean
Feb 27 at 20:49
That's definitely correct. IfSET NOCOUNT ONsolves the issue, setting user options to 512 will fix it.
– Max Vernon
Feb 27 at 20:51
add a comment |
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).
answered Feb 27 at 20:42
Max VernonMax Vernon
51.9k13114230
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).
answered Feb 27 at 20:42
Max VernonMax Vernon
51.9k13114230
edited Mar 1 at 4:04
answered Feb 27 at 20:42
Max VernonMax Vernon
51.9k13114230
answered Feb 27 at 20:42
Max VernonMax Vernon
51.9k13114230
answered Feb 27 at 20:42
Max VernonMax Vernon
51.9k13114230
51.9k13114230
That almost certainly is the cause (waiting on confirmation now). The old server had a value of 512 which is NOCOUNT according to MS (docs.microsoft.com/en-us/sql/database-engine/configure-windows/…). I didn't see this earlier as I was only looking at @@OPTIONS
– UnhandledExcepSean
Feb 27 at 20:49
That's definitely correct. IfSET NOCOUNT ONsolves the issue, setting user options to 512 will fix it.
– Max Vernon
Feb 27 at 20:51
add a comment |
That almost certainly is the cause (waiting on confirmation now). The old server had a value of 512 which is NOCOUNT according to MS (docs.microsoft.com/en-us/sql/database-engine/configure-windows/…). I didn't see this earlier as I was only looking at @@OPTIONS
– UnhandledExcepSean
Feb 27 at 20:49
That's definitely correct. IfSET NOCOUNT ONsolves the issue, setting user options to 512 will fix it.
– Max Vernon
Feb 27 at 20:51
That almost certainly is the cause (waiting on confirmation now). The old server had a value of 512 which is NOCOUNT according to MS (docs.microsoft.com/en-us/sql/database-engine/configure-windows/…). I didn't see this earlier as I was only looking at @@OPTIONS
– UnhandledExcepSean
Feb 27 at 20:49
That almost certainly is the cause (waiting on confirmation now). The old server had a value of 512 which is NOCOUNT according to MS (docs.microsoft.com/en-us/sql/database-engine/configure-windows/…). I didn't see this earlier as I was only looking at @@OPTIONS
– UnhandledExcepSean
Feb 27 at 20:49
That's definitely correct. If
SET NOCOUNT ON solves the issue, setting user options to 512 will fix it.– Max Vernon
Feb 27 at 20:51
That's definitely correct. If
SET NOCOUNT ON solves the issue, setting user options to 512 will fix it.– Max Vernon
Feb 27 at 20:51
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230905%2fset-nocount-error-in-handling-sql-call-after-upgrade%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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 (afterBEGIN). 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