Linked Server bug on count(*) records?

I am using SQL Server 2008 and having a linked server to an Oracle DB. Both DBs are in my local computer using Win 7.

Very interestingly, the count(*) does not work for one syntax but works in another syntax.

I run the following SQL statement in SQL Server Management Studio.

select COUNT(*) from openquery([hr2_major], 'select * from user_tables'); -- result is 106
select COUNT(*) from [hr2_major]...user_tables; -- result is 106
开发者_开发技巧exec('select count(*) from user_tables') at [hr2_major]; --result is 206

The results are different!

Does anyone know what wrong with the first two statements?

Thanks

Update

What I am doing is migrating data from SQL server to Oracle and validating the number of records of all tables at final stage.

With the third statement, we could do such things like

declare @recordCount int;
declare @countTable table(c int);
insert into @countTable exec('select count(*) from user_tables') at [hr2_major];
select @recordCount = c from @countTable;

However it works only if MSDTC is turned on. Otherwise, exception will be thrown in the insert statement. Due to the privilege limitation, I cannot turn MSDTC on the server. So the third statement is not able to use.

So my question is how to make either the first or the second statement works. Thanks!

Another update:

Here is the T-SQL for simulate the same problem.

exec('drop table test_table') at [hr2_major];
exec('create table test_table(col1 int)') at [hr2_major];
declare @i int;
declare @sql varchar(8000);
set @i = 0;
while @i < 500
begin
    set @sql = 'insert into test_table(col1) values(' + CONVERT(varchar(10), @i) + ')';
    exec(@sql) at [hr2_major];
    set @i = @i + 1;
end
select COUNT(*) from [hr2_major]...test_table  -- result is 200
select COUNT(*) from openquery([hr2_major], 'select * from test_table') -- result is 200
exec('select count(*) from test_table') at [hr2_major] -- result is 500

I suspect the answer may be that the two formats are using separate sessions/connections.

The one that returns 500 uses the same syntax as the INSERT, so is likely to run under the same connection/session. That means it will include the records which have been inserted by that session but not committed.

The others, which return 200, are probably establishing a separate session which doesn't see the uncommitted inserts.

In short, they are both 'right' depending on your definition of 'right'