FIX: OpenSchema Method Generates Error 3251

ID: Q195487

The information in this article applies to:

ActiveX Data Objects (ADO), versions 1.5, 2.0, 2.01

SYMPTOMS

Using server-side cursors to open a recordset with OpenSchema, such as "Set rs = Connection.OpenSchema" generates error 3251:

Application-defined or object-defined error.

CAUSE

Using the ActiveX Data Objects (ADO) OpenSchema method uses the IDBSchemaRowset interface to provide advanced schema information. This interface uses the GetRowset method to return a schema rowset. Currently, properties are not passed to the IDBSchemaRowset::GetRowset() when using a server-side cursor. This includes the IConnectionPointContainer property that is necessary for supporting notification (events). This results in ADO not hooking in the notification.

RESOLUTION

Open the connection on the client-side. For example:


   Set Connection.CursorLocation = adUseClient

This way the client engine passes the IConnectionPointContainer property, and ADO can hook in the notification.

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.

This problem has been fixed in MDAC 2.1 sp2.

MORE INFORMATION

Steps to Reproduce Behavior

Use the following steps to reproduce this behavior using Visual Basic code:

Start a new Visual Basic project. Form1 is created by default.

Add a command button (Command1) to Form1. Change the name property to cmdOpenSchema.

Add the Microsoft ActiveX Data Objects 2.0 Library as a Reference.

Paste the following code in the General Declaration of Form1:


      Option Explicit
         Dim WithEvents rs_Events As ADODB.Recordset

      Private Sub cmdOpenSchema_Click()

         Dim cn As New ADODB.Connection

         On Error GoTo ErrHandler

         Set cn = New ADODB.Connection

        ' Uncomment the following line to workaround the problem:
        ' cn.CursorLocation = adUseClient

        ' Open connection
        cn.Open "Provider=MSDASQL;DSN=pubs;UID=sa;PWD=;Database=pubs;"

        ' Error 3251 occurs at the following line.
        Set rs_Events = cn.OpenSchema(adSchemaTables)

        ' As a test: Print all table names and types in your database.
        Do Until rs_Events.EOF
           Debug.Print "Table name: " & rs_Events!TABLE_NAME & _
              vbCr & "Table type: " & rs_Events!TABLE_TYPE & vbCr
          rs_Events.MoveNext
       Loop

      ' Clean up objects.
         rs_Events.Close
         cn.Close
         Set rs_Events = Nothing
         Set cn = Nothing

      Exit Sub

      ErrHandler:

          MsgBox Err.Number &amp; ": " &amp; Err.Description

      End Sub

Run the program. Notice that when the Cursorlocation property is set to adUseClient that all the information prints properly to the Immediate Window.

Additional query words:

Keywords : kbADO200bug kbADO201bug kbDatabase kbSQLServ kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2fix kbMDAC210SP2
Version : WINDOWS:1.5,2.0,2.01
Platform : WINDOWS
Issue type : kbbug