can we have temporary table in store procedure when using BCP ?

hi, good day,

when i run query in query analyzer

exec sp_test '2006-07-21'

it show result , however when i run it using BCP tools

exec master..xp_cmdshell 'bcp "exec sp_test '2006-07-21' " queryout c:\test.txt -c '

it give me error which is something like "invalid object #temp_tbl",
i do using temporary table in store procedure

how to we solve it ? seem that bcp not support temporary table ? really need guidance from expert here ,

thank you :eek:Hmph. I would have thought that would work. Post the code for sp_test.|||thanks for reply, the query was constructed in this way

CREATE PROCEDURE [SP_TEST]

--------CREATE TEMP TABLE----------
SELECT
*
INTO #TMP_TBL1
FROM
TABLE A, TABLE B

SELECT
*
INTO #TMP_TBL2
FROM
TABLE C, TABLE D

----------BEGINNING OF PROCESS------

SELECT *
FROM
(
SELECT * FROM
#TMP_TBL1
UNION
#TMP_TBL2

)|||You are posting skeleton code. Post the actual code you are using, and the actual error message you receive.|||hi, blindman, sorry , i can't expose my company query and it would be few page query, but i use this simple store procedure to test on , it doesn't work as well

CREATE PROCEDURE [SP_TEST]

AS

SELECT *
INTO #TMP_CUSTOMER
FROM O_CUSTOMER

----------------------
SELECT DISTINCT
COUNT(CUST_CD)

FROM
#TMP_CUSTOMER
GO

when i run in query analyzer , using "exec SP_TEST" , it work

but when run
Exec Master..xp_CmdShell 'bcp "exec mydbtest..SP_TEST " queryout C:\TEST.TXT -c -Slocalhost -Usa -Ppassword'

it give error "SQLState = S0002, NativeError = 208"
and show
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#TMP_CUSTOMER'.

do u have any idea on it ?|||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.|||Hi

Did a quick RTFM:

Copying Data To or From a Temporary Table

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

HTH

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 :(|||Not a big user of bcp... I wonder if it has the same objection to Table variables...|||Hi,

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

<code>
create proc dbo.bills_test
as
begin

declare @.output table (col1... , col2 ... , ...)

insert @.output
select ...

select col1, col2 ...
from @.output

end
<\code>

you can play with this in query analyser, to get a stored proc that will work with bcp...

HTH,

Bill|||thanks everyone for contribution , i really learn a lot from this issue , i finally found that if i replace #tmp_tbl to tempdb..tmp_tbl , it work

will tempdb object will causing any performance issue , i really not sure ..

:beer:|||That may cause problems if more than one user executes your process simultaneously. You should consider using thompbil's suggestion and implementing a table variable.|||but I have not used BCP very frequently.

Oh, for the love of god....

In any case just put the results of the sproc into a table and bcp that out|||Oh, for the love of god....You got a problem wit dat, joisey-boy?|||tell me you use DTS...come on, you can tell us|||USE Northwind
GO

CREATE PROC mySproc99
AS
SET NOCOUNT ON
DECLARE @.myTabVar99 table (Col1 int)
INSERT INTO @.myTabVar99(Col1)
SELECT OrderId FROM Orders

SELECT * FROM @.myTabVar99
GO

EXEC mySproc99

exec master..xp_cmdshell 'bcp "exec Northwind.dbo.mySproc99" queryout c:\test.txt -c -T -S<servername>'

DROP PROC mySproc99
GO|||I do use DTS. To transfer data to staging tables. It's fine for migrating data. I don't do any processing within DTS.
DTS may not have the cache' of command-line utilities, then again DTS would have handled this poster's problem easily.sql