ACC: ODBC Setup for Microsoft Access and SQL Server

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97
• Microsoft SQL Server versions 1.10, 1.11, 4.20 for MS-DOS and Microsoft OS/2, and Microsoft SQL Server for Windows NT
• Microsoft SQL Administrator versions 4.20, 4.21 for Windows
• Microsoft ISQL for Windows version 4.21

SUMMARY

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

The installation of Microsoft ODBC for Microsoft and Sybase SQL Server requires the use of ISQL command line procedures to properly run the INSTCAT.SQL script.

If INSTCAT.SQL has not been properly implemented in SQL Server, you may receive the following error message while linking (attaching) a SQL table in Microsoft Access:

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

MORE INFORMATION

To properly configure SQL Server for use with Microsoft ODBC, you must run the SQL script file INSTCAT.SQL (included on the ODBC disk in Microsoft Access version 1.x, on disk 1 in Microsoft Access version 2.0 and on disk 8 in Microsoft Access version 7.0) to set up the proper stored procedures that provide catalog information used by Microsoft ODBC.

NOTE: Microsoft SAF for MS-DOS and OS/2 is limited to 511 lines of code in a SQL script. INSTCAT.SQL has well over 511 lines of code.

Microsoft SQL Administrator version 4.20 will open the INSTCAT.SQL script, but you will not be able to run it. This is because version 4.20 of the SQL Administrator does not recognize the GO command used in INSTCAT.SQL. This has been corrected in the SQL Administrator version 4.21.

In Microsoft SQL Server version 4.20a or earlier, the proper way to install the catalog stored procedures using INSTCAT.SQL is to run INSTCAT.SQL from the command line using the SQL Server utility ISQL (Interactive SQL). If you are using Microsoft SQL Server version 4.20b for OS/2 or Microsoft SQL Server for Windows NT, you can run the INSTCAT.SQL script from within either ISQL/w (Interactive SQL for Windows), or Microsoft SQL Administrator for Windows version 4.21.

Note that in order to run the INSTCAT.SQL script, you must connect to your SQL Server as the System Administrator, or SA.

The ISQL facility is run from either the MS-DOS or OS/2 command prompt. The syntax for this procedure is as follows.

NOTE: In the following example, an underscore (_) is used as a line- continuation character. Remove the underscore from the end of the line when re-creating this example.

   isql /U <sa login name> /n /P <password> /S <SQL server name> /i_
   <drive:\path\INSTCAT.SQL> /o <drive:\path\output file name>

   /U    The login 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

Example

   isql /U sa /n /P skier /S DUMMY_SERVER /i d:\SQL\INSTCAT.SQL /o_
   d:\SQL\output.txt