Why bcp cannot detect temporary table ?

hi, good day, i facing a problem



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 guidance

should you as part of your stored procedure first check whether #TMP_CUSTOMER exists, and if it doesn't, create it ?

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

not sure about temp 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

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

Here the question is whether you r running this outside the body of the Stored Proc or not.

When you create a temporary table it is in scope for either the duration of the batch (Local Temporary Table) or for the duration of the connection (Global Temporary Table.)

A Local Temporary Table is identified by a single pound sign (#TempTable).
A Global Temporary Table is identified by a double pound sign (##TempTable).

The #temp tables has only scope with in the Stored procedures and it gets dropped after that. Try running your
master...xp_cmdshell bcp inside the sp itself

Another point is that the #temp tables are stored in tempdb database and not your normal database, so you might give a try like SELECT * FROM tempdb..tablename

Thanks
Wisenut