Results returned from a view using linked server may vary?

Question

i have a view that is using linked server to retrieve data from a remote server in SQL Server. On each time viewing the view, the results returned are vary. For example, 1st time execution may return 100 rows of records but on 2nd time of execution, rows returned are 120 rows. Any ideas what is the cause?

Answer 1

The data on the linked server changed between executions?

Answer 2

Is your SQL Server fully patched? SQL Server 2008 and 2005 both have bug fixes out related to incorrect query results from linked servers.

Here is one example:

969997 FIX: You receive an incorrect result when you query data from a linked server that is created by using an index OLE DB provider in SQL Server 2005 or in SQL Server 2008

Answer 3

Is the linked server also a SQL Server? If not, perhaps a buggy driver? I've seen odd results, for example, due to an old Informix ODBC driver. Are you able to run something akin to SQL Profiler on the linked server to see what command it's receiving?

Answer 4

I have witnessed odd linked-server results that are a product of non-determinism written into the SQL itself, I.e. a TOP query written without an ORDER BY clause.

This problem, for example, where the chap had multiple non-unique foreign keys coming from a table source on the left hand side of a linked-server INNER JOIN, and wanted 10 rows from a remote sub-query to the right, where the end result was restricted to 10 rows itself, when it should have been greater than 10 rows.

Should definitely give your SQL a quick eye for such curiosities.

Answer 5

I'm not sure what the answer is, but (assuming that your counts of 100 and 120 are accurate) can you not capture the data from the two runs and compare it? That might give you some clues as to what's going on. For example, is it completely different datat, or is it duplicate rows (in the 120 row batch).