Inconsistent Error Messages When Querying Linked SQL Server

I'm building a data warehouse that queries databases that are sometimes located on linked servers.

When executing some queries, I have occasionally encountered the following error message

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Unfortunately, in my case, I don't have any subqueries within my select statement.

Following is an example of one of these select statements where I have received the error:

Select  CL.ClientID as CompanyID, CL.Client, CL.Name, CT.Description, CS.Label, 
    CCF.PrimaryDiscipline, CCF.DisciplineDescription, CL.WebSite, CL.Memo, 
    CCS.CurrentStatus, CLA.Address, CLA.Address1, CLA.Address2, CLA.Address3, 
    CLA.Address4, CLA.City, CLA.State, CLA.ZIP, CO.Country, CLA.Phone, CLA.Fax, 
    CLA.EMail, CL.PriorWork, CL.Recommend, CL.DisadvBusiness, CL.SmallBusiness, CL.MinorityBusiness, 
    CL.HBCU, CL.WomanOwned, CL.VetOwnedSmallBusiness, CL.DisabledVetOwnedSmallBusiness,
    CASE WHEN CL.LinkedVendor is not null THEN 'Vendor' ELSE null END as LinkedCompanyType, 
    CL.LinkedVendor as LinkedCompanyID, VE.Name as LinkedCompanyName, 
    'Client' as LOB_EntityCategory, Replace(URL.URL,'{0}',RTRIM(CL.ClientID)) as LOB_CompanyRecord,
    CCF.LOB_CV_CustVar01, CCF.LOB_CV_CustVar02, CCF.LOB_CV_CustVar03, CCF.LOB_CV_CustVar04,
    CCF.LOB_CV_CustVar05, CCF.LOB_CV_CustVar06, CCF.LOB_CV_CustVar07, CCF.LOB_CV_CustVar08,
    CCF.LOB_CV_CustVar09, CCF.LOB_CV_CustVar10, CCF.LOB_CV_CustVar11, CCF.LOB_CV_CustVar12,
    CCF.LOB_CV_CustVar13, CCF.LOB_CV_CustVar14, CCF.LOB_CV_CustVar15, CCF.LOB_CV_CustTxt01,
    CCF.LOB_CV_CustTxt02, CCF.LOB_CV_CustTxt03, CCF.LOB_CV_CustTxt04, CCF.LOB_CV_CustTxt05,
    CCF.LOB_CV_CustNum01, CCF.LOB_CV_CustNum02, CCF.LOB_CV_CustNum03, CCF.LOB_CV_CustNum04,
    CCF.LOB_CV_CustNum05, CCF.LOB_CV_CustDat01, CCF.LOB_CV_CustDat02, CCF.LOB_CV_CustDat03,
    CCF.LOB_CV_CustDat04, CCF.LOB_CV_CustDat05, CCF.ShowInClientDirectory
FROM    [LinkedServer].DatabaseName.dbo.CL 
INNER JOIN dbo.KA_LOB_Clients_CustomFields as CCF 
    ON CL.ClientID=CCF.ClientID 
INNER JOIN [LinkedServer].DatabaseName.dbo.CFGClientStatus as CS 
    ON CL.Status=CS.Status
LEFT OUTER JOIN [LinkedServer].DatabaseName.dbo.CFGClientCurrentStatus as CCS 
    ON CL.CurrentStatus=CCS.CurrentStatus 
LEFT OUTER JOIN [LinkedServer].DatabaseName.dbo.CFGClientType as CT 
    ON CL.Type=CT.Code 
LEFT OUTER JOIN [LinkedServer].DatabaseName.dbo.CLAddress as CLA 
    ON CL.ClientID=CLA.Cl开发者_如何学运维ientID and CLA.PrimaryInd='Y' 
LEFT OUTER JOIN [LinkedServer].DatabaseName.dbo.VE 
    ON CL.LinkedVendor=VE.Vendor 
LEFT OUTER JOIN [LinkedServer].DatabaseName.dbo.CFGCountry AS CO 
    ON CLA.Country=CO.ISOCountryCode 
LEFT OUTER JOIN dbo.KA_LOB_Config_URLs as URL 
    ON URL.URLType='LOB_ClientRecord' 

You'll notice that many of these queries are accross a linked server. I do not encounter the error message when the data warehouse is located on the same server.

To make matters even more complicated, there is no consistency to the error. If I remove all the named columns and replace with Select * From... it works fine.

If I query a different database with the exact same schema it works fine. If I move the database to another linked server, it occasionally works.

If I remove a couple of the joins, it works fine, but I can remove varying combinations of joins to similar success, which means I can't narrow down the error to a single table or join.

It also doesn't seem to matter whether my data warehouse or the database I'm querying is located on SQL Server 2005 or 2008. They seem to fail equally (though not necessarily consistently).

The only consistent element to this problem is that it only occurs when querying accross a linked server.

Does anyone know of any limitations of querying linked servers that I'm not aware of?

Or if there is something wrong with my query that I'm failing to see?

It is making a subquery because it creates a subquery (or queries) to execute on the linked server.

Be very careful with queries to linked servers...as SQL server may choose extraordinarily inefficient query plans. For example, in this scenario, SQL server may do six different selects for the six remote tables then do the join locally (as well as sending a large amount of data between servers, you would lose any benefit of indexes on your remote tables).

If I were you I would refactor the query into two parts:

• create a query for the linked table data you need and run this in a OPENQUERY statement i.e.

  OPENQUERY('Linked Server','SELECT .... FROM DatabaseName.dbo.CL INNER JOIN ...')

This will ensure the join for the remote tables will be done on the remote server.

• join the result of this query to your query on the local tables:

i.e.

SELECT ... FROM OPENQUERY(..) as REM INNER JOIN dbo.KA_LOB_Clients_CustomFields as CCF 
    ON REM.ClientID=CCF.ClientID LEFT OUTER JOIN dbo.KA_LOB_Config_URLs as URL 
    ON URL.URLType='LOB_ClientRecord'

As a side effect, I suspect your problem will go away if you do this.

BTW, I don't understand what you are trying to do with the join to the URL table. Are you really wanting to do a CROSS JOIN instead of a LEFT OUTER JOIN?