?
Solved

Crystal Reports 10 Keeps Locking Out When Reporting From A Stored Procedure

Posted on 2005-04-30
10
Medium Priority
?
447 Views
Last Modified: 2011-10-03
I am having problems with one of the reports I have developed, it runs off a stored procedure, thats pretty similar to many others I have developed. When I refresh the two parameters @startdate and @enddate, both reporting off a smalldatetime field, the report runs for a while, freezes Crystal , then comes back with the following error. I get the following error about every 9 times out of 10 I run this report

Transaction deadlock victim, error 4001, failed to open a rowset.

I have broken the stored procedure down into parts but still it keeps freezing Crystal. Of course its one of the most important reports that is run and my bosses are screaming blue murder

I have looked at indexes and datatypes to make sure everyting is okay,which it is. On the odd occasion that the report runs it takes at least 5 minutes. The report returns about 30 - 40 records when run.

Does anyone know why it keeps on doing this.

Thanks

P
0
Comment
Question by:polynominal
  • 5
  • 3
  • 2
10 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 13900503
What is the stored procedure?

How are the parameters used?

How are you calling the report?

mlmcc
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 13901449
This isn't a Crystal Problem, per se.  It's a database problem.  There error message tells you what you need to know, you were the victim of a deadlock at the database level.  Either there's a problem with the stored procedure or with your database, in general.  The fact that your stored procedure takes 15 minutes to return data is an issue.  If it didn't take so long, you might not run into deadlocks (personally I don't like any report that takes over 5 minutes).  Does the procedure take that long when run from the db or just in Crystal Reports?

Here's a webpage that has some helpful hints for reducing deadlocks (assumed SQL Server, but the general tips apply):

http://www.sql-server-performance.com/deadlocks.asp

~Kurt
0
 

Author Comment

by:polynominal
ID: 13902035
Thanks for the answers the stored procedure is below, when I execute it on query analyser it takes lierally seconds to execute successfully. There are two parameters used @startdate and @enddate both smalldatetime data types and I calling it though an ODBC connection.

@startdate smalldatetime,
@enddate smalldatetime
AS
SELECT A.clientcontractno,A.Met, A.Less, B. MetABD, B.LessABD, C.Delivery, C.Complete, D.OriginalValue, D.ActualValue, D.Jobs, E.Orders, E.Value, F.Stoppages, F.svalue, G.Job, G.sjob
FROM
(SELECT     contract.clientcontractno, SUM(CASE WHEN datediff(dd, complete.engcomplete, job.rbd) >= 0 THEN 1 ELSE 0 END) AS Met, COUNT(job.jobid) AS Less
     FROM
           job
              INNER JOIN complete ON job.jobid = complete.jobid
              INNER JOIN vw_job_abd_nodcr  ON job.jobid = vw_job_abd_nodcr.jobid
              INNER JOIN contract  ON job.contractid = contract.contractid
              INNER JOIN vw_catanotesfilter  ON job.jobid = vw_catanotesfilter.jobid
WHERE     complete.engcomplete BETWEEN @startdate AND @enddate
GROUP BY contract.clientcontractno) A
LEFT OUTER join
(SELECT     contract.clientcontractno, SUM(CASE WHEN datediff(dd, complete.engcomplete, vw_job_abd.abd) >= 0 THEN 1 ELSE 0 END) AS MetABD, COUNT(job.jobid) AS LessABD
         FROM        
           job INNER JOIN complete ON job.jobid = complete.jobid INNER JOIN
                      contract ON job.contractid = contract.contractid INNER JOIN
                      vw_job_abd_withdcr ON job.jobid = vw_job_abd_withdcr.jobid INNER JOIN
                      vw_catanotesfilter ON job.jobid = vw_catanotesfilter.jobid INNER JOIN
                      vw_job_abd ON job.jobid = vw_job_abd.jobid
WHERE     complete.engcomplete BETWEEN @startdate AND @enddate
GROUP BY contract.clientcontractno) B
On A.clientcontractno = B.clientcontractno
Left outer join
(SELECT     contract.clientcontractno, SUM(CASE WHEN datediff(dd, complete.engcomplete, vw_job_abd.abd) >= 0 THEN 1 ELSE 0 END) AS Delivery, COUNT(job.jobid) AS Complete
FROM         job INNER JOIN
                      complete ON job.jobid = complete.jobid INNER JOIN
                      contract ON job.contractid = contract.contractid INNER JOIN
                      vw_catanotesfilter ON job.jobid = vw_catanotesfilter.jobid INNER JOIN
                      vw_job_abd ON job.jobid = vw_job_abd.jobid
WHERE     complete.engcomplete BETWEEN @startdate AND @enddate
GROUP BY contract.clientcontractno) C
On A.clientcontractno = C.clientcontractno
Left outer join
(SELECT     contract.clientcontractno, SUM(vw_job_originaljobvalue.value) as OriginalValue, SUM(job.autval) as Actualvalue, count(job.jobid) as Jobs
FROM         job INNER JOIN
                      contract ON job.contractid = contract.contractid INNER JOIN
                      originator ON job.originatorid = originator.originatorid INNER JOIN
                      vw_catanotesfilter ON job.jobid = vw_catanotesfilter.jobid INNER JOIN
                      complete ON job.jobid = complete.jobid LEFT OUTER JOIN
                      vw_job_originaljobvalue ON job.jobid = vw_job_originaljobvalue.jobid LEFT OUTER JOIN
                      vw_job_jobscancelled ON job.jobid = vw_job_jobscancelled.jobid
WHERE     complete.engcomplete BETWEEN @startdate AND @enddate and contract.clientcontractno in (65583611,65583612,67325921, 67325922)
GROUP BY contract.clientcontractno) D
On A.clientcontractno = D.clientcontractno
Left outer join
(SELECT     contract.clientcontractno, count(job.jobid) as Orders, SUM(job.autval) as Actualvalue, count(job.jobid) as Value
FROM         job INNER JOIN
                      contract ON job.contractid = contract.contractid INNER JOIN
                      vw_catanotesfilter ON job.jobid = vw_catanotesfilter.jobid INNER JOIN
                      complete ON job.jobid = complete.jobid
WHERE     complete.engcomplete BETWEEN @startdate AND @enddate and contract.clientcontractno in (65583611,65583612,67325921, 67325922)
GROUP BY contract.clientcontractno) E
On A.clientcontractno = E.clientcontractno
Left outer join
(SELECT     contract.clientcontractno, count(job.jobid) as Stoppages, SUM(job.autval) as svalue
FROM         complete INNER JOIN
                      job ON complete.jobid = job.jobid INNER JOIN
                      contract ON job.contractid = contract.contractid INNER JOIN
                      vw_catanotesfilter ON job.jobid = vw_catanotesfilter.jobid INNER JOIN
                      vw_job_N333 ON job.jobid = vw_job_N333.jobid LEFT OUTER JOIN
                      vw_job_N08 ON job.jobid = vw_job_N08.jobid
WHERE     complete.engcomplete BETWEEN @startdate AND @enddate
GROUP BY contract.clientcontractno) F
On A.clientcontractno = F.clientcontractno
Left outer join
(SELECT     contract.clientcontractno, count(job.jobid) as Job, SUM(job.autval) as sjob
FROM         complete
               INNER JOIN
                      job ON complete.jobid = job.jobid INNER JOIN
                      vw_catanotesfilter ON job.jobid = vw_catanotesfilter.jobid INNER JOIN
                      contract ON job.contractid = contract.contractid
WHERE     complete.engcomplete BETWEEN @startdate AND @enddate
GROUP BY contract.clientcontractno) G
On A.clientcontractno = G.clientcontractno
Left outer join
(SELECT     contract.clientcontractno, count(job.jobid) as Accrual, SUM(job.autval) as saccrual
FROM         job INNER JOIN
                      vw_catanotesfilter ON job.jobid = vw_catanotesfilter.jobid INNER JOIN
                      contract ON job.contractid = contract.contractid
WHERE     job.statusid = 5 and vw_catanotesfilter.notes is not null
GROUP BY contract.clientcontractno) H
On A.clientcontractno = H.clientcontractno

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
LVL 101

Expert Comment

by:mlmcc
ID: 13902514
What happens if you try to get the dataset through your application without Crystal involved?

mlmcc
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 13902517
Are you running from an application or from the Crystal designer?

If an application, please show the code.

mlmcc
0
 

Author Comment

by:polynominal
ID: 13903502
Im running through Crysal Designer
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 13905645
Some versions of Crystal have a problem with procedures with multiple select statements.

Can you rebuild the procedure as several views/queries and a stored procedure that calls them?

Query 1 - qryA
SELECT     contract.clientcontractno, SUM(CASE WHEN datediff(dd, complete.engcomplete, job.rbd) >= 0 THEN 1 ELSE 0 END) AS Met, COUNT(job.jobid) AS Less
     FROM
           job
              INNER JOIN complete ON job.jobid = complete.jobid
              INNER JOIN vw_job_abd_nodcr  ON job.jobid = vw_job_abd_nodcr.jobid
              INNER JOIN contract  ON job.contractid = contract.contractid
              INNER JOIN vw_catanotesfilter  ON job.jobid = vw_catanotesfilter.jobid
WHERE     complete.engcomplete BETWEEN @startdate AND @enddate
GROUP BY contract.clientcontractno)

Query2 - qryB
(SELECT     contract.clientcontractno, SUM(CASE WHEN datediff(dd, complete.engcomplete, vw_job_abd.abd) >= 0 THEN 1 ELSE 0 END) AS MetABD, COUNT(job.jobid) AS LessABD
         FROM        
           job INNER JOIN complete ON job.jobid = complete.jobid INNER JOIN
                      contract ON job.contractid = contract.contractid INNER JOIN
                      vw_job_abd_withdcr ON job.jobid = vw_job_abd_withdcr.jobid INNER JOIN
                      vw_catanotesfilter ON job.jobid = vw_catanotesfilter.jobid INNER JOIN
                      vw_job_abd ON job.jobid = vw_job_abd.jobid
WHERE     complete.engcomplete BETWEEN @startdate AND @enddate
GROUP BY contract.clientcontractno)

etc

New procedure


@startdate smalldatetime,
@enddate smalldatetime
AS
SELECT qryA.clientcontractno, qryA.Met, qryA.Less, qryB.MetABD, qryB.LessABD, qryC.Delivery, qryC.Complete, qryD.OriginalValue, qryD.ActualValue, qryD.Jobs, qryE.Orders, qryE.Value, qryF.Stoppages, qryF.svalue, qryG.Job, qryG.sjob
FROM
qryA  LEFT OUTER JOIN qryB  ON qyrA.clientcontractno = qryB.clientcontractno
LEFT OUTER JOIN qryC ON  qryA.clientcontractno = qryC.clientcontractno

etc

mlmcc
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 13909049
mlmcc's correct- when reporting from a stored procedure in Crystal Reports, the stored procedure must return a single recordset.  You either need to separate your queries into separate stored procedures and then report on them individually either as separate reports or a single report with multiple subreports or combine them into a single select statement by unioning them together or by populating a temp table and selecting from that table.

~Kurt
0
 

Author Comment

by:polynominal
ID: 13909172
Yet I have the same structure for about 40 other stored procedures and Crystal returns them in secondes, even with multiple select statements. On one of them I have 20 sdifferent selects returning over 200 reords which returns in 3 seconds
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 13913582
Crystal is sometimes a strange product.  I have seen similar problems.  Sometimes simply rebuilding the query solves the problem.  Other times restructuring it.


Glad i could help

mlmcc
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

850 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