cancel
Showing results for 
Search instead for 
Did you mean: 

Deadlock in a report

Former Member
0 Kudos

Hello,

I have some issues in a report that shares tables with another process. After some delay the report is sending and exception with error code 1205, that is known in SQL Server as deadlock.

I've made some tests and I've added in the registry the key

Software\Business Objects\Suite 11.5\Crystal Reports\Database\ODBC = 1

That assumes a read uncommited transaction and no locks.

However, It's not enought because the error persists.

Is there a way to avoid Crystal locks tables in the query execution??

Thanks in advance.

Regards.

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Hi,

Thanks for reply.

There are some reports that are linked by Crystal, some others use views. There is a special case with a view that already uses no lock clause and still it's being locked by Crystal at run the report.

Any suggest?

Thanks.

Former Member
0 Kudos

Ah, afraid not - well, no easy to implement solutions at any rate...

We actually had such an issue with locking using Crystal (Enterprise) and general performance degredation, that I built a reporting server which has it's data shipped over from the 'live' db, so it seperates the production/reporting areas.

If anyone else has any ideas, I'd be glad to hear them! (I didn't know about the reg tweak you posted earlier, for instance...)

0 Kudos

Hi Alex,

What version of Crystal are you using and can you confirm you are using ODBC as your data source driver? If you are then that key should work but it's not recommended. Uncommited data also include any info that has also been marked to be deleted so your info may not be up to date.

As with any database tool we simply request the data in the for of a SQL statement and the server does the processing. If your queries are returning that much data that it does affect performance on your DB server you may want re-design your reports so they return less data or use a stored procedure rather than using individual tables. This way if the server is that busy CR will simply wait for the data to complete before processing client side.

Stored Procedures are processed completely before passing the data to CR so there won't be any locks coming from CR side. If you have one talk to your DBA, that person should be able to optimize the SP or SQL Server to maximize performance.

Also it's known that ODBC does momentarily, nano-seconds, lock the tables while reading them so as Jaime suggested you should use OLE DB, much better performance. You may also want to try using the DataDirect ODBC driver if you must use ODBC which you can download from our support site or the link from the start page in the Designer.

Thank you

Don

Former Member
0 Kudos

Are you building your report by joining tables in Crystal, or sourcing your data from a single view/SQL command?

If the latter, you could try adding a nolock hint to your query to really force the issue - this takes the form of:

SELECT
u.UserID,
n.Forenames,
n.Surname

from Users u WITH (NOLOCK)

inner join UserNames u WITH (NOLOCK)
on u.UserID = n.UserID

Former Member
0 Kudos

Does it work any better if you use an OLE DB connection instead of ODBC?