SET NOCOUNT Error in handling SQL call after upgradeSQL Server 2008 R2 Express single CPU core usage and need...
Why publish a research paper when a blog post or a lecture slide can have more citation count than a journal paper?
Removing disk while game is suspended
Is there any risk in sharing info about technologies and products we use with a supplier?
Early credit roll before the end of the film
Graph with overlapping labels
What is the purpose of easy combat scenarios that don't need resource expenditure?
Why is it that Bernie Sanders is always called a "socialist"?
When can a QA tester start his job?
How do you catch Smeargle in Pokemon Go?
Why do neural networks need so many training examples to perform?
Quickly creating a sparse array
Finding a logistic regression model which can achieve zero error on a training set training data for a binary classification problem with two features
Mathematics and the art of linearizing the circle
Cat is tipping over bed-side lamps during the night
What sets the resolution of an analog resistive sensor?
How to tell if a BJT is PNP or NPN by looking at the circuit?
It took me a lot of time to make this, pls like. (YouTube Comments #1)
Increment each digit in a number to form a new number
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 avoid shared user accounts?
Citing paywalled articles accessed via illegal web sharing
Words and Words with "ver-" Prefix
Can a Spectator be a bodyguard? So, can its treasure/item to guard be a person/person's item?
Eww, those bytes are gross
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♦
15 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♦
15 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♦
15 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♦
15 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♦
15 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♦
15 mins ago