I'm having an issue where I need a batch process to blow out a bunch of calculated tables and then refill them with updated results. The process worked fine on our dev boxes but on our server I keep getting caught with a lock on the table I'm trying insert into. I've tried setting the single user before executing it, but I still get the lock error despite it being able to do the numerous selects needed in order to get to the insert statement.
My only other thought is that to speed thing up we drop the table and recreate it each time without the indexes and then add the indexes at the end so we don't have to pay the price when inserting in 2-4 million rows.
Here is the error we get (Dropping Calculated Resets is a print from where we drop the table):
Dropping Calculated Resets
Msg 1204, Level 19, State 4, Line 99
The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.
Any thoughts on how to get past this