I'm in a similar spot.  I wouldn't expect the optimiser to look at any tables except the one(s) that I specify in the where clause on the partitioning column, but...

Here's an update...  I checked out the estimated query plan for one of the queries that I'm trying to get to use the VW_REVENUE view and it's not considering the...

ABOVE AND BEYOND.  Thanks a heap you guys.  I'll have something in place today and I'll update this string with deets.

Thanks again fellas.

Greg

Thanks David, but I think a "NOT EXISTS" subquery for millions of rows and 30+ columns will take too long for my sitch.  I remember MINUS was pretty quick for...

nice.  Thanks.  that's what I'm looking for Philip. 

I appreciate the idea Sergiy, but...

Setting up one table from the partitioned tables alone is too tall of an order.  Spacially, we can't afford it, and it would probably take...

thanks gang. I'm going to go with grambow's idea of partitioned views on the partitioned tables.  this makes the whole "last_2" thing a moot point.  I'll just include the partition...

Thanks Kenneth, but that's not a viable solution.  Each month has about 400,000 rows, and the data goes back to 2002-01-01, so having one main table would make things too...

Thanks Ray, but I'm really looking for a "rule of thumb" when it comes to creating an index when you already have an exact query in mind.

I understand that higher...

NEVERMIND! I GOT IT!!!

I had to switch the output param name with the local variable:

DECLARE @paramdef NVARCHAR(128)

DECLARE @cmd NVARCHAR(512)

DECLARE @a INT

SET @cmd = N'SELECT * INTO...

Thanks Sergiy, but nothing in the 4 subtopics under sp_executesql in my BOL about "output" params

Here's what I've fabricated so far and it's not working:

DROP TABLE zzzgregtemp

GO

DECLARE @paramdef NVARCHAR(128)

DECLARE @cmd...

thanks Mike, but this yields the scoping issue I can't get around. I need to be able to do something with the value of @@ROWCOUNT, so I can't just...

nice. that did the trick. and you're right, I was looking for the tables that *could* have exceeded the 8060 limit.

I was getting some system tables (some are...

Debugger can be frustrating.  Keep this command around.  It doesn't sound like your problem, but it's helped me out before.  Not in BOL, but I'm sure the heads here could...

SQLBill has it.  Maybe the full recovery model isn't for you?  Could you bcp-ify the portion of the DTS package that is causing the tlog growth?  That way you could...