Another SQL Bug
In our development environment, I found no one could use SQL Management Studio to update data.
We would get:
Data has changed since the Results pane was last retrieved. Do you want to save your changes now?
(Optimistic Concurrency Control Error)
Click Yes to commit your changes to database anyway.
Click No to discard your change and retrieve the current data for this row.
Click Cancel to continue editing.
And then get:
No row was updated.
The data in row X was not committed.
Error Source: Microsoft.VisualStudio.DataTools.
Error Message: The updated row has changed or been deleted.
Correct the errors and retry or press ESC to cancel the change(s).
A search of google and MSDN revealed a lot of people with similar errors, but they had no primary key on the table, or actually had other people change the data. There were also a lot of "why do you want to do this" posts - I really hate it when someone uses that as a reply to a technical question.
Then I found:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289535
I hate having SET NOCOUNT ON, SET NOCOUNT OFF in each of our 1000+ stored procedures, and worse, people do not set these consistently. So I set SET NOCOUNT ON by default. Apparently the team that wrote management studio just assumed NOCOUNT would always be false, and relied on the return values without even checking to see if it was on. Worse, they didn't give me anything in the GUI to use any connection settings other than the default. So if you use SQL Server 2005, you can either get the reasonable default setting of NOCOUNT to ON OR use Management Studio to enter data. You cannot do both.
I am really hoping this is fixed in SQL 2008, which I haven't had much time to try. Given the virtually unlimited money and years of time Microsoft invested, these things annoy me.
BTW - NOCOUNT=TRUE is like a double negative - this is a bad name. Why not SET COUNT=OFF insead of SET NOCOUNT=ON?
Both of my coworkers ran into this issue, but ignored it instead of figuring it out.
We would get:
Data has changed since the Results pane was last retrieved. Do you want to save your changes now?
(Optimistic Concurrency Control Error)
Click Yes to commit your changes to database anyway.
Click No to discard your change and retrieve the current data for this row.
Click Cancel to continue editing.
And then get:
No row was updated.
The data in row X was not committed.
Error Source: Microsoft.VisualStudio.DataTools.
Error Message: The updated row has changed or been deleted.
Correct the errors and retry or press ESC to cancel the change(s).
A search of google and MSDN revealed a lot of people with similar errors, but they had no primary key on the table, or actually had other people change the data. There were also a lot of "why do you want to do this" posts - I really hate it when someone uses that as a reply to a technical question.
Then I found:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289535
I hate having SET NOCOUNT ON, SET NOCOUNT OFF in each of our 1000+ stored procedures, and worse, people do not set these consistently. So I set SET NOCOUNT ON by default. Apparently the team that wrote management studio just assumed NOCOUNT would always be false, and relied on the return values without even checking to see if it was on. Worse, they didn't give me anything in the GUI to use any connection settings other than the default. So if you use SQL Server 2005, you can either get the reasonable default setting of NOCOUNT to ON OR use Management Studio to enter data. You cannot do both.
I am really hoping this is fixed in SQL 2008, which I haven't had much time to try. Given the virtually unlimited money and years of time Microsoft invested, these things annoy me.
BTW - NOCOUNT=TRUE is like a double negative - this is a bad name. Why not SET COUNT=OFF insead of SET NOCOUNT=ON?
Both of my coworkers ran into this issue, but ignored it instead of figuring it out.
posted by Brad at 6:12 AM
3 Comments:
To all data analyst, data managers, software engineers and data miners.
Take the online survey for Molecular Algorithm: A Better Approach in Data Integration
Ι ωаs suggested this ωeb site by my cousin.
I'm not sure whether this post is written by him as nobody else know such detailed about my problem. You're іnсrеdіblе!
Τhаnks!
Feel free tο surf to my ωeblog tens machines For sale
I was suggested thіs ωeb site by mу cousin.
I'm not sure whether this post is written by him as nobody else know such detailed about my problem. You're incredible!
Thanks!
My blog pοst tens machines For sale
Also see my web site: tens
Post a Comment
<< Home