INF: Relationship of the ODBC Driver to INSTCAT.SQL

• Microsoft SQL Server, versions 4.21x, 6.0 & 6.5

SUMMARY

This article discusses when you should run INSTCAT.SQL against your SQL Server to support a Microsoft SQL Server ODBC driver version. Note that this information applies only to the Microsoft SQL Server ODBC Driver; if you are using SQL Server ODBC drivers from other vendors, consult the documentation for those drivers.

MORE INFORMATION

Both the SQL Server system catalog stored procedures and the ODBC API catalog functions address the need of applications to retrieve catalog information from a database. Because there is a high correlation between the ODBC catalog functions and the SQL Server system catalog stored procedures, the Microsoft SQL Server ODBC Driver implements many of the ODBC API catalog functions as calls to a corresponding SQL Server system catalog procedure. The driver is therefore dependent on the system catalog stored procedures in any SQL Server to which it connects.

Each version of the Microsoft SQL Server ODBC Driver is developed in conjunction with a specific version of SQL Server. The proper operation of each driver version requires the versions of the system catalog stored procedures associated with the specific version of SQL Server with which the driver was developed, or a later version of the procedures. For example, the 2.00.1912 driver was developed in conjunction with Microsoft SQL Server version 4.21a, and requires either the versions of the system catalog stored procedures that were released with SQL Server 4.21a or with later versions, such as 6.0. The driver will not work properly with older versions of the catalog stored procedures, such as those in SQL Server version 4.21.

If a driver attempts to connect to a SQL Server that is running an older version of the system catalog stored procedures than those required by the driver, the connection will complete with SQL_SUCCESS_WITH_INFO and a call to SQLError will return the following message:

   SqlState:   01000
   pfNative:   0
   szErrorMsg: "[Microsoft][ODBC SQL Server Driver]The ODBC
               catalog stored procedures installed on server
               ab421def are version 02.00.4127; version 06.00.0115
               or later is required to ensure proper operation.
               Please contact your system administrator."

You can determine what version of INSTCAT.SQL was last run against your SQL Server by running the following command:

   sp_server_info 500

   attribute_id   attribute_name      attribute_value
   --------------------------------------------------
   500            SYS_SPROC_VERSION   6.50.193

   attribute_id   attribute_name      attribute_value
   --------------------------------------------------
   500            SYS_SPROC_VERSION   6.00.120

   attribute_id   attribute_name      attribute_value
   --------------------------------------------------
   500            SYS_SPROC_VERSION   2.00.4127

ODBC Version    SQL Server Version     Date of INSTCAT.SQL
------------    ------------------     -------------------

1.02.3231*          4.21                  1/26/94
2.00.1912*          4.21a                 7/15/94
2.50.0121           6.00.0121             6/7/95
2.50.0126           6.00.0124 (SP 1)      6/7/95
2.65.0201           6.50.0201             4/3/96

The SQL Server database administrator (DBA) can install the proper version of the SQL Server system catalog stored procedures on the target server by running the INSTCAT.SQL script from the SQL Server install directory. This procedure should never be run by a general user, but only by the DBA. The only time running this script is necessary is if you have clients using ODBC drivers attempting to connect to older versions of SQL Server. The system catalog stored procedures installed by INSTCAT.SQL are backward- compatible. If you run a newer INSTCAT.SQL script against an older SQL Server, the only effect is that the system catalog stored procedures will be upgraded to a level that will support the newer versions of ODBC drivers. No user data will be changed, and existing applications and the older ODBC drivers will continue to work.

You should never run an INSTCAT.SQL script against any version of SQL Server if the date or version of the INSTCAT.SQL you want to run is older than the INSTCAT.SQL that came with your SQL Server. The INSTCAT.SQL that comes with SQL Server is stored in the directory MSSQL\INSTALL (6.5), SQL60\INSTALL (6.0), or SQL\INSTALL (4.21A or earlier), assuming SQL Server was installed into its default directory structure.

If you are running multiple versions of the Microsoft ODBC SQL Server drivers against a server, ensure that the server is running with at least the version of INSTCAT.SQL associated with the newest ODBC driver you are using. For example, you would need to check this if you are running multiple version 4.21a servers and then get SQL Server version 6.0 and upgrade some of your clients to use the new 2.50.0121 driver that comes with version 6.0. In this case, you will need to run the 6.0 version of INSTCAT.SQL against the 4.21a servers before the new driver can connect to the old servers. The clients will still be able to connect to the old servers with their existing 2.00.1912 ODBC driver.

In general, you should use the INSTCAT.SQL script that ships with SQL Server. The exeption to this would be if you have clients that start running ODBC drivers that require a later version of INSTCAT.SQL than came with the version of SQL Server you are running. For example, Office 95 shipped with the 2.50.0121 driver and the SQL Server 6.0 INSTCAT.SQL script. If you are running SQL Server 4.21a and some of your clients install this driver, you will have to run the INSTCAT.SQL that shipped with Office 95 against the version 4.21a server before the new ODBC drivers can connect.

In all of these cases, you need to observe the rule not to run an INSTCAT.SQL onto any server whose original INSTCAT.SQL is newer than the one you want to run.

You should run the INSTCAT.SQL script into the server with the command line ISQL utility, which is documented in the SQL Server "Transact-SQL Reference" manual:

   cd \sql60\install
   isql /Usa /Ppassword /Sservername /iinstcat.sql /oinstcat.rpt