Suggestions:

1 confirm that all of the left joins are left joins (not innner)

2 set WorkOutlet as an inner join instead of , workoutlet and handled in the where

...

Dynamic sql is an option (one timer).

create a cursor to loop through your columns (in sql2005 the cursor would reference information_schema.columns)

@aSql = 'update mytable set ' + @aColumn + '...

I haven't tested it but with the +7 & modulo against a monday (1/1/1900) it would appear to me to be independent of DATEFIRST type database property changes.

btw: LOVE...

just because...

DECLARE @aCurrentDate DATETIME

SET @aCurrentDate = GETDATE()

SELECT DATEADD(dd, -(DATEPART(weekday, @aCurrentDate)+7 - DATEPART(weekday, '1/1/1900'))%7, @aCurrentDate)

If the update is the only action...

set the recoverytype to simple (set back after execution)

good luck

Daryl

Magic bullet!

If you can update the recovery type.

Backup database.

Set recoverytype to simple

task-shrink-file-log (note: do not shrink to almost nothing, sql just has to recreate.

set recoverytype to full

add backing...

--insert the results into an import table.

insert into import_dump_results (id, val)

....

-- insert valid date results into final

insert into dump_result (id, val)

select id, val

from import_dump_results

where isdate(left(val,22)) = 1

-- update val for...

If you have the option (access). It can speed up the process to set the recovery type to Simple.

Then script a while loop that updates the top 10k? 100k?...

Have you thought about a view?

Within a view you can have derived tables, unions, cte's etc...

In one of my apps I have a view that gives me default and custom...

A- meaningful table names will almost always save effort at the time or later in maintenance.

B- do not worry about a left join. You are only updating the consultants...

I would try to identify the consultants and their points for one month. Then create a calendar table to cross reference salestype dates to identify numbers to months.

1. ...

Shot in the dark, but if the query analyzer has updated stats... ?

sp_MSForEachTable "UPDATE STATISTICS ? with fullscan"

Warning.... update stats can take a bit of time...

Daryl

The source (3rd party app usage) sql server is 2000. The warehouse is 2005.

I have not looked into log shipping, will investigate.

thanks for the new direction.

daryl

The original database ( and logical) name is FTDB. I went for a more informational name for the replicated copy.

thanks

Another option besides clearing cache is updating the table and index statistics.

I have had similar problems. Runs fine in SSMS and not from a web request. ...