Why bcp cannot detect temporary table ?

[CPM]

Hello, please advise

Code:
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 and display result

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

thanks for suggestions -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[shaad]

should you as part of your stored procedure first check whether #TMP_CUSTOMER exists, and if it doesn't, create it ? -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[CPM]

hello

As i know temporary not necessary to check it existing , cause it is temporary table , so it will create in every new thread and drop after use , correct me if i wrong. -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[shaad]

Not sure about temporary files since i hardly ever use them, but it can't hurt to try a belts-and-braces approach and see if it solves the problem.

Bye for now -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --