Search code examples
sql-serverbcp

Single quote issue while creating the query for BCP utility

I am stuck in bcp query string formatting issue. I am missing one quote somewhere and unable to find it out. Here is my code:

BEGIN
DECLARE @stmt_c varchar(1000);
DECLARE @Current_Database varchar(1000) = (SELECT DB_NAME());
DECLARE @YYYYMMDD varchar(1000) = (SELECT convert(varchar, GETDATE(), 112));
DECLARE @YYYYMMDDhhmmss varchar(1000) = (SELECT format(getdate(),'yyyyMMddHHmmss'));
DECLARE @location varchar(1000) = 'D:\BackUp 2\Data\';
DECLARE @filename varchar(1000) = 'TGF_'+@YYYYMMDDhhmmss+'_'+@YYYYMMDD+'_'+'TT.dat';
DECLARE @absPath varchar(1000) = @location+@filename;
DECLARE @recordCount varchar(1000) = (SELECT COUNT(1) FROM data_v);
DECLARE @totalBytes varchar(1000) = (SELECT ISNULL(SUM(LEN(result)),0) FROM data_v);
DECLARE @totalItems varchar(1000) = (SELECT COUNT(DISTINCT ITEM_ID) FROM ITEM_TX);
DECLARE @totalChgd varchar(1000) = '0.00000';
DECLARE @trailer varchar(1000) = 'SELECT ''TRAILER''+''|''+@recordCount+''|''+@totalBytes+''|''+@totalChgd+''|''+@totalItems';

SET @stmt_c=
    'BCP '+
     '"SELECT ''HEADER|AA|'+@YYYYMMDDhhmmss+'|'+@YYYYMMDD+'|1.0'' UNION ALL SELECT * FROM data_v UNION ALL '+ @trailer" '+
    'QUERYOUT "'+@absPath+'" '+
    '-c -t, -T -d ' + @Current_Database;
EXEC master.sys.xp_cmdshell @stmt_c;

END

The above code is giving me the following error:

Incorrect syntax near ' '+
    'QUERYOUT '.
Msg 105, Level 15, State 1, Line 18
Unclosed quotation mark after the character string ' '+
    '-c -t, -T -d ' + @Current_Database;
EXEC master.sys.xp_cmdshell @stmt_c;

END 
'.

I want to export result set of the following query:

SELECT 'HEADER|AA|'+@YYYYMMDDhhmmss+'|'+@YYYYMMDD+'|1.0' UNION ALL SELECT * FROM data_v UNION ALL SELECT 'TRAILER|'+@recordCount+'|'+@totalBytes+'|'+@totalChgdPrem+'|'+@totalPolicies

I am not able to append the trailer query in my code.

Solution

  • I fixed the issue.

    Commented the @trailer variable. 

    -- DECLARE @trailer varchar(1000) = 'SELECT ''TRAILER''+''|''+@recordCount+''|''+@totalBytes+''|''+@totalChgd+''|''+@totalItems';

    Modifiled the query string to:

    '"SELECT ''HEADER|AA|'+@YYYYMMDDhhmmss+'|'+@YYYYMMDD+'|1.0'' UNION ALL SELECT * FROM data_v UNION ALL SELECT ''TRAILER|'+@recordCount+'|'+@totalBytes+'|'+@totalChgd+'|'+@totalItems+'''" '