SQL query error when converting CSV file

Question

This is my query:

DECLARE @cmd VARCHAR(255)

SET @cmd = 'bcp "select Cash.TransC_dtmDateTime, case
when CONVERT(varchar(10),Cash.TransC_dtmDateTime,108) <'06.00' then (convert(varchar(10),Cash.TransC_dtmDateTime -1,103))
else
(convert(varchar(10),Cash.TransC_dtmDateTime,103))
end as TransDate, tblTrans_Ticket.TransT_intNoOfSeats as NoOfSeats into #seats FROM tblTrans_Cash Cash , tblTrans_Ticket   , tblPaymentType
WHERE Cash.TransC_strType = tblPaymentType.PayType_strType and tblTrans_Ticket.TransT_lgnNumber= Cash.TransC_lgnNumber and
Cash.TransC_dtmDateTime between '2016-07-01 06:00:00.000' and '2016-08-01 06:00:00.000' and
tblTrans_Ticket.TransT_intNoOfSeats >0
and Cash.Workstation_strCode='K_DEDEDEDE'
and  tblPaymentType.PayType_strDescription in ('JUSPAY','CITRUS','FUELWALLETONLINE','CREDIT CARD')
AND ISNULL(Cash.TransC_strBKCardNo,'') not like '990022%'
order by Cash.TransC_dtmDateTime
select (convert(varchar(10),TransDate,103)),  sum(NoOfSeats)as seats from #seats  group by convert(varchar(10),TransDate,103) order by TransDate
drop table #seats" queryout "c:/tests.csv" -U sa -P test -S 192.168.57.5,1441 -T -c -t,'
Exec xp_cmdshell @cmd

When I execute it, it shows this error:

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '2016'

You need to correctly escape (nested) quotes. Also note agmed abdelqader's answer. — RobIII
– RobIII, Commented Apr 11, 2017 at 8:46
Increase the length of @cmd, its value length is more than 1000 character. — ahmed abdelqader
– ahmed abdelqader, Commented Apr 11, 2017 at 8:48
A good tip is to always format your code. Big blocks of text, like yours, are hard to read. This discourages people from helping you. EDIT A copy/paste fail led to me offering incorrect advice about table names. Sorry. — David Rushton
– David Rushton, Commented Apr 11, 2017 at 8:51

Rigerta · Accepted Answer · 2017-04-11 08:46:54Z

You need to escape the quotes.

DECLARE @cmd VARCHAR(255)
SET @cmd = 'bcp "select Cash.TransC_dtmDateTime, case
        when CONVERT(varchar(10),Cash.TransC_dtmDateTime,108) <''06.00'' 
then (convert(varchar(10),Cash.TransC_dtmDateTime -1,103))
        else
        (convert(varchar(10),Cash.TransC_dtmDateTime,103))
        end as TransDate, tblTrans_Ticket.TransT_intNoOfSeats as NoOfSeats 
into #seats FROM tblTrans_Cash Cash , tblTrans_Ticket   , tblPaymentType
        WHERE Cash.TransC_strType = tblPaymentType.PayType_strType and 
tblTrans_Ticket.TransT_lgnNumber= Cash.TransC_lgnNumber and
        Cash.TransC_dtmDateTime between ''2016-07-01 06:00:00.000'' and 
''2016-08-01 06:00:00.000'' and
        tblTrans_Ticket.TransT_intNoOfSeats >0
        and Cash.Workstation_strCode=''K_DEDEDEDE''
        and  tblPaymentType.PayType_strDescription in 
(''JUSPAY'',''CITRUS'',''FUELWALLETONLINE'',''CREDIT CARD'')
        AND ISNULL(Cash.TransC_strBKCardNo,'') not like ''990022%''
        order by Cash.TransC_dtmDateTime
        select (convert(varchar(10),TransDate,103)),  sum(NoOfSeats)as seats 
from #seats  group by convert(varchar(10),TransDate,103) order by TransDate
        drop table #seats" queryout "c:/tests.csv" -U sa -P test -S 
192.168.57.5,1441 -T -c -t,'
Exec xp_cmdshell @cmd

Sandip - Frontend Developer · Accepted Answer · 2017-04-11 08:48:21Z

1

You have to update your statement with double quote wherever it single as you are using dynamic query generation and execute it.

change your query statement like below:

Cash.TransC_dtmDateTime,108) <'06.00'

To

Cash.TransC_dtmDateTime,108) <''06.00''

And many more place, other like

between '2016-07-01 06:00:00.000' and '2016-08-01 06:00:00.000'

To

between ''2016-07-01 06:00:00.000'' and ''2016-08-01 06:00:00.000''

answered Apr 11, 2017 at 8:48

Sandip - Frontend Developer

14.7k5 gold badges38 silver badges62 bronze badges

Collectives™ on Stack Overflow

SQL query error when converting CSV file

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related