Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


@@ROWCOUNT


@@ROWCOUNT

Author
Message
Krissy
Krissy
SSC Veteran
SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)

Group: General Forum Members
Points: 279 Visits: 551
I have a problem with @@ROWCOUNT not returning the correct value.
This is my query:
SET IDENTITY INSERT [dbo].[Table1] ON
 
INSERT [dbo].[Table1](Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM [RemoteSvr].[DBName].[dbo].[Table1]
WHERE Col1 = '12345'
 
SELECT @@ROWCOUNT
 
SET IDENTITY INSERT [dbo].[Table1] OFF
 
Now, when I run this, even though 9 rows are inserted into [Table1], the 'SELECT @@Rowcount' returns 0.
Now, if I just run this (ie remove the INSERT parts of the query):
SELECT Col1, Col2, Col3
FROM [RemoteSvr].[DBName].[dbo].[Table1]
WHERE Col1 = '12345'
 
SELECT @@ROWCOUNT
Then I actually get returned back '9'.
Now I am doing this sort of query for several different tables, and those tables it will return the correct @@ROWCOUNT for the number of rows inserted.
Any ideas why @@ROWCOUNT would not work for this table?!?
 

Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5852 Visits: 11412
Scope of system functions is limited to the local server.

Since no rows have been inserted or selected locally @@ROWCOUNT returns 0.
Avinash Barnwal
Avinash Barnwal
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 16

I dont think the issue with selection of Remoteserver data.

Since selection is from remote server. And that too is giving right @@rowcount value when only select was used.

What I am suspecting is the Set IDENTITY Insert on statement, which stops @@rowcount variable to have valid values.




Kindest Regards,

Avinash

avin_barnwal@hotmail.com



vadba
vadba
SSC Eights!
SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)

Group: General Forum Members
Points: 993 Visits: 408

I ran the following test, and @@ROWCOUNT return 3, as expected. The difference here is that I don't have access to a remote server. However, since you are inserting data FROM a remote server INTO a local server, that shouldn't matter (but it might!). The SET IDENTITY_INSERT Table1 ON statement didn't adversely affect @@ROWCOUNT.

DROP TABLE dbo.Table1
GO
DROP TABLE dbo.Table2
GO

CREATE TABLE dbo.Table1
(
id int IDENTITY(1,1)
, col1 varchar(5)
, col2 varchar(5)
, col3 varchar(5)
)
GO
CREATE TABLE dbo.Table2
(
id int IDENTITY(1,1)
, col1 varchar(5)
, col2 varchar(5)
, col3 varchar(5)
)
GO
INSERT dbo.Table1 (col1) VALUES ('11111')

INSERT dbo.Table2 (col1)
SELECT '12345'
UNION ALL
SELECT '12345'
UNION ALL
SELECT '12345'

SET IDENTITY_INSERT Table1 ON

INSERT dbo.Table1 (id, Col1, Col2, Col3)
SELECT id, Col1, Col2, Col3
FROM Table2
WHERE Col1 = '12345'

SELECT @@ROWCOUNT

SET IDENTITY_INSERT [dbo].[Table1] OFF





Bulent Gucuk
Bulent Gucuk
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2900 Visits: 490

I agree with Avinash. I just tried to select and insert into a table and @@rowcount function worked. Since it's a global function there maybe something else that setting the rowcount to 0. Why don't you declare a variable and set the rowcount to the variable and check the result.

Good day...

Bulent


Avinash Barnwal
Avinash Barnwal
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 16

I tried executing the below queries and found no problem from my side.

SET IDENTITY_iNSERT [dbo].[Table1] ON

INSERT [dbo].[Table1](Col1, Col2, Col3)
SELECT Col1, Col2, Col3 FROM [remotesrv].[remotedb].[dbo].[Table2]

SELECT @@ROWCOUNT as 'rowcount'

SET IDENTITY_INSERT [dbo].[Table1] OFF

And it gives pretty gud result too........

rowcount
-----------
3

Krissy, I was wondering if you can provide me of how Linked server was added onto your native Server..




Kindest Regards,

Avinash

avin_barnwal@hotmail.com



Donald Sawford
Donald Sawford
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1883 Visits: 859
Can the actions performed by a trigger affect the @@rowcount returned?



Avinash Barnwal
Avinash Barnwal
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 16

Yes Donald, they do affect the @@rowcount variable. Say for example you have the following trigger on Table1 then you would only receive those Rowcount which may be result of the DMLs written in trigger itself.

Create trigger abcIns
on Table1
for insert
as

declare @col1var int
insert into #temptbl(timeofinsert,idoninsert) values(getdate(), @col1)

return.

Now whenever any insert is made on Table1 it will return only the rowcount of the insert statement(it may be any DML statement) inside the trigger. This carried to scope of the session in which trigger firing insert statement was issued.




Kindest Regards,

Avinash

avin_barnwal@hotmail.com



Donald Sawford
Donald Sawford
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1883 Visits: 859
Thanks,
I thought that was the case.
So maybe there is an insert trigger on table1 that is changing the @@rowcount that is being returned. This would explain why the select showed the correct @@rowcount but the insert didn't. Just a thought.



Tim Lehner
Tim Lehner
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 211
I know this topic is old, but I just tried to affect the value of @@rowcount using a trigger and I could not. I would insert 1 record into a table, then have a trigger select, insert or update many records (I tried all three) and the next @@rowcount that I ran after my initial insert would return a value of 1. An @@rowcount inside the trigger would return values greater than 1, but not the next @@rowcount in the scope of the initial insert statement. It looks to me like @@rowcount operates like scope_identity() rather than @@identity in the sense that it is scope-dependent. I got the same results in SQL 2000, 2005 and 2008r2.

Same result here:
http://stackoverflow.com/questions/7005225/sql-server-does-trigger-affects-rowcount
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search