Print Page | Close Window

Can't See My Stored Proc in Crystal

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=10019
Printed Date: 02 May 2024 at 1:01am


Topic: Can't See My Stored Proc in Crystal
Posted By: Frank in VA
Subject: Can't See My Stored Proc in Crystal
Date Posted: 13 May 2010 at 9:57am
Hi All,
 
I am using CR XI.  I have created a stored procedure in SQL2005.  When I try to use it in a report, I do not see it as most of the stored procedures are not listed on the left side of the Database Expert screen.  I see all the SQL Views from that particular database but not all the stored procs, including my new one.  Has anyone had this happen to them before? 
 
Thanks!


-------------
Frank


Replies:
Posted By: DBlank
Date Posted: 13 May 2010 at 10:14am
Sometimes I have to shut Crystal down and restart it to get it to 'fully refresh' and show me new views or sps that I had created or altered while I had crystal open.
Other times I find I am using an incorrect ODBC connection and pointing to my test set when my changes were in my production set or vice versa.
Other than that I can always find my SPs, Tables and Views.

Posted By: kevlray
Date Posted: 13 May 2010 at 10:39am
In the Database Expert.  Right-click on the connection, click on options and make sure that there is a checkbox for Stored Procedures in the Data Explorer.

Posted By: Frank in VA
Date Posted: 14 May 2010 at 5:14am
After checking the SQL connection, a co-worker questioned if the user name had the proper permissions.  So we found that we needed to give the user name we were using persmissons in the SP by right clicking on the SP and selecting "Properties".  Once we added the name it showed up in the Crystal Database Expert.
 
As always, thanks for your help.  Frank


-------------
Frank

Posted By: Frank in VA
Date Posted: 14 May 2010 at 9:27am
I am having another issue, my parameters are not being passed back to the stored procedure. 
 
I set up the parameters in the SP as @Fromdate and @ToDate.  Then I initally removed the original parameters from the report expecting the SP to cause the prompt for the dates.  But the report didn't ask for them.  So I put them back on the report.  The report now prompts me for the dates, but the SP is not seeing them. 
 
I am getting the following message: "Failed to retrieve data from the database"  "Procedure or Function expects parameter mailto:@FromDate - '@FromDate' , which was not supplied".  How do I get the report to pass the parameters to the SP?


-------------
Frank

Posted By: Frank in VA
Date Posted: 20 May 2010 at 5:17am
Yesterday I was able to get my parameters to work.  I just wanted to note it here in case someone else is searching for a similar issue.
 
When I create a new report and use the stored procedure, the parameters work fine and the report prompts me for them.  But when I tried replacing the current SQL View with the SP in an existing report, the parameters did not work.
 
Maybe something in the report is not being refreshed and it doesn't know to prompt for the parameters.
 
Thanks!


-------------
Frank


Print Page | Close Window