I'm basically trying to run queries over a few proxy tables declared in a 126.96.36.19917 database and located in a MS SQL 2008 R2 server. These queries are not very complex, i.e. mainly using joins over 3-5 proxy tables.
However, as soon as they do use an INNER JOIN and a LEFT JOIN, this doesn't work at all.
I seem to have either the choice (by setting the capability 'Unrestricted ANSI ON' OFF or ON):
However, the second approach at leads to the following incorrect syntax (the comma in the table list is rejected):
SELECT ... FROM (pt1, pt2) LEFT OUTER JOIN pt3 ON pt1.pk = pt3.pk WHERE pt1.pk = pt2.pk AND ...
Note, the original query does use an INNER JOIN between pt1 and pt2 here - which would work on MS SQL, too:
SELECT ... FROM pt1 INNER JOIN pt2 ON pt1.pk = pt2.pk LEFT OUTER JOIN pt3 ON pt1.pk = pt3.pk WHERE ...
So how can I force the remote query not to replace the INNER JOIN with a table list and a WHERE CLAUSE?
Note: If the query does only include INNER JOINs or LEFT JOINs, then they can be executed successfully, and then the inner join syntax is sent as such to the remote server...
Current setting of (possibly) relevant JOIN capabilities:
Looks like there is no way around this one. You will probably have to force no passthru for now or use sp_forward_to_remote_server() if you can. I will open a bug report and see if we can do a better job of building remote queries containing inner joins.
answered 27 Mar '13, 09:23