Microsoft KB Archive/818100

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

Article ID: 818100

Article Last Modified on 8/6/2004

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition
 * Microsoft Access 2000 Standard Edition

-



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

 

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 </li></ul>

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)

<div class="moreinformation_section">

Steps to Reproduce the Problem
<ol> Run Access.</li> Open the sample project NorthwindCS.adp.</li> 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: <ol style="list-style-type: lower-alpha;"> Move to the Database window. Then, on the File menu, click Connection.</li> 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.</li></ol> </li> In NorthwindCS.adp, create a new table t1. <ol style="list-style-type: lower-alpha;"> Add a field c1 to table t1.</li> Define c1 as the primary key. Make sure that c1 is not null.</li>  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. </li></ol> </li> 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. <ol style="list-style-type: lower-alpha;"> In the Database window, click Queries in the Objects section.</li> Click New.</li> In the New Query dialog box, click Create Text Stored Procedure and then click OK.</li>  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 </li> On the File menu, click Save.</li> In the Save As dialog box, type test1 and then click OK.</li> <li>On the File menu, click Close.</li></ol> </li> <li>In the Objects section in the Database window, click Queries.</li> <li>In the right pane, double-click the stored procedure test1.</li></ol>

Keywords: kberrmsg kbbug kbtsql kbdatabase KB818100

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.