August 21, 2006 at 5:18 pm
After applying
exec sp_configure 'user options', 512 -- equivalent to set nocount on
to fix another problem, now SQL Management Studio refuses to add, update, or delete any row in any table. The error is always "Data has changed since the results pane was last retrieved. (Optimistic Concurrency Control Error)". This is hugely annoying, to say the least. I found one post in a MS forum in December describing the same problem, but no one replied. Any work-arounds for this?
August 24, 2006 at 8:00 am
This was removed by the editor as SPAM
August 24, 2006 at 10:34 am
why would you want to do this through the gui anyway? I assume normal T SQL works fine?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 25, 2006 at 12:50 pm
Everything executes correctly at SQL server. But the rows affected message seems to confuse ADO when trying to return a record set. The server-level set nocount switch inhibits the extra message and ADO is happy, but it messes up SSMS.
Without the setting enabled, passing a batch like "Insert ......; Select @@IDENTITY AS newkey" does not get the one column recordset back through ADO. My only option seems to be to find all similar pieces of code and fix them by adding set nocount on as the first command.
August 25, 2006 at 4:42 pm
ah! surely you're not using clasic ado ? This is an ado issue not a sql issue - you need to know how to handle multiple result sets etc. etc. I'm sure this is well documented with ado.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply