bcp error when queryout is stored procedure

I am receiving an error when executing bcp out and queryout is “exec stored
procedure”. Please see details below:
use workarea
go
if object_Id('P_TMP') is not null drop proc P_TMP
go
create PROC dbo.P_TMP
AS
BEGIN
SET NOCOUNT ON
SELECT * INTO #TMP FROM SYSOBJECTS
SELECT * FROM #TMP
END
go
exec MASTER..XP_CMDSHELL 'BCP "EXEC workarea.dbo.P_TMP" QUERYOUT
"C:\Temp\TMP.TXT" -c -Ssqldev -N'
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'#TMP'.
[code]
alter PROC dbo.P_TMP
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM SYSOBJECTS
END
go
[/code]
It should work.
Cristian Lefter, SQL Server MVP
"vygandas" <vygandas@.discussions.microsoft.com> wrote in message
news:BB661426-5BEE-4420-A122-C875A48500A5@.microsoft.com...
>I am receiving an error when executing bcp out and queryout is "exec stored
> procedure". Please see details below:
> use workarea
> go
> if object_Id('P_TMP') is not null drop proc P_TMP
> go
> create PROC dbo.P_TMP
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT * INTO #TMP FROM SYSOBJECTS
> SELECT * FROM #TMP
> END
> go
> exec MASTER..XP_CMDSHELL 'BCP "EXEC workarea.dbo.P_TMP" QUERYOUT
> "C:\Temp\TMP.TXT" -c -Ssqldev -N'
> --
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> '#TMP'.
>
|||Hi,
Scope of temp (#) table will be lost in next session . So try using global
temp (##) table.
Thanks
Hari
SQL Server MVP
"vygandas" <vygandas@.discussions.microsoft.com> wrote in message
news:BB661426-5BEE-4420-A122-C875A48500A5@.microsoft.com...
>I am receiving an error when executing bcp out and queryout is "exec stored
> procedure". Please see details below:
> use workarea
> go
> if object_Id('P_TMP') is not null drop proc P_TMP
> go
> create PROC dbo.P_TMP
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT * INTO #TMP FROM SYSOBJECTS
> SELECT * FROM #TMP
> END
> go
> exec MASTER..XP_CMDSHELL 'BCP "EXEC workarea.dbo.P_TMP" QUERYOUT
> "C:\Temp\TMP.TXT" -c -Ssqldev -N'
> --
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> '#TMP'.
>
|||I posted previous code just as an example, what I think is incorrect BCP
behavior. Applications (including bcp) should not be aware how stored
procedure produces result set.
Real stored procedure is very large and it populates temporary table through
its all execution. I will consider use of global or "permanent" tables to
work around this, but it will not be trivial, because the stored procedure
will be executed in multiple processes at same time.
Does anybody know how I can submit bug fix (improvement) request to Microsoft?
Thank you for your responses!
Vygandas
MCDBA, MCSD
|||BCP tries to get how the result set will look like in order to generate the file format correctly.
It uses SET FMTONLY ON for this. You can try adding SET FMTONLY OFF in the beginning of he "query
(proc)" you will execute so that the temp table will actually be created, but be aware that the proc
then will be executed twice!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"vygandas" <vygandas@.discussions.microsoft.com> wrote in message
news:424A4746-2C0C-4D00-B529-82FE4856E371@.microsoft.com...
>I posted previous code just as an example, what I think is incorrect BCP
> behavior. Applications (including bcp) should not be aware how stored
> procedure produces result set.
> Real stored procedure is very large and it populates temporary table through
> its all execution. I will consider use of global or "permanent" tables to
> work around this, but it will not be trivial, because the stored procedure
> will be executed in multiple processes at same time.
> Does anybody know how I can submit bug fix (improvement) request to Microsoft?
> Thank you for your responses!
> Vygandas
> MCDBA, MCSD
>