Search code examples
sqlbcp

bcp won't output temp tables


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!


Solution

  • 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.