thanks for reply, the query was constructed in this way
CREATE PROCEDURE [SP_TEST]
-------------------------CREATE TEMP TABLE-----------------------------
TABLE A, TABLE B
TABLE C, TABLE D
------------------------------BEGINNING OF PROCESS----------------
SELECT * FROM
I tried you code and was able to duplicate your error. I tried a few work-arounds, but none of them were succesful. BCP seems to be failing to recognize any table created within the procedure, temporary or permanent.
I have not run into this before, but I have not used BCP very frequently. I am surprised, none the less.
Wait until Monday, and lets see if any other forum members recognize this error.
If it's not practically useful, then it's practically useless.
When using bcp or BULK INSERT to bulk copy data using a global temporary table, the table name must be specified at the command prompt, including initial number signs (##). For example, to bulk copy data from the global temporary table ##temp_authors to the Temp_authors.txt data file, execute at the command prompt:
bcp ##temp_authors out temp_authors.txt -c -Sservername -Usa -PpasswordHowever, do not specify the database name when using global temporary tables because temporary tables exist only in tempdb. It is possible to use a local temporary table (for example, #temp_authors) only when bulk copying data using the BULK INSERT statement.
The key point appears to be the last line. A rather interesting turn of phrase that says (in a roundabout way) - "you can't".
EDIT - in fact Nakata that is so poorly written (and assuming English isn't your first language - believe it or not it is my first language and I had to read it a couple of times) it basically says that local temp tables work with BULK INSERT but not (by implication) bcp.
thanks pootle flump and blindman,
this is bad for me with the answer, i may need to re-write the whole query, my god , that really killing me ...any other alternative i can have if possible no need to re-write all query that involve temporary table
This is a shortcoming of the way that bcp works, particularly when a stored procedure is used as the command for a "queryout" operation.
bcp needs to understand the format of the results it is about to receive, before it issues the query. If you use the "out" operation, giving a table name, it issues the following command:
SET FMTONLY ON
SELECT * FROM <table name>
SET FMTONLY OFF
If you use the "queryout" operator with an exec statement, it issues the following command:
SET FMTONLY ON
SET FMTONLY OFF
Unfortunately, this technique only works with the simplest of stored procedures, where the format of the ouput can be determined WITHOUT ACTUALLY RUNNING THE CODE IN THE PROCEDURE. Your case does not work because #TMP_CUSTOMER does not exist unless you actually run the code.
FYI, a solution with a table variable WILL work, e.g.
create proc dbo.bills_test
declare @output table (col1... , col2 ... , ....)
select col1, col2 ...
you can play with this in query analyser, to get a stored proc that will work with bcp...