?
Solved

SQL Server: trouble referencing temp table through bcp

Posted on 2007-08-03
4
Medium Priority
?
386 Views
Last Modified: 2013-11-05
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.
NULL

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

Thanks!


0
Comment
Question by:wppiexperts
  • 2
4 Comments
 
LVL 10

Expert Comment

by:lahousden
ID: 19628303
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'
0
 

Author Comment

by:wppiexperts
ID: 19628346
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.

0
 
LVL 10

Accepted Solution

by:
lahousden earned 500 total points
ID: 19628390
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.
0
 

Expert Comment

by:earthdust
ID: 21109399
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'.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question