PRB: ADO 2.0 and Later Return Connection Messages to Browser

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>;"
				

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