May 6, 2011 at 2:36 pm
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:
SelectCL.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.ClientID 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?
May 6, 2011 at 2:45 pm
something is missing;
the error raise typically occurs when you assigning a variable' like SELECT SomeVar = SomeOtherField,
or in a WHERE statement... WHERE SomeVar = SomeOtherField,
JOINS on field = otherfield are not the issue.
there's no WHERE statement in what you pasted, and the only thing i see is the {0} from when you String.Format it later.
can you past the {0} value or the WHERE statement that occurs when you get the error?
That's where the error is occurring. if it is in the WHERE, it's simply a matter of changing it to an IN() statement.
Lowell
May 6, 2011 at 2:48 pm
bcampbell-1136357 (5/6/2011)
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?
There's nothing wrong with this I can see. I'm assuming you simply swapped out server and database names via find/replace. Are these all on the same linked server?
As a workaround if they are you could put this into a proc on the foreign server and do a remote call. I have no idea why you'd get that error unless for some reason you are hitting a view instead of a table via the remote... but the fact that it's completely inconsistent would rule that out in theory unless you have incredibly transient data somewhere.
*scratches head* Yeah, I have no clue as to why it's happening. I can give you some more workaround ideas, but the root cause I don't think I could troubleshoot via forum.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 6, 2011 at 3:03 pm
Thanks Lowell.
There is no where clause in the query and no parameters. I am trying to pull all the values into a view in my data warehouse.
The value for {0} that I'm replacing in the string is the CL.ClientID. The string itself is a URL located in the KA_LOB_Config_URLs table.
That table has two columns, URLType and URL. I'm using a LEFT OUTER JOIN to that table and specifying the value of the URLType='LOB_ClientRecord', which only returns one record, something like:
http://servername/launchApp.aspx?initialPage=Clients&keyValue={0}
I guess that join could also be written as a CROSS JOIN with a WHERE clause at the end (i.e. CROSS JOIN KA_LOB_Config_URLs WHERE URLType='LOB_ClientRecord'), but that shouldn't change the result set and might in fact negatively affect the performance.
Hope that helps to clarify the issue.
May 6, 2011 at 3:13 pm
Thanks Craig. You're exactly right - I just changed the Linked Server and Database Names. In this case, yes, where I say "LinkedServer" it is referring to the same linked server.
Putting a stored procedure into the database on the linked server isn't an option. I'm creating a data warehouse for multiple clients that queries their ERP database. So I have to have a stand-alone database that I can deploy as a solution.
The workaround I've used in the past has been to split the query into smaller chunks and select the result sets into temp tables, then do all my joins against the temp tables. And this has worked. But is gross, and I'm most concerned about the inconsistency of the error. It doesn't happen 9 times out of 10. We first discovered the issue when deploying to a client, and it took me a zillion tries to replicate it in our internal dev environment.
I think you're right that it's going to be difficult to troubleshoot on a forum, but I'm not giving up just yet! And I really think it's a linked server thing.
May 6, 2011 at 3:13 pm
darn; i was guessing you were appending a WHERE statement...
ok how about the REPLACE?
this section:
Replace(URL.URL,'{0}',RTRIM(CL.ClientID)) as LOB_CompanyRecord,
uf Url.URL has one row, but CL.ClientID has multiple rows, that oculd cause the error right?
Lowell
May 6, 2011 at 3:21 pm
Lowell (5/6/2011)
darn; i was guessing you were appending a WHERE statement...ok how about the REPLACE?
this section:
Replace(URL.URL,'{0}',RTRIM(CL.ClientID)) as LOB_CompanyRecord,
uf Url.URL has one row, but CL.ClientID has multiple rows, that oculd cause the error right?
Wouldn't be a problem here Lowell because that's in the result section after all the joins occurred. There's a row per url/clientid combo at that point, so it'll be one to one there.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 6, 2011 at 3:50 pm
Out of curiosity, are you using linked servers to pull data into a warehouse?
The optimizer, in general, does a pretty good job. But, with linked servers it makes it very difficult to predict what the optimizer will do. Which can lead to major performance issues do to the nature of how SQL handles joins across linked servers. In most cases this type of design is predicated on using linked servers, so I've had to tell companies that their architecture is flawed and will have to be re-worked (from the ground up) in order to get performance up to acceptable levels. So, what I'm getting at is, if you can avoid linked servers now, you might be able to save a lot of grief/embarrassment later. But, if your data volume is low enough, you might be able to get away with using linked servers.
May 6, 2011 at 4:24 pm
Thanks for your response.
Unfortunately, I can't control whether or not the data I'm pulling is located on the same server as my data warehouse or on a linked server, since my firm is a vendor. The volume of data that we're pulling is low enough that it hasn't been a problem, and the queries are in stored procs that run nightly. So I'm not concerned so much with the performance.
I know that there are some limitations out there with querying linked servers - for example, there are limits on the number of CASE WHEN statements you can have in a query. I'm just wondering if there are other limitations I'm not aware of, such as the number of joins or other types of limitations.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply