"I think we may all have missed an obvious point here (or perhaps it's only just dawned on me?)"

That's the point I...

Phil has invited me in, here's my tuppence worth.

I altered the SP philproc to use Dynamic SQL and called it two different ways. ...

Right, Ive used your query as the basis for a NOT in Set. 

No where near as elegant as yours, but it runs in 25 seconds & meets the new...

It certainly seems to.  Thank you

And it runs in 21 seconds too

Of course they have checked...

As you know, refering to an instance is done by Server\Instancename, the backslash may cause problems with a badly written app.  But if the app isn't instance aware, I wouldn't...

This is straight from BOL:

Specifying Joins in FROM or WHERE Clauses

The rows selected by a query are filtered first by the FROM clause join conditions, then the WHERE clause search...

I'd use 3 SQL Instances. The overhead isn't that much, so the if the Single Server Hardware will meet your needs/load it will probably be able to manage 3...

Good article, I always enjoy arguing with oracle dbas on this, and use the example in the BOL as evidence on why joining in the where clause is just wrong....

It's only common courtesy to post the solutions, especially when the issue is a generic one.

I always try to post them as:

1. I...

Jenifer

Take a look at Jeffs solution for my 'pivot' problem.

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=242919

It builds the Case statement for you, & I think is a very elegant solution to this thorny problem.

Dave J

Here is the sp, with typos taken out.  Jeff hadn't tested his code, just typed it straight out.  Which only increases my respect for the man. 

Ian,

your solution is OK if you know what the attribute names are in advance.  My problem is I don't.

So, I don't see an...

Jeff,

I am not worthy!

If you are ever in Manchester UK, I'll buy you a pint.

Cheers.

Dave J

Thanks for the Uri, but it still uses an aggregate

I have seen similar approaches, and had a go at adapting them but end...

I've got this, but to be fair I don't use it.  It is very heavily based on a script posted on this site by David A. Long, currently...