BUG: Heterogeneous Query Showplan May Not Display Site Name

ID: Q197562

The information in this article applies to:

• Microsoft SQL Server version 7.0

SYMPTOMS

When executing a heterogeneous query, the Showplan output does not always display the site name where that portion of the query plan was executed.

MORE INFORMATION

In the example below, the site ([server1]) shows where the clustered index seek is performed.

   Query:
   Select * from server2.pubs.dbo.authors A INNER REMOTE JOIN
   server1.pubs.dbo.authors B on A.au_id = B.au_id
   
      |--Nested Loops(Inner Join)
         |--Remote Query(SELECT A."au_id" Col1002,A."au_lname"
   Col1003,A."au_fname" Col1004,A."phone" Col1005,A."address"
   Col1006,A."city"
   Col1007,A."state" Col1008,A."zip" Col1009,A."contract" Col1010 FROM
   "pubs"."dbo"."authors" A)
         |--Clustered Index
   Seek(OBJECT:([pubs].[dbo].[authors].[UPKCL_auidind] AS [B]),
   SEEK:([B].[au_id]=[server1].[pubs].[dbo].[authors].[au_id]) ORDERED)

   Query:
   Select * from server1.pubs.dbo.authors A INNER REMOTE JOIN
   server2.pubs.dbo.authors B on A.au_id = B.au_id
   
      |--Nested Loops(Inner Join)
         |--Clustered Index
   Scan(OBJECT:([pubs].[dbo].[authors].[UPKCL_auidind] AS [A]))
         |--Remote Query(SELECT B."au_id" Col1002,B."au_lname"
   Col1003,B."au_fname" Col1004,B."phone" Col1005,B."address"
   Col1006,B."city"
   Col1007,B."state" Col1008,B."zip" Col1009,B."contract" Col1010 FROM
   "pubs"."dbo"."authors" B WHERE ?=B."au_id")

WORKAROUND

To work around this problem, refer to the query and determine the site by determining which site contains the objects being scanned.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0.

Additional query words: prodsql show plan

Keywords          : kbbug7.00 
Version           : WINNT:7.0
Platform          : winnt
Issue type        : kbbug
Solution Type     : kbpending

Last Reviewed: December 19, 1998