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

@@ROWCOUNT Expand / Collapse
Author
Message
Posted Monday, April 9, 2007 11:31 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 19, 2012 6:40 PM
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?!?
 
Post #357072
Posted Monday, April 9, 2007 11:50 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, October 13, 2014 8:08 PM
Points: 4,573, Visits: 8,353
Scope of system functions is limited to the local server.

Since no rows have been inserted or selected locally @@ROWCOUNT returns 0.
Post #357073
Posted Wednesday, April 11, 2007 12:12 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 6, 2014 5:50 PM
Points: 60, 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



Post #357385
Posted Wednesday, April 11, 2007 8:46 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:09 PM
Points: 977, Visits: 277

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




Post #357511
Posted Wednesday, April 11, 2007 8:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:59 PM
Points: 2,495, Visits: 424

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

 

Post #357515
Posted Wednesday, April 11, 2007 9:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 6, 2014 5:50 PM
Points: 60, 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



Post #357524
Posted Wednesday, April 11, 2007 11:39 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, October 10, 2014 2:41 PM
Points: 1,866, Visits: 829
Can the actions performed by a trigger affect the @@rowcount returned?



Post #357610
Posted Wednesday, April 11, 2007 12:14 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 6, 2014 5:50 PM
Points: 60, 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



Post #357618
Posted Wednesday, April 11, 2007 1:35 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, October 10, 2014 2:41 PM
Points: 1,866, Visits: 829
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.



Post #357634
Posted Monday, June 25, 2012 2:35 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 7, 2014 1:29 PM
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
Post #1320882
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse