HOWTO: Programmatically Reset the Compatibility Level Setting of SQL Server ODBC Driver Version 3.70

ID: Q233539


SUMMARY

Under some circumstances, it may be necessary or desirable to change the compatibility mode setting of version 3.70 of the SQL Server ODBC driver from SQL Server 6.5 to SQL Server 7.0. This article shows how to programmatically restore the compatibility level of version 3.70.0623 or later of the SQL Server ODBC driver (SQLSRV32.DLL) from SQL Server 6.5 compatibility to SQL Server 7.0 compatibility.


MORE INFORMATION

Version 3.70 of the SQL Server ODBC driver ships with MDAC 2.1, and is installed as part of Visual FoxPro 6.0 Service Pack 3.

The SQL Server ODBC driver compatibility level is application-specific and is not a global setting. The compatibility level of the SQL Server ODBC driver does not need to be set for applications connecting to earlier versions of SQL Server using version 3.70 or later of the SQL Server ODBC driver. These drivers automatically run in the appropriate compatibility mode when connecting to earlier versions of SQL Server. Likewise, the compatibility level does not need to be set for applications using earlier versions of the SQL Server ODBC driver connecting to SQL Server 7.0. SQL Server 7.0 automatically treats any application using the earlier drivers as a 6.5-level application.

The default compatibility level of the SQL Server ODBC driver version 3.70 and later is SQL Server 7.0 compatibility. The default setting exposes new GUID and Unicode data types when connecting to SQL Server 7.0. An application's compatibility level setting for the SQL Server ODBC driver may affect how data are retrieved and or displayed.

The compatibility level setting of the SQL Server ODBC driver is stored in the following registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ODBCAppCompat

If the registry key does not exist or if the application name is not listed in the registry key, then the compatibility level for the application is set to SQL Server 7.0.

On machines on which SQL Server 7.0 has been installed, the odbccmpt utility can be used to enable or disable SQL Server version 6.5 ODBC compatibility for specific applications.

To restore the compatibility level of the ODBC driver from SQL Server 6.5 to SQL Server 7.0, open a DOS Command prompt and type the following:

odbccmpt vfp6.exe -d
The odbccmpt utility is not available on machines that have not had SQL Server 7.0 installed; this necessitates another approach to setting the compatibility level of the SQL Server ODBC driver.

The following steps can be used to programmatically set the compatibility level of the SQL Server ODBC driver for a specific application.

IMPORTANT: This article contains information about editing the registry. Before you edit the registry, make sure you understand how to restore it if a problem occurs. For information about how to do this, view the "Restoring the Registry" Help topic in Regedit.exe or the "Restoring a Registry Key" Help topic in Regedt32.exe.

WARNING: Using Registry Editor incorrectly can cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that problems resulting from the incorrect use of Registry Editor can be solved. Use Registry Editor at your own risk.

For information about how to edit the registry, view the "Changing Keys and Values" Help topic in Registry Editor (Regedit.exe) or the "Add and Delete Information in the Registry" and "Edit Registry Data" Help topics in Regedt32.exe. Note that you should back up the registry before you edit it. If you are running Windows NT, you should also update your Emergency Repair Disk (ERD).

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/
For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

  1. Open the registry editor and navigate to the following registry key:


    2. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ODBCAppCompat
  2. Set the compatibility level of the ODBC Driver for Visual FoxPro 6.0 to SQL Server 6.5 compatibility mode by opening a DOS Command prompt and navigating to the MSSQL7/BINN directory and then typing the following command:


    3. odbccmpt vfp6.exe
  3. Note that the value "vfp6" with a data value of "6.5" has been entered into the registry.


  4. From Visual FoxPro, create a program file named CMPT70.PRG using the following code:


    5. 
PARAMETER what_app
IF !EMPTY(what_app)
   what_app=alltrim(what_app)
   #DEFINE	DLL_ADVAPI			"ADVAPI32.DLL"
   * Registry roots
   #DEFINE HKEY_LOCAL_MACHINE   -2147483646   && BITSET(0,31)+2
   * Registry Path
   #DEFINE ODBC_DRVR_CMPT "Software\Microsoft\MSSQLServer\Client\ODBCAppCompat"
   * Error Codes
   #DEFINE ERROR_SUCCESS     0                && OK
   #DEFINE ERROR_EOF         259              && no more entries in key
   * Data types for keys
   #DEFINE REG_SZ            1                && Data string
   #DEFINE REG_EXPAND_SZ     2                && Unicode string
   #DEFINE REG_BINARY        3                && Binary data in any form.
   #DEFINE REG_DWORD         4                && A 32-bit number.
   * Data types labels
   #DEFINE REG_BINARY_LOC    "*Binary*"       && Binary data in any form.
   #DEFINE REG_DWORD_LOC     "*Dword*"        && A 32-bit number.
   #DEFINE REG_UNKNOWN_LOC   "*Unknown type*" && unknown type

   * Declare API Functions
   * Declare RegOpenKey to open registry key
   DECLARE INTEGER RegOpenKey IN DLL_ADVAPI INTEGER hKey, ;
      STRING lpSubKey, INTEGER @phkResult

   * Declare RegDeleteValue to delete the registry setting
   DECLARE INTEGER RegDeleteValue IN DLL_ADVAPI ;
      INTEGER nHKey, STRING cSubKey

   * Declare RegEnumValue to allow examination of registry values
   DECLARE INTEGER RegEnumValue IN DLL_ADVAPI ;
      INTEGER hKey, INTEGER iValue, STRING @lpszValue, ;
      INTEGER @lpcchValue, INTEGER lpdwReserved, INTEGER @lpdwType, ;
      STRING @lpbData, INTEGER @lpcbData

   * Declare RegCloseKey to close the registry key when done
   DECLARE INTEGER RegCloseKey IN dll_advapi INTEGER hKey

   * End of API Declares
   PUBLIC phkResult
   phkResult=0
   l_Key_Value_Exists=.F.
   l_Key_Exists=.F.
   l_Key_Exists=Check_For_Key(HKEY_LOCAL_MACHINE,ODBC_DRVR_CMPT)
   IF l_Key_Exists
      l_Key_Value_Exists=Check_For_Value(what_app)
      IF l_Key_Value_Exists
         nErrCode=RegDeleteValue(phkResult,what_app)
      ENDIF
   ENDIF
   nErrCode=RegCloseKey(phkResult)
   CLEAR DLLS
ENDIF
RETURN

PROCEDURE Check_For_Key
   PARAMETER nClass_Root, cSubKey
   nErrCode=RegOpenKey(HKEY_LOCAL_MACHINE,cSubKey,@phkResult)
   IF phkResult!=0
      RETURN .T.
   ELSE
      RETURN .F.
   ENDIF

PROCEDURE Check_For_Value
   PARAMETER cProgName
   nKeyEntry=0
   DO WHILE .T.
      STORE 0 TO lpdwReserved,lpdwType,nErrCode
      STORE SPACE(256) TO lpbData, lpszValue
      STORE LEN(lpbData) TO m.lpcchValue
      STORE LEN(lpszValue) TO m.lpcbData
      nErrCode=RegEnumValue(phkResult,nKeyEntry,@lpszValue,;
         @lpcchValue,m.lpdwReserved,@lpdwType,@lpbData,@lpcbData)
      DO CASE
         CASE nErrCode = ERROR_EOF
            EXIT
         CASE nErrCode # ERROR_SUCCESS
            EXIT
      ENDCASE
      nKeyEntry = nKeyEntry + 1
      * Set array values
      DIMENSION aKeyValues[m.nKeyEntry,2]
      aKeyValues[m.nKeyEntry,1] = LEFT(m.lpszValue,m.lpcchValue)
      aKeyValues[m.nKeyEntry,2] = LEFT(m.lpbData,m.lpcbData-1)
   ENDDO
   retval=.F.
   IF nKeyEntry>0
      FOR i=1 TO ALEN(aKeyValues,1)
         IF UPPER(aKeyValues[i,1])=UPPER(cProgName)
            retval=.T.
            EXIT
         ENDIF
      NEXT
   ENDIF
   RETURN retval
  5. From the command line, type:


    6. "DO CMPT70 WITH 'VFP6'"
  6. Switch to the Registry Editor and click View.Refresh.


  7. Note that the value "vfp6" with a data value of "6.5" has been removed from the registry.


(c) Microsoft Corporation 1999, All Rights Reserved. Contributions by John Desch, Microsoft Corporation.


REFERENCES

For more information about compatibility levels of the SQL Server ODBC driver, search for odbccmpt utility in SQL Server 7.0 Books On Line

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

Q231812 HOWTO: Programmatically Set Compatibility Level of SQL ODBC Driver

Additional query words:

Keywords : kbDatabase kbSQL kbVFp600 kbVS600sp3 kbGrpFox kbDSupport kbCodeSnippet
Version :
Platform :
Issue type : kbhowto


Last Reviewed: November 20, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.