ansaurus

Question

Crystal Reports with SQL Server 2005: Setting Transaction Isolation Level

Answer 1

+1 A:

I was able to embed this in a Command object:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

--Command objects need to return a value
SELECT GetDate()

This Command object was in addition to the query that I 'wrote' in the Database Expert.

Will you elaborate on the necessity of setting the ISOLATION LEVEL?

Craig 2009-11-20 16:12:06

Thanks for the answer! Will the command execute *before* the real query and in the same connection (transaction isolation levels are on a connection-by-connection basis)? I tried adding this to a new report and Crystal Reports gave me the "Your current link configuration contains multiple starting points" warning, thereby making me wonder which executes first.

Adam Paynter 2009-11-20 16:54:57

I wish to set the transaction isolation level because one of our reports has a long-running query (~2 minutes) that selects all rows from an often-used table. Because this locks (shared locks) every row in this often-used table, no one else is able to update it until the query completes.

Adam Paynter 2009-11-20 16:55:29

Honestly, I don't know when it will execute relative to the 'main' query. Is this something you can test?Another thought: perhaps you could create a special account for queries generated by reports and set this accounts isolation level.

Craig 2009-11-20 17:27:14

I didn't think transaction isolation levels could be specified on a per-user basis. Do you have an example of how I would configure this for a report-specific user?

Adam Paynter 2009-11-20 17:33:18

I don't. I'm not even sure you could do it, but I thought I would mention it as an idea. A DBA should be able to answer this question.

Craig 2009-11-20 18:19:58

Excellent! Thanks again, Craig. Wonderful answer!

Adam Paynter 2009-11-20 18:26:42

ansaurus

tags:

views:

answers:

Crystal Reports with SQL Server 2005: Setting Transaction Isolation Level

related questions