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

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:

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

1.

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 <LoginName> /n /P <Password> /S <ServerName> /i _ <Drive:\Path\INSTCAT.SQL> /o <Drive:\Path\FileName> where: � <LoginName> is the name of the MSDE or SQL Server administrator (or sa). � <Password> is the password assigned to the administrator account. � <ServerName> is the name given to your MSDE or SQL Server computer. � <Drive> is the letter that points to the drive where INSTCAT.SQL is stored. � <Path> is the full path to the INSTCAT.SQL file. � <FileName> is the name given to the output file that records the results of the script. Following are descriptions of some of the command-line switches that you can use with ISQL and OSQL: /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

2.

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:\>

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.

For additional information about INSTCAT.SQL, click the article numbers below to view the articles in the Microsoft Knowledge Base: For more information on the RECONFIGURE command, see the SQL Server Books Online installed with SQL Server.