Microsoft KB Archive/209828

= ACC2000: Update ODBC Stored Procedures with Office/Access 2000 SQL Script =

Article ID: 209828

Article Last Modified on 1/26/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition
 * Microsoft SQL Server 4.20 Enterprise Edition
 * Microsoft SQL Server 1.1 Standard Edition
 * Microsoft ISQL for Windows
 * Microsoft SQL Server 6.5 Service Pack 5a
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 7.0 Service Pack 1
 * Microsoft SQL Server 7.0 Service Pack 2

-



This article was previously published under Q209828



This article applies only to a Microsoft Access database (.mdb).

Advanced: Requires expert coding, interoperability, and multiuser skills.



SUMMARY
To ensure the proper operation of your Microsoft Data Engine (MSDE) or SQL Server computer, run the INSTCAT.SQL script with the Query Analyzer, ISQL, or OSQL utility. If INSTCAT.SQL has not been properly implemented on MSDE or a SQL Server, you may receive the following error message when you try to link to an MSDE or SQL Server table within Microsoft Access:

[Microsoft][ODBC SQL Server Driver] The ODBC catalog stored procedures installed on server  are version xx.xxxx; version xx.xx.xxxx is required to ensure proper operation. Please contact your system administrator.



MORE INFORMATION
To update the ODBC catalog of stored procedures on your MSDE or SQL Server computer, you must run the SQL script file INSTCAT.SQL to set up and properly configure the catalog information used by Microsoft ODBC. In order to run the INSTCAT.SQL script, you must be able to connect to your MSDE or SQL Server computer as the System Administrator (or sa).

On the Access 2000 CD, the Office Professional 2000 CD, or the Office Premium 2000 CD, you can find the INSTCAT.SQL file in the System folder.

What follows are the methods that you can use to run INSTCAT.SQL script against MSDE or SQL Server.

Using Query Analyzer

 * 1) Start Query Analyzer and when you are prompted, log on to the appropriate MSDE or SQL Server.
 * 2) On the File menu, click Open.
 * 3) In the Look in list of the Open Query File dialog box, browse to the folder where you are storing INSTCAT.SQL.
 * 4) Select INSTCAT.SQL, and then click Open. The script appears in the query window with the mouse pointer located at the top.
 * 5) Scroll down to the bottom of the script, type RECONFIGURE, and then press ENTER.
 * 6) Type GO, and then press ENTER.
 * 7) On the Query menu, click Execute to run the script INSTCAT.SQL and to reconfigure the server.

Using ISQL or OSQL
  At an MS-DOS prompt, type the following:NOTE: In the following command, an underscore (_) is used as a line-continuation character. Remove the underscore from the end of the line when you type this command. osql /U  /n /P  /S  /i _  /o  where:   is the name of the MSDE or SQL Server administrator (or sa). <Password> is the password assigned to the administrator account.</li> <ServerName> is the name given to your MSDE or SQL Server computer.</li> <Drive> is the letter that points to the drive where INSTCAT.SQL is stored.</li> <Path> is the full path to the INSTCAT.SQL file.</li> <FileName> is the name given to the output file that records the results of the script.</li></ul>

Following are descriptions of some of the command-line switches that you can use with ISQL and OSQL:

<pre class="fixed_text">   /U    The logon name for the system administrator /n   Eliminates line numbering and prompting for user input /P   Password used for the system administrator (case sensitive) /S   The name of the server to set up    /i    Provides the drive and fully qualified path for the location of          INSTCAT.SQL /o   Provides isql with an output file destination for results or          the process including errors These command-line switches are case-sensitive. For example, an uppercase switch /S is for the server name. A lowercase switch /s is for the colseparator (that is column separator).

For example, a command-line would look similar to: osql /U sa /n /P simonsays /S MachServer /i D:\System\INSTCAT.SQL /o _ C:\MSSQL7\Log\Output.txt </li>  After you run the INSTCAT.SQL script using ISQL or OSQL, reconfigure the Master database as follows.NOTE: In this next sample, user sa has a blank password. Therefore, nothing follows the /P switch. If you exclude the /P switch, you will be prompted to enter a password. Also, the /S switch is excluded. Therefore, ISQL or OSQL defaults to using MSDE or SQL Server on the local computer where you are located. C:\>osql /U sa /P 1> RECONFIGURE 2> GO 1> quit C:\> </li></ol>

Osql.exe can be found in the Sql\X86\Binn folder on the Office/Access 2000 CD or if MSDE has been installed, OSQL will be found in the MSSQL7\Binn folder. You must own a licensed copy of SQL Server to use ISQL.

Using a Pass-Through Query
You can use pass-through queries to run scripts. Although you could use a pass-through query to run INSTCAT.SQL, Microsoft does not recommended using this method. To do so, you would need to first modify INSTCAT.SQL before a pass-through query can successfully run the script. Comments and GO statements, for example, would have to be removed from the script, along with other necessary modifications. Microsoft Technical Support will not assist with the modification or debugging of INSTCAT.SQL.

Also, you may not exceed approximately 64,000 characters in the SQL window in Microsoft Access. This limit represents about one-tenth of INSTCAT.SQL.

<div class="references_section">