MS SQL Server - BCP Temporary Tables

Author

BCP Temporary Tables

Bob Badger

2005-10-30, 8:23 pm

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

Jasper Smith

2005-10-30, 8:23 pm

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
>

Sponsored Links