Microsoft KB Archive/233508

= How to determine the compatibility level of a SQL Server 7 or SQL Server 2000 database from Visual FoxPro =

Article ID: 233508

Article Last Modified on 3/15/2005

-

APPLIES TO


 * 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 Q233508



SUMMARY
This article describes how to determine the compatibility level of a Microsoft SQL Server 7.0 or SQL Server 2000 database from Microsoft Visual FoxPro and later versions.



MORE INFORMATION
There are several compatibility issues that might affect performance and the resultsets that are returned from different versions of SQL Server. SQL Server 7.0 introduces several new data types and features. SQL Server 7.0 and later versions also let the DBA to set the compatibility level of individual databases.

The sp_dbcmptlevel stored procedure allows the DBA to set the compatibility level SQL Server 7.0 databases to SQL Server 6.0, 6.5, or 7.0. In SQL Server 2000, the sp_dbcmptlevel stored procedure lets the DBA set the compatibility level to SQL Server 6.0, 6.5, 7.0, or 8.0. This stored procedure sets certain database behaviors to be compatible with the specified version of Microsoft SQL Server.

The compatibility level of the database affects reserved keywords and the behavior of the specified database, not the entire server. The compatibility setting for a database takes effect when the database is made the current database.   Create a program file called PUBSCMPT.PRGby using the following code snippet. This makes sure that the user has the correct permissions. connection_string='DRIVER={SQL Server};SERVER=MyServer;' + ; 'DATABASE=MASTER;UID=UserName;PWD=StrongPassword' gnConnHandle=SQLSTRINGCONN(connection_string) IF gnConnHandle>0 *!* Get the Version of SQL Server sqlcommand="SELECT @@version" nVersion=SQLEXEC(gnConnHandle,sqlcommand,'getversion') IF nVersion>0 SELECT getversion versioninfo=EXP l_Test_Version=IIF("Microsoft SQL Server 7.00"$versioninfo,.T.,.F.) IF l_Test_Version *!* Version of SQL Server is 7.0 *!* Get the Compatibility Level set for the Pubs Database sqlcommand="SELECT cmptlevel FROM sysdatabases WHERE name = 'pubs'" nCmpt=SQLEXEC(gnConnHandle,sqlcommand,'sqlcmpt') IF nCmpt>0 SELECT sqlcmpt =MESSAGEBOX("Current Compatibility of PUBS is set to SQL Server " + ;              ALLTRIM(STR(cmptlevel)),48+0) ENDIF ELSE *!* Not SQL Server 7.0 =MESSAGEBOX(EXP,32,"Compatibility Level Not Available In") ENDIF =SQLDISCONN(gnConnHandle) ELSE =MESSAGEBOX("Connection Failed",16,"ODBC Problem") ENDIF ENDIF If you are using SQL Server 2000, use the following code: connection_string=''DRIVER={SQL Server};SERVER=MyServer;' + ; 'DATABASE=MASTER;UID=UserName;PWD=StrongPassword' gnConnHandle=SQLSTRINGCONN(connection_string) IF gnConnHandle>0 *!* Get the Version of SQL Server sqlcommand="SELECT @@version" nVersion=SQLEXEC(gnConnHandle,sqlcommand,'getversion') IF nVersion>0 SELECT getversion versioninfo=STRCONV(EXP,10) l_Test_Version=IIF("2000 - 8.00"$versioninfo,.T.,.F.) IF l_Test_Version *!* Version of SQL Server is 2000 (8.00) *!* Get the Compatibility Level set for the Pubs Database sqlcommand="SELECT cmptlevel FROM sysdatabases WHERE name = 'pubs'" nCmpt=SQLEXEC(gnConnHandle,sqlcommand,'sqlcmpt') IF nCmpt>0 SELECT sqlcmpt =MESSAGEBOX("Current Compatibility of PUBS is set to SQL Server " + ;              ALLTRIM(STR(cmptlevel)),48+0) ENDIF ELSE *!* Not SQL Server 7.0 =MESSAGEBOX(strconv(EXP,10),32,"Compatibility Level Not Available In") ENDIF =SQLDISCONN(gnConnHandle) ELSE =MESSAGEBOX("Connection Failed",16,"ODBC Problem") ENDIF ENDIF  Run PUBSCMPT.PRG and note the information displayed in the message box.  From the SQL Server 7.0 or later version Query Analyzer, run the following statement to set the compatibility level of the PUBS database to SQL Server 6.5: sp_dbcmptlevel PUBS,65  From Visual FoxPro run PUBSCMPT.PRG again and note the information displayed in the message box.  From SQL Server 7.0 Query Analyzer, run the following statement to reset the compatibility level of the PUBS database back to SQL Server 7.0 compatibility. sp_dbcmptlevel PUBS,70   In SQL Server 2000 Query Analyzer, run the following statement to reset the compatibility level of the PUBS database back to SQL Server 2000 compatibility. sp_dbcmptlevel PUBS,80 </ol>
 * !* Connect to SQL Server
 * !* Connect to SQL Server

<div class="references_section">