?
Solved

Crystal Reports stored procedure error

Posted on 2014-03-20
8
Medium Priority
?
1,702 Views
Last Modified: 2014-03-20
Hi, I am trying to use a SQL stored procedure for my Crystal Report.  When I select the stored procedure it requires me to enter a value for my input parameter. When I input a value (I've tried 80000, 80,000, 80) and click OK, I get the following error: Not supported Details: Cannot obtain error message from server. I am using Crystal Reports 9

My stored procedure is very simple so I'm not sure what is causing the problem.  It works fine when executed via SQL Server Management Studio.  

Here is my procedure:
-- Add parameter for top paid amount
      @toppaidamt money
      
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

--====================================
--Update Claims
--====================================
      Update dbo.claims
            Set ReservedBy = 'john.doe', Reservedon = getdate()
            Where (Tracking = 0 or Tracking is null) and
            Reservedby is null and Completeuser is null
            and tmhppaid > @toppaidamt
END

I've tried removing the input parameter and setting update values to static values as well and I still get the same error.

Any suggestions on how to troubleshoot/fix this issue?
0
Comment
Question by:imstac73
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39942700
Run SQL profiler when you run the report to see if it is even getting to SQL first of all. Sounds to me like it isn't. It is possible the report is corrupt in some way. Can you quickly put another together that executes the same stored procedure just to see if it is the report itself?

If profiler picks nothing up the problem is Crystal side.

If it is Crystal side and you can create a different report that calls the same procedure then it is your report that is broken in some way.

If you can't create a new report to call that procedure then it is likely the connection between Crystal and the SQL database is not working.
0
 

Author Comment

by:imstac73
ID: 39942908
I'm getting this error just trying to create the new report; I cannot even get past trying to add the stored procedure to the new report.  I have no problems choosing other stored procedures and/or tables with the same connection.

I created a test stored procedure that did a select statement with the same input parameter and where clause and it worked fine.  Seems like Crystal does not like something with my update statement.
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39942921
What data type is tmhppaid ?
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:imstac73
ID: 39942953
money
0
 
LVL 18

Accepted Solution

by:
vasto earned 2000 total points
ID: 39942975
You need to return something to the report in order it to work at all. Correct me if I am wrong , but this sp is just updating the data. There is nothing to return back.
Add a select statement to check if the error will disappear. Keep in mind that it is not a good practice to update data in a report. So even if you add a select statement and it works try to find another way to do the update.
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39942987
Comment out the
set nocount on
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 39943020
vasto is correct.  The stored procedure you are showing is an UPDATE procedure and doesn't return a record set so Crystal cannot use it.

Crystal needs the stored procedure to include a SELECT statement that it can use to get data from the database.

mlmcc
0
 

Author Comment

by:imstac73
ID: 39943151
That was it!  Thanks!
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question