Solved

Crystal Reports stored procedure error

Posted on 2014-03-20
8
1,661 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

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 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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
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 the fundamental information of how to create a table.

726 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