I create the following stored procedure:
USE Northwind
GO
CREATE PROCEDURE mgr_Proc
AS
SET NOCOUNT ON
SELECT *
INTO #Temp1
FROM Northwind.dbo.Region
SELECT *
FROM #Temp1
DROP TABLE #Temp1
SET NOCOUNT OFF
GO
If I run the stored procedure in Query Analyzer with:
EXEC mgr_Proc
it works fine. However if I try to use BCP to get the same results:
bcp "EXEC Northwind.dbo.mgr_Proc" queryout C:\Test.txt -w -Sserver10 -T -t\t
I get an error:
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#Temp1'.
What am I doing wrong
BCPing results from sproc that uses temp tables
Rob Washo
I think if you want to use temp tables with bcp.exe, they have to exist prior to running the statement, you can't create them and reference them on the fly like you're doing in a proc.
Maybe you can try two things (I haven't tested either one):
1. try using a table variable instead of a temp table
2. create an empty global temp table before hand, and change the proc to reference the global temp table.