Linked Server Query In A View

Question

Linked Server Query In A View

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Any chance you can drop an inline table value function on the foreign server? Even if it's in another database so it wouldn't have to cross-server communicate? If it's going to parameterize on you, you might as well take advantage of it.

Scratch that, forgot you can't call functions outside of the current database.

- Craig Farrell

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

mister.magoo SSC-Forever Points: 47174 More actions · Answer 2

I may be on to something here -

I have created a view on the remote server with all the *char columns converted to the collation of the local server Latin1_General_CI_AS

When I call that view from inside a view on the local server (i.e. the other server - the one I need to call it from) it works!

I am rebuilding the original query that way to see what happens

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

Now i am even more confused!

If I create a view on the remote server and force collation to match the local server, then the simple one table query works in a view.

If I do the same for the two remote tables (in one view so they are joined remotely) then that works in a view.

Once I add in the local table it stops working again, UNLESS I use a like in the WHERE clause instead of "="

so

select * from vw_testdet

where order_no = '203927'

fails to remote the WHERE

but

select * from vw_testdet

where order_no like '203927'

does remote the WHERE

Just a thought - would I get any mileage from forcing a query plan - if that is possible when I can't get one that works!

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

mister.magoo (1/18/2011)
Just a thought - would I get any mileage from forcing a query plan - if that is possible when I can't get one that works!

Maybe, but it has to be a plan that the optimiser could (and would) generate or you'll get an error if you try to force it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mister.magoo SSC-Forever Points: 47174 More actions · Answer 5

Just a quick update -

If I do this

SELECT *

FROM vw_testdet

where order_no like '%'

and order_no = '52021'

the WHERE clause all gets remoted - both the like and the equals....go figure!

Just working on getting the CRM to do that if I can...then I will have more time to work on it!

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

mister.magoo (1/19/2011)
Just a quick update -
If I do this
SELECT *
FROM vw_testdet
where order_no like '%'
and order_no = '52021'
the WHERE clause all gets remoted - both the like and the equals....go figure!
Just working on getting the CRM to do that if I can...then I will have more time to work on it!

Whaaaaaa..... ?! :crazy:

- Craig Farrell

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

mister.magoo SSC-Forever Points: 47174 More actions · Answer 7

Solution:

Well I think I finally found the problem....ready....datatype conversion.

The remote column that I was selecting on "order_no" had a type of CHAR(10).

The query was SELECT .... FROM ... WHERE order_no = '123456'

Looking closer at the XML execution plan, I saw an implicit convert of the constant '123456' to NCHAR(10) so on a whim I explicitly converted my constant to CHAR(10).

SELECT ... FROM ... WHERE order_no = CONVERT(CHAR(10),'123456')

And vóila - the query was remoted properly with no need for LIKE.

AND it worked using a view.

So, I have my solution for now. (this is only a temporary solution anyway as this code will be replaced by something more efficient and resilient in a few months)

I still don't understand why the explicit conversion was not required unless the SELECT from the remote source was inside a VIEW, nor why it is needed at all. I still suspect that the COLLATION conflict is a factor but again don't know exactly why.

So, thanks Craig and Gail for your help.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

mister.magoo (1/19/2011)
Solution:
Well I think I finally found the problem....ready....datatype conversion.
The remote column that I was selecting on "order_no" had a type of CHAR(10).
The query was SELECT .... FROM ... WHERE order_no = '123456'
Looking closer at the XML execution plan, I saw an implicit convert of the constant '123456' to NCHAR(10) so on a whim I explicitly converted my constant to CHAR(10).
SELECT ... FROM ... WHERE order_no = CONVERT(CHAR(10),'123456')
And vóila - the query was remoted properly with no need for LIKE.
AND it worked using a view.
So, I have my solution for now. (this is only a temporary solution anyway as this code will be replaced by something more efficient and resilient in a few months)
I still don't understand why the explicit conversion was not required unless the SELECT from the remote source was inside a VIEW, nor why it is needed at all. I still suspect that the COLLATION conflict is a factor but again don't know exactly why.
So, thanks Craig and Gail for your help.

Wow! What a pain! Thanks for the feedback on that solution, Magoo!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

I beat it into the heads of all my client's staff (and SQL Saturday attendees) to ALWAYS use the correct datatypes in ALL scenarios. 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service