Whenever I go into the database painter for a microsoft sql server profile, its active and connected, but when i click on tables the list of tables wouldnt retrieve it would just sit there grinding away retrieving tables ... but nothing showing,

Turns out there was a SCH-M lock on the DB caused by a SP call that was doing a retrieve into a # tmp table

The SP did not explicitly drop the temp table because I think the SQL Server dynamically names the tmp table so multi-users can be running the SP at the same time however, it would cause PB db painter to be unable to retrieve the table list because it would get blocked by that Sch-M locking block

Would there be a way around this where i could have a simple SP creating a #temptable and when it exits the stored procedure it doesnt cause such a issue,

A connection parameter maybe or something in the SP itself i could write or what?

I have temporarily removed the use of the tmp table and do just a straightup select but the purpose of the SP was so i could do a few different actions on the initial results and then finalize a single final select to return using the modified tmp table resultset to the datawindow with the last results

any ideas or suggestions?