Upgrading the Catalog Stored Procedures

The Microsoft OLE DB Provider for SQL Server (SQLOLEDB) and Microsoft OLE DB Provider for ODBC (MSDASQL) can use a set of system stored procedures, known as catalog stored procedures, to obtain information from the SQL Server system catalog. SQL Server 2000 installs the catalog stored procedures automatically when you install or upgrade SQL Server. The Instcat.sql file includes updates to the catalog stored procedures. If the current version of SQLOLEDB or MSDASQL will be used against SQL Server version 6.5 or earlier, the SQL Server system administrator must upgrade the catalog stored procedures. Upgrading the catalog stored procedures does not affect the operation of existing SQL Server clients.

To upgrade the catalog stored procedures, the system administrator can run a script using the osql utility. To run osql, the computer must be installed as a client workstation for SQL Server. The system administrator should back up the master database before running Instcat.sql.

At a command prompt, use the osql utility to run the Instcat.sql script. For example:

C:> ISQL -Usa -Psa_password -Sserver_name -ilocation\Instcat.sql

Arguments

sa_password

System administrator password.

server_name

Name of the server on which an instance of SQL Server 2000 is installed.

location

Full path of the location of Instcat.sql. You can use Instcat.sql from an installed instance of SQL Server (the default location is C:\Program Files\Microsoft SQL Server\MSSQL\Install) or from the SQL Server 2000 compact disc (the default location is D:\platform where D is the CD-ROM drive letter and platform is the appropriate server platform directory, such as 386).

The Instcat.sql script generates many messages. Most of these indicate how Transact-SQL statements issued by the script affected rows. These messages can be ignored, although the output should be scanned for messages that indicate an execution error. When Instcat.sql is run against SQL Server 6.0, the message generated about the object sp_MS_upd_sysobj_category not existing can be ignored. The last message should indicate that Instcat.sql completed successfully.

The Instcat.sql script fails when there is not enough space available in the master database to store the catalog stored procedures or to log the changes to existing procedures. If the Instcat.sql script fails, contact your system administrator.

The system administrator can also run Instcat.sql using SQL Query Analyzer.