Yet another blog posting on BCP and Temporary Tables!!!

Published 16 June 09 01:00 PM | MartinBell
A question came up in the newsgroups yesterday about an error the person posting the question was having when trying to use BCP and a stored procedure being executed using the QUERYOUT option. Using BCP to run a procedure in this way was not something I can remember doing before. Usually if I want to export data that required manipulation, I would process that data in a user table or global temporary table before calling BCP, in which case I could just use the OUT option and not have to run a stored procedure. I think this is the best method, and MVP Erland Sommarskog seems to concur. Doing too much work in the stored procedure could possibly lead to timeouts by BCP, although I have never put this to the test!!.

If you look in Books Online when defining the “query” parameter for the BCP command it says:

"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."

This is not quite true… all tables referenced inside the stored procedure do not necessarily have to exist prior to executing!! But taken on face value, this would seem to rule out using local temporary tables (or global temporary table created in the procedure), but that isn’t the case as I will show later in this article.

Examples of how BCP can fail or not when a temporary table or global temporary table is created within the stored procedure and how a table variable or global table variable can work, are as follows:

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

Which if you ran the appropriate BCP commands would give you the following successes and failures:

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 name '#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 name '##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.)

Erland explained the reason for the poster having problems was because “BCP needs to figure out the structure of the result set before it is returned. To do that it runs the procedure in a special mode, but the table is not created in that mode and so it fails. A table variable on the hand is a declared entity and that works”

Which makes sense considering the statement in BOL, but I did say that the statement is not quite right!! If you run SQL Profiler as you execute the BCP command:

BCP "EXEC tempdb..sp_helpdb" QUERYOUT hlpdbout.txt -S (Local) -T -c

The command will give you an error similar to the earlier procedure calls:

SQLState = S0002, NativeError = 208
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name '#spdbdesc'.

And in SQL Profiler you’ll see that the batch that BCP actual sends the following batch to be executed:

set fmtonly on EXEC master..sp_helpdb set fmtonly off

On the SQLServerCentral forums I found a way of “fooling” BCP by NOT returning a “dummy” result set!! The “dummy” result set should have the same structure that your procedure will return, so I now wish I had chosen an easier example as this will wrap!!

BCP "IF 1=0 BEGIN SELECT CAST('name' AS nvarchar(256)),CAST('db_size' AS nvarchar(26)),CAST('owner' AS nvarchar(256)),CAST(0 AS smallint),CAST('created' AS nvarchar(22)),CAST('status' AS nvarchar(1200)),CAST(0 AS tinyint) END; SET FMTONLY OFF; EXEC tempdb..sp_helpdb" QUERYOUT hlpdbout.txt -S (Local) -T -c

Starting copy...

15 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 125    Average : (120.00 rows per sec.)

Great it works!!

So that was going to be the end of this article, but I then thought I would look at profiler to see what was happening...



As you can hopefully see the procedure (sp_helpdb) is run twice, which from a performance aspect could be quite expensive.

If this is a procedure that you have written yourself, then you may want to use one of the other techniques detailed above (that works). If for some reason you can’t or don't want to modify the procedure, you can still get around this by using a global temporary table or table variable to store and returned the results e.g.

USE tempdb
GO

CREATE PROCEDURE usp_helpdb
AS
DECLARE @t TABLE ( [name] nvarchar(256),
                                    [db_size] nvarchar(26),
                                    [owner] nvarchar(256),
                                    [dbid] smallint,
                                    [created] nvarchar(22),
                                    [status] nvarchar(1200),
                                    [compatibility_level] tinyint );
INSERT INTO @t ([name],
                                    [db_size],
                                    [owner],
                                    [dbid],
                                    [created],
                                    [status],
                                    [compatibility_level] 
                                    )
EXEC sp_helpdb;
SELECT * FROM @t;
GO

In Profiler you can see the when usp_helpdb is run with SET FMTONLY ON a minimal amount of work is carried out (4 reads 0 CPU).
Filed under: , ,

Comments

No Comments