SET NOCOUNT Error in handling SQL call after upgradeSQL Server 2008 R2 Express single CPU core usage and need...
What is the difference between rolling more dice versus fewer dice?
Slow While Loop, Query Improvment Assistance
What are "industrial chops"?
When can a QA tester start his job?
Removing disk while game is suspended
Cookies - Should the toggles be on?
How can my powered armor quickly replace its ceramic plates?
Eww, those bytes are gross
A curious equality of integrals involving the prime counting function?
How do you catch Smeargle in Pokemon Go?
Why am I able to open Wireshark in macOS without root privileges?
What is the data structure of $@ in shell?
Is it a fallacy if someone claims they need an explanation for every word of your argument to the point where they don't understand common terms?
Why did Luke use his left hand to shoot?
How can I play a serial killer in a party of good PCs?
What would the chemical name be for C13H8Cl3NO
What is a good reason for every spaceship to carry a weapon on board?
Cat is tipping over bed-side lamps during the night
What is the most fuel efficient way out of the Solar System?
Mathematics and the art of linearizing the circle
Why did Democrats in the Senate oppose the Born-Alive Abortion Survivors Protection Act (2019 S.130)?
Why is it that Bernie Sanders is always called a "socialist"?
Clues on how to solve these types of problems within 2-3 minutes for competitive exams
How does Leonard in "Memento" remember reading and writing?
SET NOCOUNT Error in handling SQL call after upgrade
SQL Server 2008 R2 Express single CPU core usage and need of upgrade to SQL Server 2014 ExpressSQL Server Management Studio 2014 Backup - Set Property Devices ErrorReplication error after server name changegrant dbmail access to accountSQL Server AlwaysOn database stuck in Not Synchronizing / In Recovery mode after upgrading. Error: Cannot open database '…' version 782Much slower SQL application performance after upgrading from Windows Server 2008 R2 to Windows Server 2012 R2Cardinality Estimator Question after 2014 SP1 + CU4 UpgradeTimeout error after SQL Server upgrade from 2008R2 to SQL Server 2014SSPI handshake failed with error code 0x8009030c - only stops when SSMS is closedSQL Server 2016 Restore Failure on DB Using Clustered Shared Volumes (Error 7627)
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 1 hour ago
Max Vernon
51.2k13112225
asked 1 hour ago
UnhandledExcepSeanUnhandledExcepSean
19327
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 1 hour ago
Max Vernon
51.2k13112225
asked 1 hour ago
UnhandledExcepSeanUnhandledExcepSean
19327
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♦
16 mins 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 1 hour ago
Max Vernon
51.2k13112225
asked 1 hour ago
UnhandledExcepSeanUnhandledExcepSean
19327
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 1 hour ago
Max Vernon
51.2k13112225
asked 1 hour ago
UnhandledExcepSeanUnhandledExcepSean
19327
edited 1 hour ago
Max Vernon
51.2k13112225
asked 1 hour ago
UnhandledExcepSeanUnhandledExcepSean
19327
edited 1 hour ago
Max Vernon
51.2k13112225
edited 1 hour ago
Max Vernon
51.2k13112225
edited 1 hour ago
Max Vernon
51.2k13112225
51.2k13112225
asked 1 hour ago
UnhandledExcepSeanUnhandledExcepSean
19327
asked 1 hour ago
UnhandledExcepSeanUnhandledExcepSean
19327
asked 1 hour ago
UnhandledExcepSeanUnhandledExcepSean
19327
19327
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♦
16 mins 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♦
16 mins 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♦
16 mins 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♦
16 mins 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 1 hour 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
1 hour ago
That's definitely correct. IfSET NOCOUNT ONsolves the issue, setting user options to 512 will fix it.
– Max Vernon
1 hour 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 1 hour 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
1 hour ago
That's definitely correct. IfSET NOCOUNT ONsolves the issue, setting user options to 512 will fix it.
– Max Vernon
1 hour 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 1 hour 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
1 hour ago
That's definitely correct. IfSET NOCOUNT ONsolves the issue, setting user options to 512 will fix it.
– Max Vernon
1 hour 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 1 hour 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 1 hour ago
Max VernonMax Vernon
51.2k13112225
answered 1 hour ago
Max VernonMax Vernon
51.2k13112225
answered 1 hour ago
Max VernonMax Vernon
51.2k13112225
answered 1 hour 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
1 hour ago
That's definitely correct. IfSET NOCOUNT ONsolves the issue, setting user options to 512 will fix it.
– Max Vernon
1 hour 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
1 hour ago
That's definitely correct. IfSET NOCOUNT ONsolves the issue, setting user options to 512 will fix it.
– Max Vernon
1 hour 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
1 hour 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
1 hour ago
That's definitely correct. If
SET NOCOUNT ON solves the issue, setting user options to 512 will fix it.– Max Vernon
1 hour ago
That's definitely correct. If
SET NOCOUNT ON solves the issue, setting user options to 512 will fix it.– Max Vernon
1 hour 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♦
16 mins ago