Solved

Crystal Reports stored procedure error

Posted on 2014-03-20
8
1,586 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
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
 

Author Comment

by:imstac73
ID: 39942953
money
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 18

Accepted Solution

by:
vasto earned 500 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 100

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now