Thanks, Raja - this works great!

A few mods:

1. Changed the VARCHAR(8000) variables to VARCHAR(MAX) to avoid problem noted by another respondent.

2. To handle ORDER BY clauses, I added code to...

Thanks Jeff!

Carl

Thanks, Razvan.

Carl

Thanks, Noel - that makes sense.

Carl

Thanks, Phil.

Carl

Thanks, jcool - I'll check this out too!

Thanks, Jonathan! I'll try it out.

Thanks to all for your help. The most effective tweak by far was to avoid doing joins on the archive table, using instead a temp version of the archive table...

5409045121009, thanks for the ideas on query optimization. Yes, the date field is indexed. I can see the reasons for all your suggestions except placing MOAT as the first table...

Thanks for the help from each of you. mromm seems to be on the right track about indexes - DBCC DEFRAG, sp_udatestats and DBCC updateusage had no effect on the...

quote:

---

Carl, In SQL, global search and replace for procs is a query useing the command replace run against the right sys table......

Thanks for your speedy responses - I appreciate it. Given the constraints, I think I'll settle for the template option. It would be great if SQL Server had global search...

Thanks very much Simon. I do have recovery mode set to Simple and there are no other users on the system so I don't think I'd gain anything by an...