Lynn,

Your point on the Backup Log is very well taken. I forgot to mention it. Without it I've seen transaction logs grow from around 100 MB to over...

Your procedure doesn't actually use a cursor. You are using a simple while loop.

I've used code like yours for years to do deletes and it seems to be about...

You might want to check to see if the foreign key defined in the child table actually has an index on it. It is a common misconception that creating...

Tony,

Thanks - I'll check it out.

Todd Fifield

Your problem is probably that the table in SQL 2000 doesn't have a Primary Key. You can edit the table in the Enterprise Manager, but Access won't allow it...

I used to get burned on this in SQL 2000 and ever since I've always used the same WHERE clause in the glue table. Since then I've never had...

I've never used SQL Server Express so I don't know if this will help. The Distributed Transaction Coordinator (DTC) has to be running on both servers.

Your insert code should...

Matt,

Thanks for the pointer. I'll have a look and see if it makes any sense to add some sort of identity as a clustered index. With over 200...

Thanks for the quick reply.

Is there a way of adding an Identity to the table schema that is not for replication?

Todd Fifield

No mention yet of the common trigger mistake:

SELECT @Variable = SomeValue

FROM inserted

The becoming mystified why only 1 row was processed in the trigger.

I still see this in a lot...

If all the data being fetched was in a covering index, would that guarantee the order?

Todd Fifield

Gail,

Wow! I never thought about the parallel problem. It gets uglier all the time. I'd have to tweak MAXDOP in order to guarantee that it was done...

For what it's worth, the only way I've been able to get around the ORDER BY problem in derived tables and functions is to create an index and then use...

Matt and Jeff,

The points are well taken.

Todd Fifield

Jeff,

As I pointed out the sequence table is ugly and only works for a single insert. It should only be done when there are interactive inserts done by users....