Export a execution time generated table inside a store procedure to a file SQL Server 2008

How Do you export a table to a file befores it is deleted by a store procedure, I am executing the following(code from @Adam):

SET @table_name = 'DYNAMIC_GENERATED_TABLE'
SET @qu开发者_Python百科ery      = 'select * from ' + @table_name
SET @saveas     = 'c:/sqlTest/QueryOutput.txt'
SET @bcpdelim   = '|'
SET @bcpconn    = '-T' -- Trusted


SET @bcpquery = 'bcp "' + replace(@query, char(10), '') + '" QUERYOUT "' + @saveas + '" -c -t^' + @bcpdelim + ' ' + @bcpconn + ' -S ' + @@servername
EXEC master..xp_cmdshell @bcpquery  

So as the table 'DYNAMIC_GENERATED_TABLE' is generated inside the store procedure, I get an error:

SQLState = S0002, NativeError = 208
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'DYNAMIC_GENERATED_TABLE'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
NULL

If I execute the code for a table I already have, There are no errors.

How to fix this

Try BCP via xp_cmdshell

http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/

Example, from the above site:

declare @sql varchar(8000)
select @sql = 'bcp master..sysobjects out
                c:\bcp\sysobjects.txt -c -t, -T -S'+ @@servername
exec master..xp_cmdshell @sql

Seems making global the table

'DYNAMIC_GENERATED_TABLE' -> '##DYNAMIC_GENERATED_TABLE'

does the trick

SQLState = 37000, NativeError = 156 Error = [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near the keyword 'where'. NULL

i am using the bcp command getting this error

from my 1 str proc where bcp is defined i am calling another str proc in bcp. But if i run that another str proc using EXEC it runs fine. What will be the issue?