• Use the View Designer.
  
  -or-
• If programmatically creating the remote SQL VIEW, check the syntax of the command used to create the remote SQL VIEW.
  
  The correct syntax for creating a remote SQL VIEW, using a Visual FoxPro database and table as the remote data source follows:

        CREATE SQL VIEW MY_SQL_VIEW REMOTE CONNECT MYREMOTE ;
           AS SELECT FIELD_1, FIELD_2, FIELD_3 ;
           FROM table ;
           ORDER BY table.field
  						

  The correct syntax for creating a remote SQL VIEW, using a SQL Server table as the remote data source follows:

        CREATE SQL VIEW MY_SQL REMOTE CONNECT MYREMOTE ;
           AS SELECT FIELD_1, FIELD_2, FIELD_3 ;
           FROM database.owner.table table ;
           ORDER BY table.field
  						

Back to the top

Steps to Reproduce Behavior

1. Create an ODBC data source called MYREMOTE, to use in a remote SQL VIEW. MacIntosh users should create an ODBC data source using the PUBS database that ships with SQL Server. Windows users should create an ODBC data source using the TESTDATA files that ship with Visual FoxPro.
2. Copy the following code to a new program file and save it:

         CLOSE ALL
         SET SAFETY OFF
         SET MULTILOCKS ON
         * Create a database.
         CREATE DATABASE MYREMOTE
         * Create a connection.
         CREATE CONNECTION MYREMOTE DATASOURCE "MyRemote"
         * Create a remote view.
         DO CASE
            CASE _MAC
               CREATE SQL VIEW test REMOTE CONNECT MYREMOTE ;
                  AS SELECT au_lname, au_fname ;
                  FROM pubs.dbo.authors ;
                  ORDER BY authors.au_lname
            CASE _WINDOWS
               CREATE SQL VIEW test REMOTE CONNECT MYREMOTE ;
                  AS SELECT cust_id, company, city ;
                  FROM testdata!customer ;
                  ORDER BY customer.cust_id
         ENDCASE
         USE test && Use the view
         BROWSE TIMEOUT 2
         MODIFY VIEW test
         CLOSE ALL
         RETURN
   							

3. Run the program file. Note that when you encounter the MODIFY VIEW command that the "SQL: Cannot locate table" message appears.
4. Replace the DO CASE ... ENDCASE section of the program with the following code:

         DO CASE
            CASE _MAC
               CREATE SQL VIEW test REMOTE CONNECT MYREMOTE ;
                  AS SELECT au_lname, au_fname ;
                  FROM pubs.dbo.authors authors;
                  ORDER BY authors.au_lname
            CASE _WINDOWS
               CREATE SQL VIEW test REMOTE CONNECT MYREMOTE ;
                  AS SELECT cust_id, company, city ;
                  FROM customer ;
                  ORDER BY customer.cust_id
         ENDCASE
   							

5. Run the program file. Note that when you encounter the MODIFY VIEW command, that the View Designer opens without any error message.

Back to the top