This is an issue I have also run across in SQL 2005. It works fine using BCP from SQL 2000 but BCP from 2005 seems to handle the temporary table scope differently. Just as an example BCPing out the following:

Create a stored procedure

CREATEPROCEDURE [dbo].[gsp_foo]

AS

BEGIN

CREATE TABLE #dog(Col int)

INSERT INTO #dog VALUES(1)

SELECT * FROM #dog

DROP TABLE #dog

END

BCP Queryout from the procedure in 2005

c:\>BCP "EXEC dbc.gsp_foo" queryout "text.txt" -SRemoteServer -T

Results

SQLState = 42S02, NativeError = 208

Error = [Microsoft][SQL Native Client][SQL Server]Invalid object name '#dog'.

This code works just fine if you run the BCP from a SQL 2000 server.

Puzzled,

Michael Mockus