BCP Temporary Tables

Hi,

I am trying to bcp data from a txt file into a temp table:

CREATE TABLE #output
(FIRSTNAME varchar NOT NULL,
lastname VARCHAR(32) NOT NULL,
state VARCHAR(14) NULL )

master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'

I am running this within the dbtemp database. I am getting the error:

SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid o
bject
name '#output'.
NULL

When I run it against a normal table the query runs fine. Can anybody
tell me what I am doing wrong? Is it possible to run this into a
temporary table??

Thanks
Steffan

Temporary tables are session specific, so the new session used by osql
connecting back into SQL Server can't see the temp table created in the
original session. You can use a global temporary table (CREATE TABLE
##output) or a permanent staging table

--
HTH

Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org

"Bob Badger" <sjdavies47@hotmail.com> wrote in message
news:1130707432.996880.194530@g47g2000cwa.googlegroups.com...
> Hi,
>
> I am trying to bcp data from a txt file into a temp table:
>
> CREATE TABLE #output
> (FIRSTNAME varchar NOT NULL,
> lastname VARCHAR(32) NOT NULL,
> state VARCHAR(14) NULL )
>
> master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
>
> I am running this within the dbtemp database. I am getting the error:
>
> SQLState = S0002, NativeError = 208
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid
 object
> name '#output'.
> NULL
>
> When I run it against a normal table the query runs fine. Can anybody
> tell me what I am doing wrong? Is it possible to run this into a
> temporary table??
>
> Thanks
> Steffan
>