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

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.

1. 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'
   *!* Connect to SQL Server
   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'
   *!* Connect to SQL Server
   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
   	

2. Run PUBSCMPT.PRG and note the information displayed in the message box.
3. 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

4. From Visual FoxPro run PUBSCMPT.PRG again and note the information displayed in the message box.
5. 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

6. 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