Any chance you could post the sqlplan?

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...

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...

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...

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...

I have now tested a straight select from one of the remote tables and get the same problem when I put that inside a view...

Craig Farrell (1/18/2011)

---

Well now, that's intriguing. It's ignoring the join logic and simply sending the where clause, but it's splitting the where clause to the two foreign tables and...

At last - used IE and i can upload the plans!

Hi Gail, thanks for your time.

Security is currently (for testing purposes) set to log in remotely as "sa" - I have also tried using the login that owns the tables...

Craig Farrell (1/18/2011)

---

Ah, my brain coughed up something from the bottom of my soul and... oh, wait, I may have just remembered it.

I can't get to skydrive, cause it's blocked...

Craig, if I put the WHERE clause inside the VIEW then

SELECT * from vw_test

Works just fine.

Do you think the collation difference could be the cause and do you have...

Thanks Craig - any offer of advice on this one is welcome!

That is almost the exact query - although it is a simplified version of my real query - all...

Hi, I don't have your latest data/query to work with, but expanding on Lutz' excellent code, you could try this approach

DECLARE @ParentIdReference TABLE (Parent_ID INT,InternalID VARCHAR(10))

;WITH XMLNAMESPACES(

DEFAULT 'http://ALD.riban.com'

)

INSERT

INTO Parent

OUTPUT...

I have a question or two...

1) How many rows does that UPDATE query affect? Is it updating 1 row or more?

2) Is there some reason why the application that is...

Heh - no need for my apology for playing then - Jeff - you couldn't resist either!