Microsoft KB Archive/225948

= ACC2000: Stored Procedure with Output Parameter Doesn't Return Data =

Article ID: 225948

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q225948





Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access project (.adp).



SYMPTOMS
When you try to run a stored procedure that uses an output parameter to return data from within a Microsoft Access project (.adp), you are prompted for any input parameter(s) as well as the ouput parameter. After you enter the parameter(s), you receive the following message:

The Stored Procedure executed successfully but did not return records.



RESOLUTION
To run a stored procedure that contains output parameters from within an Access project, create a second stored procedure that nests the original procedure. Then you can run the new stored procedure instead. The following example demonstrates how to implement this technique:  First, complete the steps in "Steps to Reproduce Problem" section of the "More Information" section later in this article.  Create a new stored procedure, and enter the following text: CREATE PROCEDURE OutputSqrrt (  /* Input Parameter used to prompt user for value */

@EnterValue int ) AS  /* variable used to return the result */    Declare @OutputResult int

/* Call First Stored Procedure, storing the result in @OutputResult */ Execute Sqroot @EnterValue, @OutputResult OUTPUT SELECT @OutputResult as long 



Steps to Reproduce Behavior
 Open the sample Access project, NorthwindCS.adp. In the Database window, click Stored Procedures under Objects, and then click New.</li>  In a new stored procedure window, enter the following text: CREATE PROCEDURE Sqroot (     @EnterValue int,      @Result int OUTPUT   ) AS SELECT @Result = SQRT(@EnterValue) </li> Close and save the stored procedure as "Sqroot" (without quotation marks).</li></ol>

When you run the stored procedure that you've just created, you are prompted for both the input parameter and the output parameter. When you click OK on the prompt for the output parameter, Microsoft Access returns the following message:

The Stored Procedure executed successfully but did not return records.

Additional query words: pra prb doesn t does not

Keywords: kbprb kbclientserver KB225948

-

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

© Microsoft Corporation. All rights reserved.