Help and Support
 

powered byLive Search

PRB: ADO 2.0 and Later Return Connection Messages to Browser

View products that this article applies to.
Article ID:197459
Last Review:October 31, 2003
Revision:2.0
This article was previously published under Q197459
On This Page

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.

Back to the top

CAUSE

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

Back to the top

RESOLUTION

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

Back to the top

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.

Back to the top

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

Back to the top

STATUS

This behavior is by design.

Back to the top

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>

Back to the top

REFERENCES

For additional information, please see the following articles in the Microsoft Knowledge Base:
137636 (http://support.microsoft.com/kb/137636/EN-US/) INF: Relationship of the ODBC Driver to INSTCAT.SQL
193339 (http://support.microsoft.com/kb/193339/EN-US/) PRB: Unexpected Errors Using OLE DB Provider for SQL Server

Back to the top

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

Back to the top

Keywords: 
kbcode kbdatabase kbdriver kbjet kboracle kbprb kbsqlprog KB197459

Back to the top

Article Translations

 

Other Support Options

  • Need More Help?
    Contact a Support professional by Email, Online or Phone.
  • Customer Service
    For non-technical assistance with product purchases, subscriptions, online services, events, training courses, corporate sales, piracy issues, and more.
  • Newsgroups
    Pose a question to other users. Discussion groups and Forums about specific Microsoft products, technologies, and services.