You receive an error message when you try to run a stored procedure that has the same name as another stored procedure, but that has a different owner, in SQL Server 2000 Reporting Services (898086)


The information in this article applies to:

  • Microsoft SQL Server 2000 Reporting Services

SYMPTOMS

In Microsoft SQL Server 2000 Reporting Services, when you try to run a stored procedure by using the Data view in Report Designer, you receive an error message that is similar to the following:
An error occurred while executing the query.
Procedure or function yourStoredProcedure has too many arguments specified.
Note The placeholder yourStoredProcedure is a placeholder for the stored procedure that you are trying to run.

CAUSE

This problem occurs when two stored procedures have the same name but have different owners. The SqlCommandBuilder.DeriveParameters method in SqlClient returns the parameters of every stored procedure that has the name of the stored procedure that you are trying to run.

Note This problem only occurs when you call stored procedures from the System.Data.SqlClient namespace. This problem does not occur in report projects that are based on the Microsoft OLE DB provider.

WORKAROUND

To work around this problem, in the Data view in Report Designer, change the Command type to Text. Then, run the following query:
EXEC <Owner>.<yourStoredProcedure> @parameter1, @parameter2, .
Note The Owner placeholder is a placeholder for the owner of the stored procedure.

The following is an example of the command text that is used to run the CustomerList stored procedure that is owned by user1.
EXEC user1.CustomerList @region
The following is an example of a stored procedure.
CREATE  PROCEDURE CustomerList(@region varchar(15)='BC') as
SELECT * FROM customers WHERE region = @region

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section.

MORE INFORMATION

Steps to reproduce the behavior

  1. In Microsoft SQL Server 2000, create two different users. Grant each user sufficient permissions to create stored procedures in the Northwind database.
  2. Log on to SQL Query Analyzer as the first user that you created in step 1. Then, run the following Transact-SQL script:
    USE Northwind
GO

CREATE  PROCEDURE CustomerList(@region varchar(15)='BC') as
SELECT * FROM customers WHERE region = @region
  3. Log on to SQL Query Analyzer as the second user that you created in step 1. Then, run the following Transact-SQL script:
    USE Northwind
GO

CREATE  PROCEDURE CustomerList(@region varchar(15)='BC') as
SELECT * FROM customers WHERE region = @region
  4. Start Microsoft Visual Studio .NET.
  5. Create a new report project.
  6. Add a new data source to the report project.
  7. Log on to the Northwind database as the first user that you created in step 1.
  8. Add a new report to the report project by using the Report Wizard.
  9. Click the Data tab, and then click New Dataset in the Dataset list. Then, click to select StoredProcedure for the Command text property.
  10. In the Query string dialog box, type the following command and then click OK:

    user1.CustomerList @region

  11. On the Dataset toolbar, click Run.
  12. After you run the command in step 9, a Define Query Parameters dialog box will appear. You will be prompted to click OK in order to approve two @region parameters.
  13. After you click OK, you will receive the error message that is mentioned in the "Symptoms" section.
Modification Type:MinorLast Reviewed:6/9/2005
Keywords:kbReportWriter kbReport kbtshoot kbnofix kbbug KB898086 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.