bcp won't output temp tables

Question

I have a stored procedure that stores values in temp tables.

It all works well, but I can not bcp it with

exec master..xp_cmdshell 'bcp "exec sp_test '2006-07-21' " queryout c:\test.txt -c '

If I change the table to regular, then it all works. Can you not use temp tables this way?

I would not necessarily want to share the code as it contains company stuff, but it is basically like this

SELECT 
* 
INTO #Extractr
FROM 
TABLE A
WHERE ID in (4,9,14)

The error message is invalid object #Extractr

Thanks!

Answer 1

I have just stumbled upon this a few days ago.

What I've learned from this link:

http://www.dbforums.com/microsoft-sql-server/1605565-can-we-have-temporary-table-store-procedure-when-using-bcp.html

is that it won't see temp tables as they'd be in the tempdb database not the one you are using.

Also, I got mine working by replacing the local temp tables to global ones (## instead of # with a simple replace helped me).

As @Kevin has mentioned in the comments, you can alternatively use table variables for the same purpose.

Hope this will work for you.

Answer 2

Have you tried referencing the temp table like this in your query: tempdb..#Extractr For example:

SELECT 
* 
INTO tempdb..#Extractr
FROM 
TABLE A
WHERE ID in (4,9,14)