Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Rowcount Expand / Collapse
Author
Message
Posted Thursday, January 28, 2010 11:06 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 2:13 AM
Points: 1,865, Visits: 368
thanks, i got my answer..

even after opening a new window, i got the result as 1,1 and i was wondering why ???

stewartc-708166 (1/28/2010)
when a new query window is opened, a connection is made to the database, which returns a result (not visible)
this item is reflected in the @@rowcount as 1
however, SET NOCOUNT ON does reset this to 0.

interesting question, I think this is going to be another interesting discussion...


Post #855777
Posted Thursday, January 28, 2010 11:18 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, April 27, 2011 9:10 AM
Points: 1,814, Visits: 60
First query returns 0 ONLY when SET ROWCOUNT {something} is executed first. It doesn't matter to which value ROWCOUNT is set (2008EE).
Interesting issue but answer to the question is wrong
Post #855780
Posted Thursday, January 28, 2010 11:23 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 28, 2011 7:06 AM
Points: 151, Visits: 20
Hi

I too experienced the same result (1,1). I tried it in sql 2008

pls give the detailed explanation for the same......
Post #855782
Posted Thursday, January 28, 2010 11:23 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, April 17, 2014 1:41 AM
Points: 543, Visits: 101
1,1 in that case when u have opened a new qurey window and executed that statement and 0,1 in case when u have executed a select statement, which returns nothing and then u execute the same rowcount statement. So by default 1,1 is the correct answer.
Post #855784
Posted Thursday, January 28, 2010 11:30 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, March 30, 2013 6:06 AM
Points: 532, Visits: 224
I too got 1,1 in sql server 2008
Post #855786
Posted Thursday, January 28, 2010 11:31 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 5:31 PM
Points: 2,953, Visits: 435
The posts saying if you set SET NOCOUNT ON or SET ROWCOUNT <any value> first is correct, you'll get 0,1 as the answer. unfortunately the question didn't mention anything about it, so 1,1 is what most people would have answered (as shown by the percentage of the chosen answer).

I got it wrong (chose 1,1) but I don't really care, at least I learn something new!



Urbis, an urban transformation company
Post #855788
Posted Thursday, January 28, 2010 11:49 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 3:59 AM
Points: 1,938, Visits: 1,149

Dear all,

SET NOCOUNT ON

Does not reset the @@rowcount to 0.

@@rowcount is zero when we execute the system Defaults like as shown

set ansi_padding on
select @@rowcount

set nocount on
select @@rowcount

set ansi_nulls on
select @@rowcount

set ansi_warnings off .... so on


for all system defaults like above when we set system defaults on/off @@rowcount will be displayed as zero.

other wise by defalut @@rowcount is zero.





Malleswarareddy
I.T.Analyst
MCITP(70-451)
Post #855791
Posted Friday, January 29, 2010 12:04 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 6:40 AM
Points: 3,683, Visits: 4,818
upon opening a new query window in ssms, try

set nocount on
go
select @@ROWCOUNT
select @@ROWCOUNT

then the result will be 0,1
otherwise it will be 1,1


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #855800
Posted Friday, January 29, 2010 12:47 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, March 28, 2014 5:06 AM
Points: 1,204, Visits: 915
Can I please ask that questions asked please take all necessary conditions in consideration. I lost a point and so what but when a question is asked and things like nocount and rowcount is omitted then please first make sure that your answer is what it really is.

Manie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Post #855827
Posted Friday, January 29, 2010 1:00 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, April 10, 2014 7:08 AM
Points: 3,448, Visits: 4,406
stewartc-708166 (1/28/2010)
when a new query window is opened, a connection is made to the database, which returns a result (not visible)
this item is reflected in the @@rowcount as 1


Not exactly. When connection is made, no results are returned (visible or not). After establishing a connection, SSMS sends some SQL commands to the server. You can see these commands in Profiler:
SELECT SYSTEM_USER
SET ROWCOUNT 0
SET TEXTSIZE 2147483647
... a lot of other SETs ...
select @@spid
select SERVERPROPERTY('ProductLevel')

The last 'select' statement makes the @@rowcount value equal to 1.

In fact, you can see the results of these statements in the bottom of your SSMS window. For example, I see the following:
(local) (9.0 SP2) | <my username> (59) | master | 00:00:00 | 0 rows

(Oops, I'm still having SP2 on my local machine )

When I run the batch via SQLCMD, I get "0, 1" as the result.

So the answer differs for different clients. "1, 1" is the answer for SSMS, "0, 1" is the answer for SQLCMD, and there can be another answer for another client.
Post #855836
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse