@@ROWCOUNT

Question

Post reply

@@ROWCOUNT

Krissy

Ten Centuries

Points: 1185

April 9, 2007 at 11:31 pm

#119544

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?!?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

Scope of system functions is limited to the local server.

Since no rows have been inserted or selected locally @@ROWCOUNT returns 0.

_____________
Code for TallyGenerator

Avinash Barnwal Right there with Babe Points: 716 More actions · Answer 2

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 SSChampion Points: 11133 More actions · Answer 3

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 SSCertifiable Points: 5497 More actions · Answer 4

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 Right there with Babe Points: 716 More actions · Answer 5

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 SSCrazy Points: 2778 More actions · Answer 6

Can the actions performed by a trigger affect the @@rowcount returned?

Avinash Barnwal Right there with Babe Points: 716 More actions · Answer 7

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 SSCrazy Points: 2778 More actions · Answer 8

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 SSC Enthusiast Points: 189 More actions · Answer 9

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