Trying to create tab delimited TXT file from query results in SSMS 2014

Question

I am trying to export my SSMS 2014 query results to a fixed length .txt file but I am not sure how to handle a hard-coded (-1) value in one of my columns.

Any help/direction would be appreciated. This is my first attempt but I cannot get past this error. Thanks.

Here is the error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '-'.

Here is the code:

xp_cmdshell 'bcp
"SELECT DISTINCT 
    LEFT(LTRIM(RTRIM('**-1**')), 2) as 'school key', 
    LEFT(LTRIM(RTRIM('1')), 1) as 'district key', 
    LEFT(SPACE(1), 1), 
    LEFT(LTRIM(RTRIM('1')), 1) as 'district wide vendor', 
    LEFT(SPACE(1), 1), 
    LEFT(LTRIM(RTRIM(a_vendor_name)), 30) as name, 
    LEFT(LTRIM(RTRIM(v_vend_address1)), 30) as addr1, 
    LEFT(LTRIM(RTRIM(v_vend_address2)), 30) as addr2, 
    LEFT(LTRIM(RTRIM(v_vend_city)), 25) as city, 
    LEFT(LTRIM(RTRIM(v_vend_state)), 2) as st, 
    LEFT(LTRIM(RTRIM(v_vend_zip)), 10) as zip, 
    LEFT(LTRIM(RTRIM(v_contact1_phone)), 14) as phone, 
    LEFT(LTRIM(RTRIM(v_contact1_fax)), 14) as fax, 
    LEFT(LTRIM(RTRIM(v_vend_status)), 1) as 'status', 
    CASE 
        WHEN LEFT(LTRIM(RTRIM(v_1099_vendor)), 1) = 'Y' THEN '1' ELSE '0' 
    END as irs1099, 
    LEFT(SPACE(1), 1), 
    LEFT(SPACE(10), 10) as tax_id, 
    LEFT(SPACE(6), 6) as vcode, 
    LEFT(LTRIM(RTRIM(v_email_addrs)), 50) as email, 
    LEFT(SPACE(9), 9) as ssn, 
    LEFT(SPACE(20), 20) as comment, 
    LEFT(LTRIM(RTRIM(vr_vend_seq_addr1)), 30) as poaddr1, 
    LEFT(LTRIM(RTRIM(vr_vend_seq_addr2)), 30) as poaddr2, 
    LEFT(LTRIM(RTRIM(vr_vend_seq_city)), 25) as pocity, 
    LEFT(LTRIM(RTRIM(vr_vend_seq_state)), 2) as post, 
    LEFT(LTRIM(RTRIM(vr_vend_seq_zip)), 10) as pozip, 
    LEFT(LTRIM(CONCAT('MUNIS', SPACE(5))), 10) as createdBy, 
    LEFT(LTRIM(RTRIM(REPLACE(CONVERT(varchar(10), getdate(), 101), '/', ''))), 8)  as strDate,
    CASE 
        WHEN LTRIM(RTRIM(a_vendor_remit_seq)) > 0 THEN LEFT(LTRIM(RTRIM(CONCAT(a_vendor_number, a_vendor_remit_seq))), 20) ELSE LEFT(LTRIM(RTRIM(a_vendor_number)), 20) 
    END as covennbr, 
    LEFT(LTRIM(RTRIM(v_dba)), 30) as dba
FROM dbo.vend_table
WHERE v_vend_status = 'A'
AND v_general_type IN ('', '1', '2', '7', '13', '15', '19')" queryout "C:\temp\testTabDelimitedFile.txt -T'

Answer 1

You're encapsulating your xp_cmdshell command with single quotes, while also using single quotes around your values.

For example:

xp_cmdshell 'bcp
"SELECT DISTINCT 
    LEFT(LTRIM(RTRIM('**-1**')), 2) as 'school key',
                     ^      ^          ^          ^

Because of this, you'll get "incorrect syntax near '-'", because xp_cmdshell thinks that **-1** is seperate from the string you're passing through.

I would imagine that you could escape your single quotes (\').