CODEWITHSUNDEEP
sql-serverbcp
gene
gene

Reputation: 2108

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

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?

Upvotes: 2

Views: 744

Answers (3)

gene
gene

Reputation: 2108

I fixed the issue. The problem was I did not set variables used to build the file name. When bcp was executing, the file name was not built correctly

Upvotes: 0

cjb110
cjb110

Reputation: 1471

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)

Upvotes: 1

ashim
ashim

Reputation: 776

This is due to the fact that the query 'SELECT ''Transaction...' is varchar and input is represented as INT although it is varchar. Use :

    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 = ''' + CONVERT(VARCHAR, @input) + ''''

Upvotes: 0

Related Questions