December 13, 2012 at 10:24 am
I am using SSMS 2008 version 10.50.1617.0 connected to a 2008 R2 SQL server. For some reason, when I run queries that are not necessarily affecting rows, such as a CREATE TABLE statement, I get a "1 row(s) affected message" rather than the classic "Commands Completed Successfully" message. Did something change in SSMS or is there a setting that controls this? I can't find anything in the options.
Not a huge deal, but it's kind of annoying to see that "rows" have been affected when they really have not been. Thanks in advance!
December 13, 2012 at 10:48 am
dsorensen 97289 (12/13/2012)
I am using SSMS 2008 version 10.50.1617.0 connected to a 2008 R2 SQL server. For some reason, when I run queries that are not necessarily affecting rows, such as a CREATE TABLE statement, I get a "1 row(s) affected message" rather than the classic "Commands Completed Successfully" message. Did something change in SSMS or is there a setting that controls this? I can't find anything in the options.Not a huge deal, but it's kind of annoying to see that "rows" have been affected when they really have not been. Thanks in advance!
That isn't how it works for me. I have a slightly different version of SSMS (10.50.1600.1)
But if I run the following:
create table #Test
(
ID int identity,
txSubType varchar(20)
)
drop table #Test
I get command(s) completed successfully.
I suspect there is something else going on for you. It wouldn't say 1 row affected unless there was a row affected somewhere.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 13, 2012 at 10:50 am
As far as I can remember this is standard response.
Although you can specfically set the SET NOCOUNT option under Options->Query Execution->SQL Server->Advanced
Is it possible you had this checked in previous versions of SSMS without remembering or as a default setting.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
December 13, 2012 at 10:59 am
You're probably right. It occurrs to me that our DBA team probably have triggers set up to log all our actions on this particular Database instance.
Thanks for taking the time to answer!
December 13, 2012 at 11:01 am
Someone's set up a DDL trigger and forgot to SET NOCOUNT ON in it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 13, 2012 at 11:14 am
GilaMonster (12/13/2012)
Someone's set up a DDL trigger and forgot to SET NOCOUNT ON in it.
this is my thoughts as well. when we deal with a table that has a trigger we get the results for both the insert and the trigger. since a CREATE TABLE does not have a row count you get just the 1 row affected.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply