LINKED Server does not return all rows

Question

This is a little strange for me. I have script like following

DECLARE @maxCustId INT
SELECT @maxCustId = MAX(CustomerId) FROM Customers
SELECT * INTO #temp FROM linkserver.DB.dbo.Customers where CustomerId > @maxCustId 

-- Copy records to local db Custome table

DROP TABLE #temp

But sometimes #temp does not fetch all records from linked sever.

Like I had Max CustomerId = 1138 in my local db and when I run above script my temp table (which fetch records from linked server) misses CustoemrIds 1140, 1141. Linked server has customers upto 1160. #temp table has records upto 1160 but misses two records i.e. 1140, 1141

I run that script again and again and on 4th attempt the record 1141 added in #temp table but record 1140 was still missing.

I then put following query on local server to check record in linked server

SELECT * FROM linkserver.DB.dbo.Customers where CustomerId = 1140

Above query return no records.

To verify this I went to linked server and wrote same query on server from where I have created LINKED server.

-- This is the server which I am using as linked server in my local server
SELECT * FROM Customers where CustomerId = 1140

Custoemr 1140 was in db, which I was sure that it would be there but I run above script to verify it.

I come back to my local server and run this query

SELECT * FROM linkserver.DB.dbo.Customers where CustomerId = 1140

This time my linked server returns customer 1140 which it was not returning earlier.

I run the whole query again and now my #temp table has all records.

I am so confused that why first time linked server did not return all records.

This is sample from a long procedure which copy records from linked server to local server and because of this reason my local db has less records than linked server db.

• Linked server: SQL Server 2005 Standard with SP2
• Local Server: SQL Server 2008 Web Edition with SP2
• Provider: SQL Native Client 10

Linked server security options:

• Be made using the securing context
• Remote Login
• With Passowrd

login user details:

• Server Roles: public and sysadmin
• User Mappings: sa is not mapped with db I query.
• Linked server db updated frequently
• No NOLOCK at any point in query

Any help.

Answer 1

I have seen similar behaviour with linked servers (although usually Oracle not SQL Server) and the problem was traced to the driver being used for the linked server. I see you have SQL Native Client 10 as the provider, could you try using Microsoft OLE DB Provider for SQL Server instead and seeing if it makes a difference?