**************************************************************** MICROSOFT SQL SERVER MICROSOFT SQL SERVER ODBC DRIVER SETUP README, VERSION 3.7 **************************************************************** This file describes using the Microsoft SQL Server ODBC driver version 3.7 with Microsoft SQL Server version 6.5 or earlier. Because Microsoft SQL Server ODBC driver version 3.7 is shipped with SQL Server 7.0, SQL Server 7.0 users should refer to SQL Server 7.0 documentation for this driver instead of the documentation in this Readme file. The topics covered are: 1. Overview 2. Installing Instcat.sql on the server 3. Obtaining the SQL Server Client Net-Libraries 4. Documentation sources regarding using ODBC with SQL Server 5. Using the driver in a development environment 6. Compatability issues **************************************************************** 1. Overview The SQL Server ODBC driver version 3.7 is a Win32ODBC version 3.51 driver. It can be used with applications written to either the ODBC 2.X or ODBC 3.X APIs. The driver works with SQL Server version 4.21a or later. The driver runs on Windows 98, Windows 95, or Windows NT (version 4.0 or later). A Win32 SQL Server 7.0 client Network utility is also installed with the SQL Server ODBC driver version 3.7. This SQL Server client Network utility can be used with SQL Server version 4.21a or later, and the client Net-Libraries that come with those versions of SQL Server. **************************************************************** 2. Installing Instcat.sql on the server The SQL Server ODBC driver uses a set of system stored procedures, known as catalog stored procedures, to obtain information from the SQL Server system catalog. Each version of the SQL Server ODBC driver is developed to work with a specific version of the catalog stored procedures. The Instcat.sql file included with the SQL Server ODBC driver version 3.7 includes minor updates to the catalog stored procedures that upgrade the procedures to the versions used by this driver. The Instcat.sql file shipped with the SQL Server ODBC driver version 3.7 is the same as the Instcat.sql file shipped with SQL Server 7.0. SQL Server 7.0 sites need not run the Instcat.sql. The SQL Server system administrator must use the Instcat.sql script to upgrade the catalog stored procedures to ensure the proper operation of the driver. Upgrading the catalog stored procedures does not affect the operation of older SQL Server clients. This must be done for all versions of SQL Server from version 4.21a to 6.5. This upgrade is not needed if you have SQL Server 7.0. To upgrade the catalog stored procedures on SQL Server 4.21a, 6.0, or 6.5, the system administrator runs Instcat.sql script using the isql utility (see the following instructions). Before making any changes to the master database, the system administrator should back it up. To run isql, your computer must be installed as a client workstation for SQL Server. At a command prompt, use the isql utility to run the Instcat.sqlscript. For example: C:>ISQL /Usa /Psa_password /Sserver_name /ilocation\Instcat.Sql where sa_password Is the system administrator's password. server_name Is the name of the server on which SQL Server resides. location Is the full path of the location of Instcat.Sql. The Instcat.sql script generates many messages. Most of these indicate how many rows were affected by the Transact-SQL statements issued by the script. Most of these messages can be ignored, although the output should be scanned for messages that indicate an execution error. When Instcat.sql is run on SQL Server version 6.0, the message that says the object sp_MS_upd_sysobj_category does not exist 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. **************************************************************** 3. Obtaining the SQL Server Client Net-Libraries The SQL Server ODBC driver uses the SQL Server client Net-Libraries to communicate with the server. The SQL Server ODBC driver version 3.7 also uses the SQL Server Client Configuration utility to manage the Net-Library associated with an ODBC data source. The SQL Server ODBC driver version 3.7 installs only one Net-Library: the Win32 named pipe Net-Library, Dbnmpntw.dll. You can use the SQL Server ODBC driver version 3.7 with older Win32 Net-Libraries. If a Net-Library other than the named pipe Net-Library is needed to connect to SQL Server, you can use the Net-Library that came with your version of SQL Server. You can get the SQL Server Net-Libraries by installing the Win32 SQL Server Client utilities for your version of SQL Server. The version of the SQL Server Client Network utility installed with the SQL Server ODBC driver version 3.7 can be used with the client Net-Libraries from SQL Server 4.21a or later. **************************************************************** 4. Documentation sources regarding using ODBC with SQL Server The Microsoft SQL Server ODBC driver version 3.7 is same driver that is shipped with SQL Server 7.0. SQL Server 7.0 users can refer to SQL Server 7.0 documentation for the SQL Server ODBC driver version 3.7. When SQL Server ODBC driver version 3.7 is used with SQL Server (version 4.21a, 6.0, or 6.5), the driver operates in the same manner as the older drivers. You can use the driver-specific information supplied with that version of SQL Server. This includes: * The drvssrvr.hlp file that shipped with the earlier version of SQL Server. * The section "Programming ODBC for Microsoft SQL Server" in the SQL Server 6.5 manuals. * The white paper "Using ODBC with Microsoft SQL Server" available on MSDN. The Microsoft SQL Server ODBC driver version 3.7 also complies with additional driver-specific information in the technical note "Using ODBC with Microsoft SQL Server," which is available on MSDN. The Sqlsodbc.hlp file that ships with the SQL Server ODBC driver version 3.7 contains only context-sensitive help for the SQL Server ODBC Data Source wizard. The Drvssrvr.hlp file that shipped with earlier versions of the SQL Server ODBC driver contained driver-specific information for older versions of the driver. The information contained in the older versions of Drvssrvr.hlp is duplicated in the SQL Server 6.5 manual "Programming ODBC for Microsoft SQL Server." **************************************************************** 5. Using the driver in a development environment The SQL Server ODBC driver uses driver-specific parameters for several ODBC function calls. #defines for these driver-specific parameters and driver-specific C and C++ programming structures are contained in the include file Odbcss.h. The SQL Server ODBC driver version 3.7 works with the Odbcss.h file provided in the following sources: * SQL Server 7.0 * SQL Server 6.5 Service Pack 2 (SP2) or later * MDAC SDK The MDAC SDK is part of the Microsoft Developer Network Professional edition. The SDK can also be downloaded from the Microsoft Web site at www.microsoft.com/data. The SDK is also available from Microsoft Press in the "Microsoft ODBC 3.0 Software Development Kit and Programmer's Reference." **************************************************************** 6. Compatability issues Since ODBC driver version 3.7 is shipped with SQL Server 7.0, SQL Server 7.0 users should refer to the ODBC documentation in SQL Server 7.0. The compatibility issues documented in this section only apply when running this driver with earlier versions of SQL Server (4.21a, 6.0, and 6.5). The SQL Server ODBC driver version 3.7 displays a new wizard when adding or configuring data sources in either the ODBC Administrator utility or when an application calls SQLConfigDataSource and asks the driver to prompt the user for information. Click the Help button in the wizard to access the wizard documentation. In the SQL Server ODBC driver version 2.65 that shipped with SQL Server 6.5, the SQL_COPT_SS_PERF_QUERY_INTERVAL worked in seconds instead of the milliseconds it was documented to use (see Knowledge Base article Q157753). In the SQL Server ODBC driver version 3.7, SQL_COPT_SS_PERF_QUERY_INTERVAL has been changed to work in milliseconds as documented. The following changes affect only applications written using the ODBC 3.X API. They do not affect applications written using the ODBC 2.X API. These changes should not impact the result set processing in most ODBC applications. In prior versions of the SQL Server ODBC driver, contiguous PRINT or RAISERROR statements in a batch or stored procedure return their messages together, in one result set. In the SQL Server ODBC driver version 3.7, the messages for each SQL statement are returned as separate result sets. You must call SQLMoreResults in between each message to be positioned on the message for the next SQL statement. The messages from a single SQL statement, such as a DBCC statement, are all returned in a single result set and there is no need to call SQLMoreResults between each message. In prior versions of the SQL Server ODBC driver, a run-time error or a RAISERROR with a severity of 11 or higher on the first statement in a batch or stored procedure always caused either SQLExecute, SQLExecDirect, or SQLParamData to return SQL_ERROR. In the SQL Server ODBC driver version 3.7, SQLExecute, SQLExecDirect, or SQLParamData return SQL_ERROR only if no other statements are executed after the first statement. If any other statements are executed after the first, even a simple RETURN statement with no return value, then SQLExecute or SQLExecDirect return SQL_SUCCESS_WITH_INFO. After processing the SQL_SUCCESS_WITH_INFO messages using SQLGetDiagRec, call SQLMoreResults to be positioned on the next result set. When prior versions of the driver encountered an error on the first statement of a batch or stored procedure, the statement handle was available for use with another SQL statement after SQLExecute or SQLExecDirect returned SQL_ERROR. When the version 3.7 driver returns SQL_SUCCESS_WITH_INFO, the statement is not free to process another SQL statement until SQLMoreResults returns SQL_NO_DATA or until all result sets following the RAISERROR have been closed. If no result set follows the error message, then SQLCloseCursor cannot be called. SQLFreeStmt(SQL_CLOSE) or SQLMoreResults must be called to free the statement handle to process another SQL statement: CREATE PROCEDURE TestPrc @Parm1 as IF (@Parm1 IS NULL) BEGIN RAISERROR ('Parm1 cannot be NULL', 11, 1) RETURN END SELECT * FROM sysusers WHERE suid = @Parm1 GO Execute the following: SQLExecDirect(hstmt, "{ call TestPrc (NULL) }", SQL_NTS); When using an older version of the SQL Server ODBC driver, or if the application uses the ODBC 2.X API, then SQLExecDirect returns SQL_ERROR. After SQLGetDiagRec returns SQL_NO_DATA or SQLError returns SQL_NO_DATA_FOUND, the statement handle is free to execute another SQL statement. When using the SQL Server ODBC driver version 3.7 from an application written to the ODBC 3.X API, then SQLExecDirect returns SQL_SUCCESS_WITH_INFO. After SQLGetDiagRec returns SQL_NO_DATA, the statement handle cannot be used to process another SQL statement until SQLMoreResults returns SQL_NO_DATA or SQLFreeStmt(SQL_CLOSE) is called. In prior versions of the SQL Server ODBC driver, SQLExecute, SQLExecDirect, or SQLParamData return SQL_SUCCESS when an application executes a searched UPDATE or DELETE statement that affects no rows. For this case, the version 3.7 driver still returns SQL_SUCCESS to applications written with the ODBC 2.X API, but it returns SQL_NO_DATA to applications written with the ODBC 3.X API. If either the ODBC 2.X application that receives SQL_SUCCESS or the ODBC 3.X application that receives SQL_NO_DATA then calls SQLRowCount, SQLRowCount returns a count of zero. ODBC 3.X more clearly defines the way results are returned than ODBC 2.X. Earlier versions of the SQL Server ODBC driver returned the values of output parameters and return codes when the ODBC 2.X functions SQLFetch or SQLExtendedFetch returned SQL_NO_DATA on the last result set returned by a stored procedure. The SQL Server ODBC driver version 3.7 driver retains this behavior when called by ODBC 2.X applications. When the version SQL Server ODBC driver version 3.7 is called by ODBC 3.X applications, however, the driver does not return output parameters or return codes until SQLMoreResults returns SQL_NO_DATA. ****************************************************************