Microsoft KB Archive/197459

= PRB: ADO 2.0 and Later Return Connection Messages to Browser =

Article ID: 197459

Article Last Modified on 10/31/2003

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7
 * Microsoft OLE DB Provider for ODBC 2.0

-



This article was previously published under Q197459



SYMPTOMS
ODBC drivers often return non-fatal, informational connection messages to the client as part of the connection process. The return code out of SQLConnect or SQLDriverConnect is 1 (SQL_SUCCESS_WITH_INFO), indicating that the connection was successful, but additional information has been returned.

Neither ActiveX Data Objects (ADO) 1.0 nor ADO 1.5 returned these messages to the browser, but ADO 2.0 and later now return these connection messages when SQLConnect or SQLDriverConnect returns SQL_SUCCESS_WITH_INFO. These error messages occur when using the OLE DB to ODBC provider (MSDASQL), which is the default provider, but does not occur if you are using other Microsoft providers.

If connection pooling is enabled, these messages may not reappear during a page refresh until the length of time specified by the CPTimeout value passes and the connection is removed from the connection pool. If a page is subsequently refreshed, the connection messages may reappear since the connection has to be re-established.

When using the SQL Server ODBC driver, the following messages may appear:

[Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'Pubs'. [Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to 'us_english'.

If the Instcat.sql file has not been run on the SQL Server to which you are connecting, a message similar to the following may also appear:

[Microsoft][ODBC SQL Server Driver][SQL Server]The ODBC catalog stored procedures installed on server ServerName are version 6.50.xxx; version 07.00.0324 or later is required to ensure proper operation.[ASCII 160] Please contact your system administrator. (0)

When using the Microsoft Access ODBC driver, the following messages may appear:

[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed [Microsoft][ODBC Microsoft Access 97 Driver]Driver not capable.

When using the Microsoft ODBC for Oracle driver, just the following error may appear:

[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed.



CAUSE
These messages are considered to be normal behavior of the driver, and are purely informational.



RESOLUTION
Here are two options that you can use to avoid this behavior:

Option One
Check the Number property of each error in the Conn.Errors collection. This number is returned to the Errors collection from the ODBC driver. If this Number property is zero (0), then the message is purely an informational message. For example: If Conn.Errors.Count <> 0 Then For each errItem in Conn.Errors If errItem.Number <> 0 Then Response.Write " NativeError = " & errItem.NativeError Response.Write " Description = " & errItem.Description Response.Write " SQLState   = " & errItem.SQLState Response.Write " Source     = " & errItem.Source End If     Next End If Alternatively, trap for the NativeError error code that returns from the ODBC driver to the Errors collection. For example: If Conn.Errors.Count <> 0 Then For each errItem in Conn.Errors If errItem.NativeError <> 5701 Then Response.Write " NativeError = " & errItem.NativeError Response.Write " Description = " & errItem.Description Response.Write " SQLState   = " & errItem.SQLState Response.Write " Source     = " & errItem.Source End If     Next End If For SQL Server, the "Changed database context" NativeError code is 5701, and the "Changed language setting" NativeError code is 5703. These messages are normal and will always be returned through DBLib or the ODBC driver.

To avoid the "ODBC catalog stored procedures" message, open an instance of ISQL and connect to your server. Next, run the Instcat.sql file that has been provided with the Microsoft Data Access Components (MDAC) download. Please see articles listed in the REFERENCES section for more information on this message.

The messages returned from the Microsoft Access ODBC driver and the Microsoft ODBC for Oracle driver usually refer to the SQL_ATTR_LOGIN_TIMEOUT setting, which is not currently supported by either driver but is non-fatal.

The NativeError code for "Driver's SQLSetConnectAttr failed" is zero (0) and is returned by the ODBC Driver Manager. The NativeError code for "Driver not capable" is 84.

Option Two
Use a provider other than MSDASQL to make the connection. For example, instead of using a connection string that defaults to MSDASQL as follows: Conn.Open "DSN=Pubs;UID= ;PWD= ;DATABASE=Pubs;" Use a connection string that specifically names a provider: Conn.Open "Provider=Sqloledb;Data Source=YourServer;" & _ "Initial Catalog=Pubs;User Id= ;Password= ;"



STATUS
This behavior is by design.



Steps to Reproduce the Behavior
These steps assume that ADO 2.0 is correctly installed and registered.

Copy and paste the following code into an Active Server Pages (ASP) page, save it to a valid Web application folder managed by Internet Information Server (IIS), then run it. This code sample assumes that a System ODBC data source name (DSN) has already been created with the name "Pubs".

Note The Username and Password may have to be changed.   Connection Message Test   Connection Message Test  <%  Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open "dsn=pubs;uid= ;pwd= ;" if Conn.Errors.Count <> 0 then for i = 0 to Conn.errors.count -1 Response.Write (Conn.Errors.Item(i).NativeError ; " - " & _        Conn.Errors.Item(i).Description & "<BR>") next end if  Set RS = Server.CreateObject("ADODB.Recordset") RS.Open "select au_id, au_lname from authors", Conn %>  <P> <BR> </BODY> </HTML>

<div class="references_section">