6

I have a stored procedure that stores values in temp tables.

It all works well, but I can not bcp it with 

exec master..xp_cmdshell 'bcp "exec sp_test '2006-07-21' " queryout c:\test.txt -c '

If I change the table to regular, then it all works. Can you not use temp tables this way?

I would not necessarily want to share the code as it contains company stuff, but it is basically like this

SELECT 
* 
INTO #Extractr
FROM 
TABLE A
WHERE ID in (4,9,14)

The error message is invalid object #Extractr

Thanks!

cupholdR
  • 75
  • 1
  • 1
  • 6

3 Answers3

6

I have just stumbled upon this a few days ago.

What I've learned from this link:

http://www.dbforums.com/microsoft-sql-server/1605565-can-we-have-temporary-table-store-procedure-when-using-bcp.html

is that it won't see temp tables as they'd be in the tempdb database not the one you are using.

Also, I got mine working by replacing the local temp tables to global ones (## instead of # with a simple replace helped me).

As @Kevin has mentioned in the comments, you can alternatively use table variables for the same purpose.

Hope this will work for you.

Alex Szabo
  • 3,274
  • 2
  • 18
  • 30
  • 1
    An alternative is using [table variables](http://msdn.microsoft.com/en-us/library/ms175010(v=sql.105).aspx) – Kevin Hogg Sep 10 '14 at 11:17
  • This "bug" hit me as well. A stored-proc stored intermediary results in a temp-table before SELECT-ing them out, and it did not work with BCP. Changing it to a table-variable worked (although table-variables have their own problems -- http://stackoverflow.com/questions/11857789/when-should-i-use-a-table-variable-vs-temporary-table-in-sql-server). Global temp tables are OK too, but you could run into concurrency issues. – NateJ Oct 06 '16 at 22:46
1

Have you tried referencing the temp table like this in your query: tempdb..#Extractr For example:

SELECT 
* 
INTO tempdb..#Extractr
FROM 
TABLE A
WHERE ID in (4,9,14)
Donal
  • 31,121
  • 10
  • 63
  • 72
  • Selecting into them works fine, it's only the BCP part. I have tried to update the bcp to use "tempdb..#Extractr" but that did not work: ````Warning = [Microsoft][SQL Server Native Client 10.0][SQL Server]Database name 'tempdb' ignored, referencing object in tempdb. ```` – cupholdR Sep 10 '14 at 11:30
0

Using table variables instead of temp tables helped me to figure it out.

DECLARE @tbl1 TABLE
(fld1 int,...)

INSERT INTO @tbl1 
SELECT * FROM Table1
Shahab J
  • 1,363
  • 11
  • 10