SET NOCOUNT Error in handling SQL call after upgradeSQL Server 2008 R2 Express single CPU core usage and need...
Why am I able to open Wireshark in macOS without root privileges?
What incentives do banks have to gather up loans into pools (backed by Ginnie Mae)and selling them?
Play Zip, Zap, Zop
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?
Finding a logistic regression model which can achieve zero error on a training set training data for a binary classification problem with two features
What would be the rarity of this magic item(s)?
Why exactly do action photographers need high fps burst cameras?
Gear reduction on large turbofans
Why zero tolerance on nudity in space?
Making him into a bully (how to show mild violence)
Cookies - Should the toggles be on?
Cat is tipping over bed-side lamps during the night
Why are the books in the Game of Thrones citadel library shelved spine inwards?
How does Leonard in "Memento" remember reading and writing?
Why do cars have plastic shrouds over the engine?
What is the data structure of $@ in shell?
What are "industrial chops"?
When can a QA tester start his job?
It took me a lot of time to make this, pls like. (YouTube Comments #1)
Clues on how to solve these types of problems within 2-3 minutes for competitive exams
How to tell if a BJT is PNP or NPN by looking at the circuit?
If I delete my router's history can my ISP still provide it to my parents?
In mixed effect models, how account for grouped random effects?
Slow While Loop, Query Improvment Assistance
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♦
11 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♦
11 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♦
11 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♦
11 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♦
11 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♦
11 mins ago