Stored Procedure doesn''t exist-->error
-
Hi, Gangs..
I need to you help..
I creat report and want to use Stored Procedures in Dataset.
Therefore I create SP in SQL 2005(SP1) and SP is there and can run from SQL.
However when I put that SP name in Dataset using Command type with StoredProcedure and run that SP, I've got this error message.
*********************************************************
An Error occurred while retrieving the parameters in the query.
The Stored procedure 'dbo.usp_AT @ Prog, @Chapt' doesn't exist.
*********************************************************
I dont have a problem to run from SQL but not RS..
Any idea
-
Looks to me like the client thinks that the procedure is name
"dbo.usp_AT @ Prog, @Chapt" instead of named dbo.usp_AT with 2 parameters.
Also is this a typo (@ Prog) >> The space between @ and prog?
-
ohhh..yes space is typo
-
can we see the code that leads to this result?
-
Create
Proc USP_AT_Participation_Report_Pull @prog Varchar(50),@Chapt Varchar(10),@State Varchar(5),@County Varchar(25),@Zip Varchar(15)
AS
/*
DECLARE @prog Varchar(50),@Chapt Varchar(10),@State Varchar(5),@County Varchar(25),@Zip Varchar(15)
SET @prog='AT_XMAS_PROGRAM'
SET @Chapt='331'
SET @State='VA'
SET @County='Centreville'
SET @Zip='20120'
*/
DECLARE
@SQL varchar(2000),@SQL1 varchar(2000),@SQL2 varchar(2000),@SQL3 varchar(2000),@SQL4 varchar(2000)
Set
@SQL='select n.Company,na.* from name n join name_address na on n.mail_address_num=na.address_num join '
Set
@SQL1=' p on n.id = p.org_id where n.member_type=''churc'' and n.chapter in ('''
Set
@SQL2=''') and na.state_province in ('''
Set
@SQL3=''') and na.County in ('''
Set
@SQL4=''') and na.zip in ('''
EXEC
(@SQL+@prog+@SQL1+@Chapt+@State+@County+@Zip+''')')
-
You need to enter just the stored procedure name without the parameters, they're not needed as Visual Studio works out the parameters for you.
-
There is another reason you may receive this error. I found that Reporting Services had insufficient permissions to run the Stored Procedure. By modifying the SP's properties and giving execute permissions to the account under which RS calls my SPs, I solved my problem.
HIH
🙂
-
Yes, first check if your user has the execute rights on the stored procedure. Second you just need to give the name of your stored procedure and define your paramters under the Parameters tab.
Prasad Bhogadi
www.inforaise.com -
I have the same issue except I'm using SQL Server 2008 Development Studio. My data source is also SQL Sever 2008. I get this error when I try to create a dataset from the stored procedure. I did not include the parameters in the stored procedure name. I checked permissions on the stored procedure and verified that the data source user has execute permissions. I signed into Management Studio under the same user and was able to execute the the stored procedure with no problems. I'm not sure what my problem could be. I am executing other other stored procedures with the same data source in the same report with no problems as well.
-
I solved my own problem. For those that are curious, I changed my data source to a test database and forgot that I had changed it... Oops. All is well.
-
Thank you. I had the same problem. Mine is now fixed....:-P
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply