SELECT Data Set Using Stored Procedure with BCP Error

Question

All, I have a BCP export query. It is failing with the useless error mesage on BCP usage:

usage: bcp {dbtable | query} {in | out | queryout | format} datafile [-m maxerrors] [-f formatfile] [-e errfile] [-F firstrow] [-L lastrow] [-b batchsize] [-n native type] [-c character type] [-w wide character type] [-N keep non-text native] [-V file format version] [-q quoted identifier] [-C code page specifier] [-t field terminator] [-r row terminator] [-i inputfile] [-o outfile] [-a packetsize] [-S server name] [-U username] [-P password] [-T trusted connection] [-v version] [-R regional enable] [-k keep null values] [-E keep identity values] [-h "load hints"] [-x generate xml format file] [-d database name] NULL

The query I am using is as follows

DECLARE @SQL VARCHAR(8000); 
SELECT @SQL = 'bcp "EXEC ispsSelectEmptyAsNull ''B1A'';" 
 queryout "F:\aaData\IPACostData\R15TData2円BSHAEOS_B1A_20121120.txt" 
 -f "F:\aaData\IPACostData\R15TData\tmpFormatCard_B1A.fmt" -T -S' + @@SERVERNAME + ''; 
EXEC xp_cmdshell @SQL;
GO

where if I replace EXEC ispsSelectEmptyAsNull ''B1A''; with SELECT * FROM B1A; there is not problem. The query EXEC ispsSelectEmptyAsNull ''B1A''; itself runs with no problems and returns the correct result set. I have run a million BCP queries but never using an SP to provide the result set. Am I doing this correct?

The SP is as follows:

IF EXISTS (SELECT name 
 FROM sys.procedures 
 WHERE name = N'ispsSelectEmptyAsNull') 
DROP PROCEDURE ispsSelectEmptyAsNull;
GO
CREATE PROCEDURE ispsSelectEmptyAsNull @TableName NVARCHAR(256) 
AS 
DECLARE @Columns VARCHAR(MAX);
SELECT @Columns = 
 COALESCE(@Columns + N', 
 NULLIF([' + CAST(COLUMN_NAME AS VARCHAR) + N'], '''') AS ' + COLUMN_NAME + '',
 N'NULLIF([' + CAST(COLUMN_NAME AS VARCHAR) + N'], '''') AS ' + COLUMN_NAME + '') 
FROM (SELECT DISTINCT COLUMN_NAME 
 FROM [IPACostAdmin].INFORMATION_SCHEMA.COLUMNS 
 WHERE TABLE_NAME = @TableName) AS H 
ORDER BY COLUMN_NAME; 
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'
 SELECT ' + @Columns + N' 
 FROM ' + @TableName + N';';
-- SELECT CONVERT(XML, @SQL);
EXEC (@SQL);
GO

Thanks very much for your time.

Ps. I do not care about SQL injection etc.

• John K. N.: the bcp syntax look ok. I double checked with BOL and the only limitation for bcp is that if you use a procedure all tables have to exist prior to bcp being executed.
• Stuart Moore: Does the BCP command run if you run it direct from the command line rather than xp_cmdshell?
• MoonKnight: I have not tried that @StuartMoore. I will attempt to do that now...
• Marian: It couldn't run. That's why the bcp default message. It wasn't executed properly.

Answer 1

You have CR\LF in shell invocation, a no-no. Make it a single line:

DECLARE @SQL VARCHAR(8000); 
SELECT @SQL = 'bcp "EXEC ispsSelectEmptyAsNull ''B1A'';" '+ 
 'queryout "F:\aaData\IPACostData\R15TData2円BSHAEOS_B1A_20121120.txt" '+
 '-f "F:\aaData\IPACostData\R15TData\tmpFormatCard_B1A.fmt" -T -S' + @@SERVERNAME + ''; 
EXEC xp_cmdshell @SQL;
GO

• Remus Rusanu: A bcp out format file specified that a column should be skipped. This is not allowed. Either create a view containing only the desired columns and bcp out from that view, or use the -Q flag to provide a SELECT statement selecting only the desired columns.. The problem is your format file.
• John K. N.: True. How did I miss that one.
• MoonKnight: Why? Basically, the SP is casting Empty.Strings to nulls to get around BCP putting the ASCII CHAR(0)/Nul character in the output file. Running SELECT * FROM [2BSHAEOS]..[B1A]; as the query works. I think you are right and this is occuring due to the fields specified in the format file being of an incorrect order as the fields output by my SP - this goes from bad to worse! You should see the SP that creates the .fmt file!!

Answer 2

At first I would try to change everything to be on the same line:

Instead:

SELECT @SQL = 'bcp "EXEC ispsSelectEmptyAsNull ''B1A'';" 
 queryout "F:\aaData\IPACostData\R15TData2円BSHAEOS_B1A_20121120.txt" 
 -f "F:\aaData\IPACostData\R15TData\tmpFormatCard_B1A.fmt" -T -S' + @@SERVERNAME + ''; 

Change it to:

SELECT @SQL = 'bcp "EXEC ispsSelectEmptyAsNull ''B1A'';" queryout "F:\aaData\IPACostData\R15TData2円BSHAEOS_B1A_20121120.txt" -f "F:\aaData\IPACostData\R15TData\tmpFormatCard_B1A.fmt" -T -S' + @@SERVERNAME + ''; 

And then at least bcp will parse correctly and you'll see the proper output.

Answer 3

I'm not sure but is it as simple as adding two single quotes to your statement around B1A?

Yours:

DECLARE @SQL VARCHAR(8000); 
SELECT @SQL = 'bcp "EXEC ispsSelectEmptyAsNull ''B1A'';" 
 queryout "F:\aaData\IPACostData\R15TData2円BSHAEOS_B1A_20121120.txt" 
 -f "F:\aaData\IPACostData\R15TData\tmpFormatCard_B1A.fmt" -T -S' + 
@@SERVERNAME + ''; 
EXEC xp_cmdshell @SQL;
GO

Mine:

DECLARE @SQL VARCHAR(8000); 
SELECT @SQL = 'bcp "EXEC ispsSelectEmptyAsNull '''B1A''';" 
 queryout "F:\aaData\IPACostData\R15TData2円BSHAEOS_B1A_20121120.txt" 
 -f "F:\aaData\IPACostData\R15TData\tmpFormatCard_B1A.fmt" -T -S' + 
@@SERVERNAME + ''; 
EXEC xp_cmdshell @SQL;
GO

You might even need an extra two single quotes around B1A.

• John K. N.: You might even have to escape the single quote at some stage with CHAR(39) to get the proper result. Single quotes are trial and error at some point in SQL Server.