Microsoft SQL Server 2000 Administrator's Pocket Consultant

William R Stanek

Microsoft Press ISBN 0-7356-1129-7

Good book, but misleading title.  It would have to be a very large pocket.

Take a look at the documentation...

Everybody knows you don't drive nails with a screwdriver, that's what monkey wrenches are for!

There is no user interface on the processing program.  That would get confusing with multiple copies...

I don't have a problem with a set-based approach, but I do have a problem with forcing the entire job to be done in one set.  If the size of...

I know that #temp tables are unique per session.  I interpreted your suggestion to use a single temp table literally, so since there are multiple connections I assumed you must have meant a...

The main objection to a single temp table is that a job may run to over 20 million rows, and I don't like 20 million row updates.  If I've gone...

Big Foam Cluebat (see http://ars.userfriendly.org/cartoons/?id=20030311 and http://ars.userfriendly.org/cartoons/?id=20030211)

Good for LART parties (Luser Attitude Readjustment Tool)

I realized the ADO method was getting the benefit of a cached execution plan when I saw it was using sp_executesql.  I wll probably try that.  It also cuts down...

The server-side cursor is on the old program, and if it is asking for a large chunk of data based on a field other than the clustered index it takes...

Remi - forget it, this will never be rewritten in SQL.  There is a lot of processing on the client for each row.  I would rather tie up a couple...

There is a lot of VB code used to determine the update values, and they're unique for each row.  Some of the processing uses third-party dlls, some COM and some...

Just to clarify, I haven't migrated the code to .NET yet but I've rewritten it to work like a DataReader and SQLCommand so migration will be easier when the time...

Maybe I have too much time on my hands today, but I'm still guessing.

My previous post was too simplistic, it would produce the same result as putting all non-aggregates fields...

Approach 1: Write the trigger on the subscriber table.  Inserts on the published table will be replicated, then the trigger will fire there to do the additional inserts.

Approach 2: Lookup...

It sounds like you need to join the detail records to a grouped subquery.

select a.ID, a.f1, a.f2, a.f3, b.f4

from tbl a

inner join (

    select ID, SUM(f4) as f4

    from tbl group...

I would agree that table variables will *USUALLY* be better than temp tables, the original question was about getting an execution plan.

You can't get an execution plan for a table...