BCP to export an SP which uses Temporary Tables not working - SQL Server 2005

Hi

I am trying to export the data from a stored procedure via bcp export. The SP uses temporary tables although the actual data in the temp tables is not the data being exported they are just used to help get the final data.

When running the BCP Export I get an error message that the Object does not exist however if I change the SP to use real tables as opposed to temporary then it runs fine.

I have read that there is no problem exporting from temporary tables with BCP but I am not exporting the data in the temporary tables is this what is causing the problem, it seems a but strange that you can only use them if the data contained is what is being exported.

Can anyone explain?

Thanks

Paul

Maybe you can post some code.

I'm somewhat confused. You say it's throwing an error about the temporary tables, but you say you're exporting from the temporary tables.

|||

Paul:

If the temp tables are created inside the stored procedures then what you are doing is not going to be possible because the temp tables will go out of scope when you exit the stored procedure. This is also logically consistent with the fact that if you use permanent tables instead of temporary tables that the BCP works.

It seems to me that the most likely scenario for you to get your stored procedure operate on a temp table and then have BCP also operate on the same temp table is if (1) your temp table is a global temp table -- that is, it starts with ## instead of # -- that is created before the the stored procedure is call by the connection that also calls the stored procedure and (2) the connection is maintained and the global temp table is not dropped. Under these circumstances you should be able to use BCP on the global temp table.

It would be simpler if you could convert your stored procedure to a function or a view.

|||

Hi Kent

Thanks for the reply I have tried using global temp tables and i get the same error but with ## before the object name so it would appear that it is not going to work with temp tables at all. This may not be a problem as I can always create them then drop them so in effect they are temporary.

Problem with converting to anything else is the whole routine is someone elses that they have been working on for many months I was just trying to help with the BCP aspect, it is a large amount of code and apart from the temp tables it does work we were just trying to understand why it wouldnt work so it can be documented or if possible we could have fixed it.

I can see the problem with the local temp tables thanks to your help but dont see why the global ones wouldnt work I even tried without dropping them at the end of the SP. Oh wait a moment are you saying that the global temp tables would need to be created before the SP is executed and therefore my order of events would be

1. Create Global Temp Tables

2. Execute SP in the BCP export Command

3. Once all is done and happy with the results send in another SQL statement to Drop the Global Tables

If so that should be a good enough reason for us to create real tables and drop them instead.

Thanks for your help

Paul