?
Solved

Get error message when trying to save data in application while running a report in Crystal Reports

Posted on 2009-04-29
7
Medium Priority
?
561 Views
Last Modified: 2013-11-23
Hi all!

When I'm running a complex report in Crystal Reports, I'm unable to save data in the SQL Server 2005 database through an accounting application.  The user will get the following message:

Error 1222 [Microsoft] [ODBC SQL Server Driver] Lock Request Time Out Period Exceeded

The data in the report is good, though it is quite long to generate (3 minutes). I'm running several reports that do not cause any problem, but they are usually simpler and don't take as long to calculate.  

I tried to figure out why would SQL Server 2005 prevent writing to the database while I'm running this report, without success.  The total size of the 13 tables I'm using for the report is around 3 GB.  Therefore Crystal should be able to put all the data it might need in a temporary file that it will use for selecting and sorting and let the database alone.  Instead, it seems like it still needs to prohibit writing on it to avoid a conflict that I can't explain.

Have you got any idea if a solution exists for this problem?

Thank you!
0
Comment
Question by:AlainSt-Pierre
  • 3
  • 3
7 Comments
 
LVL 12

Accepted Solution

by:
Chris M earned 750 total points
ID: 24260643
It looks like this query suffers due to database locks: check message "Lock Request Time Out Period Exceeded".
As such, it seems there are other processes on the database that are accessing the same data (probably modifying it) and your report is getting locked out in the long run.
Use SQL profiler and see what's happening back end.
If thisis the nature of the resources, then it's best to have a seperate database for reporting purposes (you may setup replication in between to keep modifying the data).
The other option for you is to index your database based on the more intense sql queries, and also trying to use table locking hints like "nolock" at the end in the select statement, which will make the query not wait for a lock, making the reports run faster, however be careful because this may result into picking data which is being modified (a condition called "dirty read").
Now that all those options are laid for you to choose, the choice is yours!
All the best,
 
regards,
Chris Musasizi
0
 

Author Comment

by:AlainSt-Pierre
ID: 24263043
Hi Chris,

It appears normal to me that a running report require queries on data (columns) that can be modified by another user.  In an accounting system, that can be just the case with just any report.  For instance, a user may want to save a record in the table "Client" while I'm running a report on clients and there won't be any problem.  My question is why only few reports necessitate a lock in the database write process.  What are the specific cases when such a thing happens.  And what can be done other than to duplicate the database to avoid that.

Thanks

See attached SQL Profiler screen shot.
PS-SQL-Server-Profiler.bmp
0
 
LVL 35

Assisted Solution

by:James0628
James0628 earned 750 total points
ID: 24276111
Just to clarify something, is it the report that gets the lock message, or is it the process that's trying to update the db?

 James
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:AlainSt-Pierre
ID: 24278378
Hi James,

It is the process that's trying to update the database that gets the lock message.

Thank you!

Francis
0
 
LVL 35

Expert Comment

by:James0628
ID: 24284243
You've accepted our two posts as a solution.  Did you solve your problem?

 FWIW, I don't consider my post to be a solution.  Chris just appeared to have it backwards (the report was being blocked, instead of the update) and I wanted to make sure, because it might be a significant factor in fixing the problem.

 Assuming that you haven't actually solved the problem:

 As for the problem itself, I really don't know.  We're also using SQL Server 2005 and we have people running reports and entering data all day and, to the best of my knowledge, we don't have problems like this.

 Which version of CR are you using?  I suppose that that might be a factor.  We're using CR 10.

 What type of db connection does this report use?  Is it the same as the reports that don't have this problem?  FWIW, our reports normally use an OLE DB connection.

 Is the report reading the tables directly or using a stored procedure or ???  From your screenshot, it looks like it's _not_ using a stored procedure, but if I'm wrong and it is, the procedure doesn't update any tables, does it?  I've never tried it, but, theoretically, a CR report could execute a stored procedure that updated tables.

 James
0
 

Author Comment

by:AlainSt-Pierre
ID: 24288715
James,

If I accepted your advice, I have my reasons.  Keep you interested, be nice, make friends in the community, and give my problems new words.

The problem it seems doesn't come from Crystal, but from SQL Server.  Are you an expert in this matter?

Thanks

 
0
 
LVL 35

Expert Comment

by:James0628
ID: 24300770
Just trying to be fair.  I didn't consider my post to be a solution and it wouldn't be the first time that someone accidentally picked the wrong post when accepting a solution.  And once you accept a solution, that tells anyone watching that question that you presumably don't need any more help.  At that point I didn't realize that you had asked essentially the same question again.  FYI, duplicate questions are generally frowned upon here.

 As for SQL Server, no, I'm not an expert.  I've written some fairly complicated stored procedures and know a bit about various odds and ends, but I honestly don't know much about locks and that kind of thing, because I've never needed to.  You said that the problem report takes 3 minutes to generate.  It's not at all unusual for reports here to take that long or longer - Sometimes much, much, longer (admittedly, the "much, much, longer" reports were mostly "malfunctioning" reports, but the point is, they were accessing the db all that time).  To the best of my knowledge, we've never had a problem like the one you described.

 FYI, I'm going to post this last part in the other thread too, for anyone that's reading that one and not this one.

 James
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

Read about achieving the basic levels of HRIS security in the workplace.
Get to know the ins and outs of building a web-based ERP system for your enterprise. Development timeline, technology, and costs outlined.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

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