My Crystal Reports are locking the DB because the default lock setting in SQL-SERVER is READ-COMMITTED. That means the reports will wait to read records and lock them as they are read. I want to remove all locking from my reports without rewritting the reports to use DB Views or SPs. Can someone please provide some help !!!!!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Crystal Reports is locking SQL-Server !!!!
- Thread starter dellguy
- Start date
synapsevampire
Programmer
Going against a production database is ill advised for reporting systems anyway.
EVen if it isn't locking rows or pages, it can drop the server to it's knees.
Try creating a snapshot of the database and report off of that, then you can change the row locking to allow for dirty reads (no locking) on the snapshot version.
Oherwise you'll need to include a hint with your query such as nolocks.
You can use the Add Command listed below your SQL Server connection in the database explorer (depending upon your software version, not posting technical info will limit responses, please take the time to post the basics).
-k
EVen if it isn't locking rows or pages, it can drop the server to it's knees.
Try creating a snapshot of the database and report off of that, then you can change the row locking to allow for dirty reads (no locking) on the snapshot version.
Oherwise you'll need to include a hint with your query such as nolocks.
You can use the Add Command listed below your SQL Server connection in the database explorer (depending upon your software version, not posting technical info will limit responses, please take the time to post the basics).
-k
- Thread starter
- #3
Sorry - I should have known better. We use CR Developer XI R1 on WIN XP and CR Server XI R1 on WIN 2000 accessing SQL-Server 2000.
If we run reports off of a clone DB, it would have to be cloned many times during a given day. When users run reports (especially at month-end) then want up-to-the-minute info. With adequate indexing and tight usage of them by reports, performnace should not be a huge issue for the amount of reports we run. We just do not want the reports locking the on-line app.
If we run reports off of a clone DB, it would have to be cloned many times during a given day. When users run reports (especially at month-end) then want up-to-the-minute info. With adequate indexing and tight usage of them by reports, performnace should not be a huge issue for the amount of reports we run. We just do not want the reports locking the on-line app.
synapsevampire
Programmer
Then use the Add Command, paste inb youer SQL and add in the NOLOCK (something like that) hint.
-k
-k
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question