Something is wrong with a query syntax for bcp command in SQL

Question

I'm building a query for my bcp command in SQL. When executing the statement I have the following error:

Conversion failed when converting the varchar value 'SELECT 'Transaction Unique ID','Transaction Date', 'Person Unique ID' UNION ALL SELECT NULL AS 'Transaction Unique ID', CONVERT(VARCHAR(10),CONVERT(DATETIME,Date,1),101) AS ' Transaction Date', NULL AS 'Person Unique ID' FROM tblChromeRiverInitData WHERE YYYYMM = ' to data type int.

This is the syntax for my bcp command:

DECLARE @query VARCHAR(2000)
DECLARE @bcpCommand VARCHAR(1024)
DECLARE @sharedDevFolder VARCHAR(500)
DECLARE @fileName VARCHAR(200)
DECLARE @environment VARCHAR(5)
DECLARE @customerCode VARCHAR(5)
DECLARE @parserConfig VARCHAR(5)
DECLARE @bucketAssign VARCHAR(10)
DECLARE @dateFormat VARCHAR(15)
DECLARE @input VARCHAR(15)
DECLARE @RC int

SET @sharedDevFolder = '\\REMOTE_SERVER\MyDirectory\'
SET @input = 201507
SET @fileName = 'Transaction-' + 
                @environment + '-' + 
                @customerCode + '-' + 
                @parserConfig + '-' + 
                @bucketAssign + '-' + 
                @dateFormat + '.txt'

SET @query = 
    'SELECT ''Transaction Unique ID'',''Transaction Date'', ''Person Unique ID'' UNION ALL SELECT NULL AS ''Transaction Unique ID'', CONVERT(VARCHAR(10),CONVERT(DATETIME,Date,1),101) AS '' Transaction Date'', NULL AS ''Person Unique ID'' FROM tblChromeRiverInitData WHERE YYYYMM = ' + CAST(@input as VARCHAR(10))

SET @bcpCommand = 'bcp "' + @query + '" queryout "'
set @bcpCommand = @bcpCommand + @sharedDevFolder + @fileName + '" -c  -T -t^| -r\n'

EXEC @RC = master..xp_cmdshell @bcpCommand

I'm not sure what's wrong.

When executed, the following query is printed:

SELECT 'Transaction Unique ID','Transaction Date', 'Person Unique ID' UNION ALL SELECT NULL AS 'Transaction Unique ID', CONVERT(VARCHAR(10),CONVERT(DATETIME,Date,1),101) AS 'Transaction Date', NULL AS 'Person Unique ID' FROM tblChromeRiverInitData WHERE YYYYMM = '201507'

I have been trying to solve it for sometimes already and it's really bugging me.

Can you please explain what I'm doing wrong and if possible provide me with the correct way of doing this?

On which line of this query do you get that error message? And can you verify that this is the actual query being executed (except perhaps the sharedDevFolder value), and not something you've changed for this question? For instance, can you verify that the type of @input is really a varchar and that you're really using this variable when creating @query, and that the line setting @query does not use ... ' + 201507 and you just thought that a variable looks nicer? — Lasse V. Karlsen
Just a tip, but rather than single quotes, try square brackets ([, ]) for your identifiers. — cjb110
There is no line indicator. Just bcp command is not executing — gene
It is an int, and I already changed it to CAST(@input as VARCHAR(10)) — gene

cjb110 cjb110 · Accepted Answer · 2015-07-20T13:55:05

You've got a data type mismatch going on. The + @input at the end of your query is the issue.

I'm guessing the column [YYYYMM] is a number? It should be a string/varchar.

So your line could be

declare @input as varchar
set @input = '201505'
set @query = 'SELECT ''Transaction Unique ID'',''Transaction Date''...WHERE YYYYMM = ''' + @input + ''''

if [YYYYMM] is a string, or if it's not and it's numeric

declare @input as int
set @input = 201505
set @query = 'SELECT ''Transaction Unique ID'',''Transaction Date''...WHERE YYYYMM = ' + cast(@input as varchar)

Something is wrong with a query syntax for bcp command in SQL

3 Answers