BUG: A stored procedure that fetches a cursor may fail to run in Microsoft Access project (818100)


The information in this article applies to:

  • Microsoft Access 2002
  • Microsoft Access 2000
Advanced: Requires expert coding, interoperability, and multiuser skills. This article applies only to a Microsoft Access project (.adp).

SYMPTOMS

When you run a stored procedure in Access project and that stored procedure creates and fetches a cursor, the stored procedure may fail. You may receive the following error messages:
  • The data provider or other service returned an E_FAIL status.
  • Invalid object name '<table name>'.
  • Unspecified error.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

WORKAROUND

To work around the problem, use one of the following methods:

Method 1

  • You can change the stored procedure to assign the result of the FETCH statement to a variable. For example, the original stored procedure may look similar to this:
    CREATE PROCEDURE test1
AS
DECLARE cc1 CURSOR FOR SELECT c1 FROM t1
OPEN cc1
FETCH cc1
CLOSE cc1
DEALLOCATE cc1
  • You can use the following code sample to change the stored procedure to fetch the result of the FETCH statement to a variable. 
    ALTER PROCEDURE test1
AS
DECLARE @ID int
DECLARE cc1 CURSOR FOR SELECT c1 FROM t1
OPEN cc1
FETCH cc1 INTO @ID
CLOSE cc1
DEALLOCATE cc1

Method 2

You can use any of the following ways to run the stored procedure:
  • SQL Query Analyzer
  • SQL Pass-Through query
  • VBA environment by means of ActiveX Data Objects (ADO)

MORE INFORMATION

Steps to Reproduce the Problem

  1. Run Access.
  2. Open the sample project NorthwindCS.adp.
  3. Note the database name that NorthwindCS.adp is connected to. To find the server and the database that NorthwindCS.adp is connected to, follow these steps:
    1. Move to the Database window. Then, on the File menu, click Connection.
    2. In the Data Link Properties dialog box, the name of the server and the name of the database appear. You can see the NorthwindCS.adp project that the server and the database are connected to.

      Note If the project is not connected to the Microsoft SQL Server database, then you must provide the server name, the username, and the password of the SQL Server that you want to connect to.
  4. In NorthwindCS.adp, create a new table t1.
    1. Add a field c1 to table t1.
    2. Define c1 as the primary key. Make sure that c1 is not null.
    3. You can perform the task if you run the TRANSACT-SQL statement in SQL Query Analyzer:
      USE <databaseName>
GO
CREATE TABLE t1
(
	c1 INT NOT NULL PRIMARY KEY
)
      Note The <databaseName> is the database that NorthwindCS.adp is connected to.
  5. Create the stored procedure test1 in the Access project. To do this, follow these steps:

    Note You can also create the procedure by using SQL Query Analyzer.
    1. In the Database window, click Queries in the Objects section.
    2. Click New.
    3. In the New Query dialog box, click Create Text Stored Procedure and then click OK.
    4. Replace the code in the Stored Procedure2 : Stored Procedure dialog box with the following code:
      CREATE PROCEDURE test1
AS
DECLARE cc1 CURSOR FOR SELECT c1 FROM t1
OPEN cc1
FETCH cc1
CLOSE cc1
DEALLOCATE cc1
    5. On the File menu, click Save.
    6. In the Save As dialog box, type test1 and then click OK.
    7. On the File menu, click Close.
  6. In the Objects section in the Database window, click Queries.
  7. In the right pane, double-click the stored procedure test1.
Modification Type:MinorLast Reviewed:8/6/2004
Keywords:kbBug kbDatabase kbTSQL kberrmsg KB818100 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.