No data is returned in Visual FoxPro when you use the TOP clause to retrieve data from a table in SQL Server 7.0 or in SQL Server 2000 (260526)

MORE INFORMATION

Steps to Reproduce Behavior

The compatibility of a SQL Server 7.0 or SQL Server 2000 database can be changed by using the SP_DBCMPTLEVEL command. For example, you can issue the command SP_DBCMPTLEVEL pubs, 70 in the SQL Server 7.0 Query Analyzer window, or send it through the Visual FoxPro ODBC driver using the SQLEXEC command.

Create a program (.prg) file and run the following code, changing the database name to your database wherever necessary:

*!* Make changes to the connect string, adding your server, database,
*!*      login, and password.
MyConnect_Str = 'DRIVER={SQL Server}; SERVER=YourServer;' + ;
                'DATABASE=YourDatabase;UID=sa;PWD='
*!* Connects to SQL Server using the connect string.
MyHandle = SQLSTRINGCONN(MyConnect_Str)
IF MyHandle > 0
*!* Comment out the next line and uncomment the line after to return data.
    Change_Ver = SQLEXEC(MyHandle, "SP_DBCMPTLEVEL pubs, 65") &&Returns Error
*!* Change_Ver = SQLEXEC(MyHandle, "SP_DBCMPTLEVEL pubs, 70") &&Returns Top 5
   MyExec = SQLEXEC(MyHandle, "SELECT  TOP 5 * FROM authors")
      IF MyExec < 1
         WAIT WINDOW "SELECT statement failed!"
      ELSE
         BROWSE
         USE	
      ENDIF
ELSE
      WAIT WINDOW "Connection failed!"
ENDIF