Problem with accessing stored procedure in the report

Hi,

I am new to Sql Server 2005 Reporitng Services. I created a report in BI and used stored procedure as a dataset. When I run the report in preview mode it works fine and when I run it in report server/report manager, I am getting the following error:

An error has occurred during report processing. (rsProcessingAborted)

Query execution failed for data set 'dsetBranch'. (rsErrorExecutingCommand)

Could not find stored procedure 'stpBranch'.

But I have this procedure in the db and it runs fine in the query analyzer and the query builder window in report project. When I refresh the page in Report manager, I am getting this error.

Input string was not in a correct format.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.FormatException: Input string was not in a correct format.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[FormatException: Input string was not in a correct format.] System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) +2753715 System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) +102 Microsoft.Reporting.WebForms.ReportAreaPageOperation.PerformOperation(NameValueCollection urlQuery, HttpResponse response) +149 Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +75 System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +154 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64

I have changed the dataset from procedure to a sql string and the report is working fine everywhere. But I have a business requirement that I need to use a stored procedure.

I am not sure why I am getting this error and I greatly appreciate any help.

Thanks

ngk,

I have seen this before. Are you using any schema namespaces in your stored procedure name.

Such as HumanResources.GetAllEmployees instead of the old default of dbo.GetAllEmployees.

If so are you also running your stored procedures in Query Analyser as the "SAME" user that your Report Datasource uses.
Pay close attention to these details...

I have seen where 1 sql user has the default schema set to HumanResources and the stored procedure call is made such as exec GetAllEmployees instead of
exec HumanResources.GetAllEmployees.

In this situation any user that makes the first call (exec GetAllEmployees ) and has the default schema of HumanResources will succeed.
Any user that does not have this default will return an error because it is looking for dbo.GetAllEmployees and this may not exist.

Hope this helps.. if not please provide more details on what users you are using and the exact call you are making for the stored procedure.

|||

Hi Bret,

Thanks for the info. I am not using any schema namespaces in my stored procedures. I actually got this error when I tried to connect to a remote sql server. Now I have installed developer editon on my local machine and I have reporting services also on my local machine. I have been able to connect to the strored procedures and deploy the reports to the local report server and view the reports without any problems. I am not sure whether I may have to face the same issue when the reports are deployed to the remote SQL DB and Reporting Services production servers.

Thanks