SET IDENTITY INSERT [dbo].[Table1] ON
INSERT [dbo].[Table1](Col1, Col2, Col3)
SELECT Col1, Col2, Col3
WHERE Col1 = '12345'
SET IDENTITY INSERT [dbo].[Table1] OFF
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.Table1GODROP TABLE dbo.Table2GO
CREATE TABLE dbo.Table1( id int IDENTITY(1,1), col1 varchar(5), col2 varchar(5), col3 varchar(5))GOCREATE TABLE dbo.Table2( id int IDENTITY(1,1), col1 varchar(5), col2 varchar(5), col3 varchar(5))GOINSERT 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'
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.
I tried executing the below queries and found no problem from my side.
SET IDENTITY_iNSERT [dbo].[Table1] ONINSERT [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........
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 abcInson Table1for insertas
declare @col1var int insert into #temptbl(timeofinsert,idoninsert) values(getdate(), @col1)
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.