Microsoft KB Archive/260526

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

Article ID: 260526

Article Last Modified on 2/12/2007

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft Visual FoxPro 3.0b Standard Edition
 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 5.0a
 * Microsoft Visual FoxPro 6.0 Professional Edition
 * Microsoft Visual FoxPro 7.0 Professional Edition
 * Microsoft Visual FoxPro 8.0 Professional Edition
 * Microsoft Visual FoxPro 9.0 Professional Edition

-



This article was previously published under Q260526



SYMPTOMS
When using the TOP clause in a SELECT-SQL statement that is retrieving data from a table in a SQL Server 7.0 or SQL Server 2000 database, no data is returned to Visual FoxPro.



CAUSE
The TOP clause is new to SQL Server 7.0 and SQL Server 2000. The reason that no data is being returned is that the compatibility mode on the database from which the data is being retrieved is set to 6.5.



RESOLUTION
Change the compatibility mode of the database to 7.0 or 8.0 (for SQL Server 2000). This allows the correct data to be returned. The different modes for SP_DBCMPTLEVEL are 60, 65, 70, 80; see SP_DBCMPTLEVEL in SQL Server Books Online for more information.



STATUS
This behavior is by design.



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

