Get error message when trying to save data in application while running a report in Crystal Reports
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi James,
It is the process that's trying to update the database that gets the lock message.
Thank you!
Francis
It is the process that's trying to update the database that gets the lock message.
Thank you!
Francis
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
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
ASKER
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
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
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
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
ASKER
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