Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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\R15TData\2BSHAEOS_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.

share|improve this question

migrated from serverfault.com Nov 20 '12 at 13:47

This question came from our site for system and network administrators.

1  
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. –  hot2use Nov 20 '12 at 14:15
    
Does the BCP command run if you run it direct from the command line rather than xp_cmdshell? –  Stuart Moore Nov 20 '12 at 14:16
    
I have not tried that @StuartMoore. I will attempt to do that now... –  Killercam Nov 20 '12 at 14:19
1  
It couldn't run. That's why the bcp default message. It wasn't executed properly. –  Marian Nov 20 '12 at 14:33

3 Answers 3

up vote 2 down vote accepted

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\R15TData\2BSHAEOS_B1A_20121120.txt" '+
               '-f "F:\aaData\IPACostData\R15TData\tmpFormatCard_B1A.fmt" -T -S' + @@SERVERNAME + ''; 
EXEC xp_cmdshell @SQL;
GO
share|improve this answer
    
    
True. How did I miss that one. –  hot2use Nov 20 '12 at 14:33
    
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!! –  Killercam Nov 20 '12 at 14:51

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\R15TData\2BSHAEOS_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\R15TData\2BSHAEOS_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.

share|improve this answer

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\R15TData\2BSHAEOS_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\R15TData\2BSHAEOS_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.

share|improve this answer
    
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. –  hot2use Nov 20 '12 at 14:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.