SET NOCOUNT Error in handling SQL call after upgrade
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 3 hours ago
Max Vernon
51.2k13112225
asked 3 hours ago
UnhandledExcepSeanUnhandledExcepSean
20837
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 3 hours ago
Max Vernon
51.2k13112225
asked 3 hours ago
UnhandledExcepSeanUnhandledExcepSean
20837
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♦
2 hours ago
@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
1 hour ago
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 3 hours ago
Max Vernon
51.2k13112225
asked 3 hours ago
UnhandledExcepSeanUnhandledExcepSean
20837
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 3 hours ago
Max Vernon
51.2k13112225
asked 3 hours ago
UnhandledExcepSeanUnhandledExcepSean
20837
edited 3 hours ago
Max Vernon
51.2k13112225
asked 3 hours ago
UnhandledExcepSeanUnhandledExcepSean
20837
edited 3 hours ago
Max Vernon
51.2k13112225
edited 3 hours ago
Max Vernon
51.2k13112225
edited 3 hours ago
Max Vernon
51.2k13112225
51.2k13112225
asked 3 hours ago
UnhandledExcepSeanUnhandledExcepSean
20837
asked 3 hours ago
UnhandledExcepSeanUnhandledExcepSean
20837
asked 3 hours ago
UnhandledExcepSeanUnhandledExcepSean
20837
20837
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♦
2 hours ago
@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
1 hour ago
add a comment |
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♦
2 hours ago
@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
1 hour ago
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♦
2 hours ago
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♦
2 hours ago
@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
1 hour ago
@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
1 hour ago
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 3 hours ago
Max VernonMax Vernon
51.2k13112225
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
3 hours ago
That's definitely correct. IfSET NOCOUNT ONsolves the issue, setting user options to 512 will fix it.
– Max Vernon
3 hours ago
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 3 hours ago
Max VernonMax Vernon
51.2k13112225
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
3 hours ago
That's definitely correct. IfSET NOCOUNT ONsolves the issue, setting user options to 512 will fix it.
– Max Vernon
3 hours ago
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 3 hours ago
Max VernonMax Vernon
51.2k13112225
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
3 hours ago
That's definitely correct. IfSET NOCOUNT ONsolves the issue, setting user options to 512 will fix it.
– Max Vernon
3 hours ago
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 3 hours ago
Max VernonMax Vernon
51.2k13112225
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 3 hours ago
Max VernonMax Vernon
51.2k13112225
answered 3 hours ago
Max VernonMax Vernon
51.2k13112225
answered 3 hours ago
Max VernonMax Vernon
51.2k13112225
answered 3 hours ago
Max VernonMax Vernon
51.2k13112225
51.2k13112225
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
3 hours ago
That's definitely correct. IfSET NOCOUNT ONsolves the issue, setting user options to 512 will fix it.
– Max Vernon
3 hours ago
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
3 hours ago
That's definitely correct. IfSET NOCOUNT ONsolves the issue, setting user options to 512 will fix it.
– Max Vernon
3 hours ago
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
3 hours ago
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
3 hours ago
That's definitely correct. If
SET NOCOUNT ON solves the issue, setting user options to 512 will fix it.– Max Vernon
3 hours ago
That's definitely correct. If
SET NOCOUNT ON solves the issue, setting user options to 512 will fix it.– Max Vernon
3 hours ago
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
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♦
2 hours ago
@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
1 hour ago