Gail,

Yes running the proc in QA would give me the execution plan - would that be the same displaying the estimated execution plan (CTRL L)? Running the proc might...

Very nice but it's not the answer to the question.

Jez

table variable vs #table- possible but you cannot create an index on a table variable.

But the question remains - should I extract the records I need from a...

This type of problem is usually caused by either mapping or permissions.

When you run the DTS package through EM it runs from your local machine so any drive mappings are...

Have a look at http://sqlblogcasts.com/blogs/antxxxx/archive/2006/11/30/script-to-find-out-what-a-spid-is-doing.aspx

Having got the spid from sp_who2, you can call the code from this webiste - the last select returns the T-SQL statement that is currently running.

Jez

Have you had a look at sp_who2? This give the amount of CPU time for a given process.

You could write a proc to put the results into a temp...

No. You can only do this by having a job that runs (say every 5 minutes) to send the emails or execute other batch proceses.

Jez

You cannot use a # temporary table - the table would be created in one object but dropped when DTS moves onto the next object as the connection is broken...

Why would the collations be different given that the database on the test server is a restore of the live database?

Jez

We already have WITH RECOMPILE in the procs and it seemed to make no difference. That's why I made the other changes.

Our processing sometimes involves a few thousand rows...

Have a look at xp_sendmail in Books on Line.

Jez

Andrew has hit the nail on the head.

I have amended the procs so that they use temporary (#) tables and local variables rather than the parameters passed into the proc...

Jeff,

The database on the test server is a restore of live so the tables will have the same rowcount, the indexes are identical and the triggers (one to update the...

We've tried running profiler but not had much sucess (no real experience as to what to record).

We have run sp_who2 and it was the only process running on the box...

I'm not a server engineer so all of that is over my head. I cannot believe that it is something wrong with SQL Server as we have the same...