Query against linked server (KBSQL) doesn't return all rows

• SiB

  SSC Journeyman

  Points: 91

  More actions

  October 30, 2009 at 5:03 am

  #385683

  Hi,

  I have a problem running queries against a particular table on a linked server. The linked server is a KB-SQL (product URL) database that translates MUMPS (wiki URL) data into relational data.

  I run DTS jobs to import data into a local database on our SQL2000 server. They work without errors in most cases, but on one table in particular I have to allow some errors in the datapump step to get all the rows. There are approx 1.75 million rows in the table and I get 5 errors at approximately the same row numbers each time. The DTS datapump log is as shown below:

  Execution Started: 28/10/2009 15:11:35

  @@LogSourceRows: \\xxx\SQL_DTS_Logs\MConnect_CRTest_Datapump.txt.Source

  @@LogDestRows: \\xxx\SQL_DTS_Logs\MConnect_CRTest_Datapump.txt.Dest

  @@ErrorRow: 159302

  Error at Source for Row number 159302. Errors encountered so far in this task: 1.@@SourceRow: Not Available

  @@DestRow: Not Available

  @@ErrorRow: 255119

  Error at Source for Row number 255119. Errors encountered so far in this task: 2.@@SourceRow: Not Available

  @@DestRow: Not Available

  @@ErrorRow: 756277

  Error at Source for Row number 756277. Errors encountered so far in this task: 3.@@SourceRow: Not Available

  @@DestRow: Not Available

  @@ErrorRow: 861552

  Error at Source for Row number 861552. Errors encountered so far in this task: 4.@@SourceRow: Not Available

  @@DestRow: Not Available

  @@ErrorRow: 1056731

  Error at Source for Row number 1056731. Errors encountered so far in this task: 5.@@SourceRow: Not Available

  @@DestRow: Not Available

  @@ExecutionCompleted

  I'm guessing that the issue is corrupt index on the source, but cannot get this confirmed at present.

  My question is why SQL Query Analyzer doesn't report the fact that it didn't complete normally? I have tried both 4-part notation and the openquery method.

  Note that I have two linked servers (same DSN, different logins), storing data for different populations. It is only the 'KBSQL_K' one that exhibits the problem, as shown by the results below:

  SELECT COUNT(*) FROM KBSQL_K..R.CR;

  --Result: 1748082 (i.e. successfully counted every row)

  SELECT * FROM KBSQL_K..R.CR;

  --Result: 159301 row(s) affected - INCOMPLETE- NB the first error row shown by DTS is at row 159302 for the same query.

  SELECT PK FROM KBSQL_K..R.CR;

  --Result: (159301 row(s) affected) - same erroneous result, even if just a single column is requested.

  Further queries, using OpenQuery rather than 4-part notation:

  select count(*) from openquery(KBSQL_K,'select * from R.CR')

  --result:159301 (incorrect)

  select * from openquery(KBSQL_K,'select COUNT(*) from R.CR')

  --result:1747942 (correct)

  Same queries on 2nd population - note identical counts this time:

  select count(*) from openquery(KBSQL_B,'select * from R.CR')

  --result:498013 (correct)

  select * from openquery(KBSQL_B,'select COUNT(*) from R.CR)

  --result:498013 (correct)

  Summary: DTS datapump returns all the expected rows (with a small number of "Error at source" rows) whereas Query Analyzer can count the rows successfully but doesn't return them all when using "Select *" or "Select PK".

  Why does it return short, incomplete result set _without_ indicating that it encountered a problem?

  Is there an option that I've overlooked?

  How can I guard against erroneous query results in this scenario?

  Thanks for reading. Relevant knowledge, sound advice and suggestions sought.

  SiB