SQL Server: trouble referencing temp table through bcp

through a stored procedure I'm populating a temp table (#TempItems) from a series of queries.
the last step is the export the data in the #TempItems table to a text file. I'm using the following code:
exec master..xp_cmdshell 'bcp "select * from #TempItems" queryout c:\Rev_codes.txt -T -c'
which results in the follwing error messages:
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#TempItems'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

I'm assuming I'm not referencing the temp table properly in the sql statement....how should I do that?


Who is Participating?
Scratch that... the problem is most likely that the #TempItems table is out of scope to the command where you are using BCP.  You may be able to accomplish what you are trying to use with a Global Temp table (name it with two hashes instead of one: ##TempItems) - but you still need to make sure that the table is still there when you want to BCP it - e.g.: if you execute all your queries in Query Analyzer and then type your exec... command into the same session and execute it then you should be OK.
Not sure if this will work, but you could try using square brackets:

exec master..xp_cmdshell 'bcp "select * from [#TempItems]" queryout c:\Rev_codes.txt -T -c'
wppiexpertsAuthor Commented:
nope, no luck with the brackets.

I'm assuming I need to identify the owner of the temp table (I tried tempdb..TempItems), but had the same results.

I just want to add that we had the same experience. But even with a global (##) table it could not find the table. Upon closer examination, we knew that it had something to do with the instance of the SQL service running on the server. We had multiple instances, for instance [severname\instancename]. You sometimes have to specify the instance you want the BCP command to go look into when exporting data from a table to a file. Use the -S parameter to specify the specific instance of the SQL service on the server, for instance if you have two instances running on the same server you can use the following command "exec master..xp_cmdshell 'bcp "select * from master..##mytable" queryout c:\myfile.txt -T -c -S myservername\myinstancename'.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.