Be creative with BCP

My favorite colleague - you know the one :) - left me with a simple task before he left on a holiday. I had to finish this export for him that we decided to rewrite. He had to export several quite large recordsets to multiple XML-files. We agreed to use FOR XML EXPLICIT instead of a custom made SSIS component to be able to deploy it on our current production environment, still being SQL Server 2000.

When using FOR XML EXPLICIT, you don't get that many options to export the result to a file because of the special nature of that result. But with BCP, everything is possible - at least I thought so. Because of the complexity of the export, some data had to be staged temporarily in the stored procedure that retrieved the data and formatted the XML. And that's where the trouble started.
With the code below you simulate the core of the problem - which isn't FOR XML by the way.

USE TempDB
GO

CREATE PROC usp_bcp_test
AS
SET NOCOUNT ON
IF 1=0
BEGIN
DECLARE @Tbl TABLE (Id INT)
INSERT @Tbl VALUES (1)
END
SELECT 1 as ColA, 'test' as ColB
GO

DECLARE @Stmt VARCHAR(1024)
SET @Stmt = 'BCP "EXEC TempDB.dbo.usp_bcp_test" QUERYOUT C:\TEMP\tst.txt -c -S ' + @@SERVERNAME + ' -T'
EXEC master.dbo.xp_cmdshell @Stmt
GO

DROP PROC usp_bcp_test
GO

I try to use the output of the stored procedure as the 'query' for BCP, but this fails because of the INSERT-statement in the stored procedure, even though it's unreachable code. BCP gives the following feedback:

SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]BCP host-files must contain at least one column
NULL

Using Profiler, I discovered that BCP issued the following statement to determine the resultset it can expect.

SET FMTONLY ON EXEC TempDB.dbo.usp_bcp_test SET FMTONLY OFF

In the messages pane of SSMS you can see the following:

(0 row(s) affected)

(0 row(s) affected)

Which explains the fact that BCP complains about the stored proc: it expects the INSERT statement to give a columnlist for its resultset, which it doesn't have of course. Moving the SELECT clause to the begin of the stored proc makes this construct work because BCP only looks at the first resultset.

The solution to this problem is straightforward: split the retrieval and the formatting over multiple stored procs so that the stored proc used for the BCP only contains a simple SELECT-statement.

Nice little detail: I was only able to simulate this behavior in SQL Server 2005, not with SQL Server 2000.