Is there never any activity in any of the tables (other than Selects), or is the "window of no activity" short? Like a table loaded by a nightly ETL...

Here are some articles I wrote a while back on data auditing:

http://www.sqlservercentral.com/articles/Auditing/63247/

http://www.sqlservercentral.com/articles/Auditing/63248/

There are a number of trigger-based options and samples in the second one.

With regard to a trigger to record...

I'm accustomed to using the package variable, not a script-local one. But that's habit, not something based on actually comparing efficiency/performance/anything.

Even I know...

Is the query just insanely complex? Like the plan would be some simply titanic amount of RAM?

I don't think it's even possible to have a plan that large, but...

Standard, normal, boring Read Committed usually works just fine for that kind of situation.

If you find that the few in-day updates are causing more blocks than you like, switch to...

Yeah, that's a good case for breaking it up.

sqldba_newbie (11/7/2012)

---

GSquared (11/7/2012)

---

It depends on what end result you want.

Read Committed Snapshot Isolation will help prevent blocking, but allows "old data" to be seen by concurrent transactions. Doesn't have...

For Windows (Active Directory) security, you do that on your domain server, not in SQL Server. Usually, your network admin (IT Department) would create an AD group, add members...

Does it just Select stuff, or does it do any Update/Insert/Delete actions? If it does more than just Select, then it will still generate locks and can still block...

It depends on what end result you want.

Read Committed Snapshot Isolation will help prevent blocking, but allows "old data" to be seen by concurrent transactions. Doesn't have the "read...

mmartin1 (11/7/2012)

---

When your code reaches the statement

if (@Due = 'All') set @sql = 'Select * From ##temp'

It is realising that @due has not been declared. The '@due' in the string...

There is no guaranteed sequence to the actions. The whole idea of using Merge is that it makes it into a single, atomic action as far as your code...

The first one will do what you're looking for. Isolation levels are set for the connection, not for the object.

HOWEVER, I have to warn you that setting isolation to...

How much work does the query for the report do? Can you post the table definition(s) and the query?