Microsoft KB Archive/898086

= 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 =

Article ID: 898086

Article Last Modified on 6/9/2005

-

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  has too many arguments specified.

Note The placeholder  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 . @parameter1, @parameter2, … Note The  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 &quot;Applies to&quot; section.



Steps to reproduce the behavior
 In Microsoft SQL Server 2000, create two different users. Grant each user sufficient permissions to create stored procedures in the Northwind database.  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   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  Start Microsoft Visual Studio .NET.</li> Create a new report project.</li> Add a new data source to the report project.</li> Log on to the Northwind database as the first user that you created in step 1.</li> Add a new report to the report project by using the Report Wizard.</li> Click the Data tab, and then click New Dataset in the Dataset list. Then, click to select StoredProcedure for the Command text property.</li> In the Query string dialog box, type the following command and then click OK:

user1.CustomerList @region

</li> On the Dataset toolbar, click Run.</li> 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.</li> After you click OK, you will receive the error message that is mentioned in the &quot;Symptoms&quot; section.</li></ol>

Keywords: kbtshoot kbbug kbnofix kbreportwriter kbreport KB898086

-

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

© Microsoft Corporation. All rights reserved.