Robert

Please will you provide some DDL, sample data and expected results, since it's hard to see what you're trying to do.

Adding in that WHERE clause turns your OUTER JOIN into...

Yes, and he hasn't even had the courtesy to say thank you.  No doubt you've already added him to your mental list of users that you won't bother helping again......

Krishna

I notice you still haven't posted the (exact) error message.  However, from what you have said, I think I can help to point you in the right direction: you should...

Krishna

"Some prob" is too vague.  And I don't see "curren_area" anywhere in the query.  If there is an error message, please post it.  If the query is not returning the...

Krishna

Care to tell us what the problems are?

John

Jorge

I think you need to study the SQL Server 2005 security model - there's plenty of material in Books Online.  Good luck!

Also, I would try out sqldiag, which is part...

Jorge

Presumably this is the output of some health-checking tool that already exists?  Does sqldiag.exe provide this?

John

Something like this?

SELECT Word FROM #RestrictedWords

WHERE CHARINDEX(Word, @TestWord) > 0

John

Jorge

I don't understand exactly what you require, then.  Please will you give an example of how your report should look.

Thanks

John

Jorge

If you're using SQL Server 2000, use the code below.  If you're on SQL Server 2005, use the sys.database_permissions view instead - you don't need a temp table.  Full details...

Krishna

OK, that's different from what you gave before, but at least I understand it!  Now, please will you confirm what version of SQL Server you're using?  And WHY does your...

Mark

Is it possible that the SQL Server service has been stopped and started since the connection was made to the database?

John

Krishna

I think I might understand what you want... for each invoice, you want to populate the days_in_area column with the number of different days that invoice has appeared on, but...

Mark

Yes, a similar thing happens with your original sample data as well.  Now we just need to see what happens when you get hold of all the extra data.  What indexes do you...

Mark

OK, please let us know when you've done that.  My inkling may have been wrong, since the query optimizer may not totally materialise the CTE anyway.  If you SET STATISTICS...