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

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

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.

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

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

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..

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.