Hi, John...
Thanks for responding. I realize that a hundred users creating temp tables would affect performance. I have a follow-up question.
Our app has a reporting piece where a user may report on thousands of accounts and products. The report might be summarized or detailed. The list of accounts/products they select will vary on every report run.
We basically present a MOVER-style form, and allow the user to pick the accounts and items they want. So a user might pick 10 accts and 100 items. Our current 'prototype' (a VFP app) does an SQLEXEC to do a CREATE TABLE #TMPACCT and CREATE TABLE #TMPITEM. We return the result into a VFP cursor, do a little massaging, and then pass that data over to Crystal Reports.
It then scans through the array of accts/items that the user selected in the MOVER form, and does an SQLEXEC INSERT into the temp tables.
Finally, we call a stored proc that queries against the historical data, and the two TMP tables that were created.
This 'works', though I don't know enough about 'best practices' to know if this is a good methodology that will work well with hundreds of users. (My gut tells me there's a better way to do this, but don't know what it would be.) That's pretty much the challenge I have now...I have a basic understanding of how to use the technology, but not enough of how to use it effectively.
Any feedback you can give would be great.
Thanks,
Kevin