Sorry guys. I thought included all the information needed in my original post. It has the DDL and poorly performing query included. Let me try to explain...

Basically the table #tmpDeal represents a cartesian product of IDs. The profit is determined using some formula. What i need to do is identify the ideal combination of...

Good point. The issue I'm having is the SQL-DMO.JobID does not match the msdb.jobid. I suspect the msdb.jobid is getting converted to a 32-character string.

I believe that did the trick. I really appreciate the help.

It looks like the problem is gone now.  I can not reproduce the issue to generate an execution plan.  Sorry to have bothered everyone.  Next time, I'll be sure to...

I apologize for not making the example clearer.  Here's the low down on the process.

The purpose of the stored proc is to Process / Aggregate approximately 4 million transaction header records...

Thanks for taking a look.  I made a mistake in my post when trying to change object names.  I corrected the typo.  It looks like i can only have 1...

I guess i should've included the steps we took to identify the issue.  I first noticed the problem when the tables became very large, so I assumed SQL Server cached...

Is integration services installed and running?  2005 maintenance plans require SSIS.

I had a similar problem with connecting an SSIS package on a 64 bit server to Pervasive.SQL using 32 bit drivers. 

Solution:

1) Build the package in 32 bit mode.  

2) Register the pervasive...

here's a simple example

http://blogs.simplifi.com/brucet/category/8.aspx/rss

I solved the problem using this query.  I'm not sure how it will perform yet, so I'll have to let you all know later.

Select Item, Date, Time, NewCost, OldCost

From #CostHist...

Jason, thanks for the link.  I actually similar discussions about using "Script Components" as a destination source.  It actually worked well.  I even went as far as creating script component transformations...

Believe it or not, i did exactly what you suggested prior to posting here.  The problem I'm having is the final step.  INSERT/Update pervasive via SSIS.  The datareader w/ odbc...

I appreciate everyone's input.  I tried the CTE solution and it worked, but with 1 major flaw...  It blew our TEMPDB to 50gb under a normal load (a few million...