Solved

Crystal Reports stored procedure error

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

776 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