SQL Server: trouble referencing temp table through bcp

Posted on 2007-08-03
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.

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


Question by:wppiexperts
    LVL 10

    Expert Comment

    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'

    Author Comment

    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.

    LVL 10

    Accepted Solution

    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.

    Expert Comment

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

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

    731 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now