PRB: ADO 2.0 and Later Return Connection Messages to Browser (197459)

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.

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 "<br>NativeError = " & errItem.NativeError
            Response.Write "<br>Description = " & errItem.Description
            Response.Write "<br>SQLState    = " & errItem.SQLState
            Response.Write "<br>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 "<br>NativeError = " & errItem.NativeError
            Response.Write "<br>Description = " & errItem.Description
            Response.Write "<br>SQLState    = " & errItem.SQLState
            Response.Write "<br>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=<username>;PWD=<strong password>;DATABASE=Pubs;"

Use a connection string that specifically names a provider:

      Conn.Open "Provider=Sqloledb;Data Source=YourServer;" & _
      "Initial Catalog=Pubs;User Id=<username>;Password=<strong password>;"

MORE INFORMATION

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.

   <HTML>
   <HEAD>
   <TITLE>Connection Message Test</TITLE>
   </HEAD>
   <BODY BGCOLOR=#FFFFFF>
   <H3>Connection Message Test</H3>
   <BR><BR>
   <%
   Set Conn = Server.CreateObject("ADODB.Connection")
   Conn.Open "dsn=pubs;uid=<username>;pwd=<strong password>;"
   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>
   <TABLE BORDER=1>
   <TR>
   <% For i = 0 to RS.Fields.Count - 1 %>
      <TD><B><% = RS(i).Name %></B></TD>
   <% Next %>
   </TR>
   <%Do While Not RS.EOF %>
      <TR>
      <% For i = 0 to RS.Fields.Count - 1 %>
         <TD VALIGN=TOP><% = RS(i) %></TD>
      <% Next %>
      </TR>
      <%
      RS.MoveNext
   Loop
   RS.Close
   Conn.Close
   %>
   </TABLE>
   <BR>
   </BODY>
   </HTML>