Microsoft KB Archive/242974

= PRB: Unable to Create or Modify REMOTE View Using a RIGHT JOIN in the View Designer =

Article ID: 242974

Article Last Modified on 8/27/2002

-

APPLIES TO


 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 5.0a
 * Microsoft Visual FoxPro 6.0 Professional Edition
 * Microsoft Data Access Components 2.5

-



This article was previously published under Q242974



SYMPTOMS
When you attempt to create or modify a remote view that uses a RIGHT JOIN, the RIGHT JOIN clause does not appear in the SQL SELECT syntax.

When you attempt to save the View, an error similar to the following appears:

Connectivity error: [Microsoft][ODBC Visual FoxPro Driver] Command contains unrecognized phrase/keyword



RESOLUTION
Create Views that use a RIGHT JOIN programmatically.



Steps to Reproduce Behavior
  Create a program file named Demo.prg using the following code: CREATE DATABASE testa CREATE TABLE tablea (fld1 c(10), fld2 c(10)) CREATE TABLE tableb (fld1 c(10), fld3 c(10)) CREATE SQL VIEW localsql ; AS SELECT * ; FROM tablea RIGHT JOIN tableb ; ON tablea.fld1 = tableb.fld1

MODIFY VIEW localsql

CLOSE ALL

CREATE DATABASE testb CREATE CONNECTION connect1 CONNSTRING 'DRIVER={Microsoft Visual FoxPro Driver};' + ; 'Exclusive=No;SourceType=DBC;SourceDB='+SYS(5)+SYS(2003)+'\TESTA.DBC'

CREATE SQL VIEW remotesql REMOTE CONNECTION connect1 ; AS SELECT * ; FROM testa!tablea tablea RIGHT JOIN testa!tableb tableb ; ON tablea.fld1 = tableb.fld1

MODI VIEW remotesql  Run the program file.  When the View Designer opens the local view, localsql, click the Show SQL button on the View Designer toolbar. Note that the SQL statement is: SELECT *; FROM testa!tablea RIGHT OUTER JOIN testa!tableb ; ON Tablea.fld1 = Tableb.fld1   Close the code and the View Designer-Localsql windows. When the View Designer opens the remote view, remotesql, click the Show SQL button on the View Designer toolbar and note that the SQL statement is: SELECT *; FROM {oj testa!tablea Tablea  testa!tableb Tableb ; ON Tablea.fld1 = Tableb.fld1 }  Close the code window. Press CTRL-W or from the File menu, choose Save. In the View Designer window note that an error message similar to the following appears:

Connectivity error: [Microsoft][ODBC Visual FoxPro Driver] Command contains unrecognized phrase/keyword



<div class="references_section">