Solved

Crystal Reports stored procedure error

Posted on 2014-03-20
8
1,609 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:imstac73
ID: 39942953
money
0
 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL query and VBA 5 45
SQL Query 2 31
What is the best way to use power bi and ssrs 3 19
SQL Recursion schedule 13 13
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

789 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