Re: BCP and table variables
- From: "John Bell" <jbellnewsposts@xxxxxxxxxxx>
- Date: Mon, 15 Jun 2009 21:35:47 +0100
"Rick" <rick0726@xxxxxxxxxxx> wrote in message news:f843668a-35af-40d2-95de-4645c6b607f5@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
The stored procedure that's being executed by BCP is also what creates
the global temp table. I need to have BCP run the proc to create the
temp table *and* do a QUERYOUT from that stored proc, whose last line
SELECTs from the temp table that it just created. That's what doesn't
seem to work, even when I use a global temp table.
But it works fine if I use a table variable instead of a global temp
table. I'm just trying to understand why that would be.
On Jun 15, 10:54 am, "Tony Rogerson" <tonyroger...@xxxxxxxxxx> wrote:
Hi Rick,
BCP is an external process (bcp.exe), in the queryout you could declare a
batch of sql that returned a result set I guess that uses a table variable.
To BCP in/out from a global temp table...
bcp ##traceout_callcredit out somefile.txt -c -S -T'
Hi Rich
Tables referenced in the procedure have to exist beforehand:
From BOL:"The query can reference a stored procedure as long as all tables referenced inside the stored procedure exist prior to executing the bcp statement. For example, if the stored procedure generates a temp table, the bcp statement fails because the temp table is available only at run time and not at statement execution time. In this case, consider inserting the results of the stored procedure into a table and then use bcp to copy the data from the table into a data file."
So you had:
CREATE PROCEDURE P1
AS
DECLARE @t TABLE ( id int )
INSERT INTO @t (id ) VALUES ( 1), (2)
SELECT * FROM @t
GO
CREATE PROCEDURE P2
AS
CREATE TABLE #t ( id int )
INSERT INTO #t (id ) VALUES ( 1), (2)
SELECT * FROM #t
GO
CREATE PROCEDURE P3
AS
CREATE TABLE ##t ( id int )
INSERT INTO ##t (id ) VALUES ( 1), (2)
SELECT * FROM ##t
GO
CREATE TABLE ##t_global ( id int )
GO
CREATE PROCEDURE P4
AS
INSERT INTO ##t_global (id ) VALUES ( 1), (2)
SELECT * FROM ##t_global
GO
C:\Temp>BCP "EXEC tempdb..p1" QUERYOUT p1out.txt -S (Local) -T
Enter the file storage type of field id [int-null]:
Interrupt - Bulk copy aborted.
C:\Temp>BCP "EXEC tempdb..p1" QUERYOUT p1out.txt -S (Local) -T -c
Starting copy...
2 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1 Average : (2000.00 rows per sec.)
C:\Temp>BCP "EXEC tempdb..p2" QUERYOUT p2out.txt -S (Local) -T -c
SQLState = S0002, NativeError = 208
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object nam
e '#t'.
C:\Temp>BCP "EXEC tempdb..p3" QUERYOUT p3out.txt -S (Local) -T -c
SQLState = S0002, NativeError = 208
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object nam
e '##t'.
C:\Temp>BCP "EXEC tempdb..p4" QUERYOUT p4out.txt -S (Local) -T -c
Starting copy...
2 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1 Average : (2000.00 rows per sec.)
It's not really that it's a global temporary table, the important thing is it exists beforehand.
John
.
- References:
- BCP and table variables
- From: Rick
- Re: BCP and table variables
- From: Tony Rogerson
- Re: BCP and table variables
- From: Rick
- BCP and table variables
- Prev by Date: Re: Performance question
- Next by Date: Re: AdventureWorks testdatabase from MS
- Previous by thread: Re: BCP and table variables
- Next by thread: Re: BCP and table variables
- Index(es):
Relevant Pages
|
