Inconsistent Error Messages When Querying Linked SQL Server?

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?

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?

Thank you Gareth. That is extremely helpful. First, regarding the join to the URL table you're right in that it's essentially a CROSS JOIN where the URLType='LOB_ClientRecord'.

It seemed like it would be better to do it as a LEFT OUTER JOIN though, because as a CROSS JOIN the query would have to return all the results and then apply the WHERE clause. I tested it both ways, and the performance seemed to be better using the LEFT OUTER JOIN. – bcampbell May 8 at 4:32 I will definitely try out using OPENQUERY.

The only problem is that the databases are sometimes on the same server, depending on our clients' environments. I was hoping to have just one set of sql sripts, but given the nature of querying accross linked servers, that might not be possible. Thanks again!

– bcampbell May 8 at 4:42 You could make the subquery a SP which you implement on all platforms. When you run the query, you chose whether to run against the linked or local server – Gareth May 8 at 18:58 On the LEFT OUTER JOIN/CROSS JOIN issue I would check the associated query plans to see what is different. SQL Server should be producing the same query plan (sometimes minor changes to queries can cause SQL server to produce substantially different query plans).

I would try to avoid using the LEFT OUTER JOIN if that is not really what you are doing (and it will be confusing to the next person who has to support your code). If you need to force a specific query plan use hints. – Gareth May 8 at 19:05 Alternatively you could do "SELECT ... FROM ... CROSS JOIN (SELECT ... FROM dbo.

KA_LOB_Config_URLs WHERE URLType='LOB_ClientRecord') as URL" to indicate to SQL server to filter on 'LOB_ClientRecord' first. If performance is still not what you expect, check the query plan. – Gareth May 8 at 19:12.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.