Anyone who has used SQL's bcp (bulk copy) program knows what I'm talking about here: If you want to use bcp, it won't work with any temporary tables, whether session level or global level. One has to use a normal MSSQL table, which creates all kinds of issues if we're writing stored procedures which read in files and record information on the content, or just want to output some information on a table to a file.
It just can't be done. It doesn't seem like it would have been a big deal to have a global @@SESSION value, or local @@GET_CURRENT_SESSION parameter, that one could use to identify a session temporary table and pass it in as a parameter to bcp.
It's one of those little things that ten and twenty years ago Microsoft would have included, and is what used to set them apart from other companies. I can't imagine what it's like working for Microsoft these days, but it must be difficult to get a lot of the little good ideas past the vast bureaucracy of product development and product management/marketing teams--into products. From writing Billboard Top Ten software before, my experience has been that it's the attention to detail of the little things that make a good product great.
We can only hope that Microsoft does something about this in an MSSQL Service Pack. The whole idea of temporary tables is to create tables that are used in process and then easily gotten rid of. And one of the important things about any process is the ability to report on it for those who do not have access to the server, and yet are responsible for data quality.
The workaround we came up with is to include a unique process id each time a stored procedure is called asynchronously to verify the quality of incoming data before allowing any updates to a database. Then, when completed, we delete all the records in the table for that process id. Ugly, but it works.