• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 754
  • Last Modified:

Outputfile to csv from sql

I am getting the error Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near 'English'.  This query runs find with just the select statement but I am trying to output it to a csv file daily.   I  would appreicate any help.  Thanks


declare @filename varchar(100)
declare @cmd varchar (1000)
set @filename='C:\testsqloutput.txt'
Set @cmd = 'osql -h -E -d healthdistrictQnA /Q "SELECT  lastname, firstname,case when bytlanguage = 2 then 'English'  when bytlanguage = 3 THEN 'Spanish' when bytlanguage = 1 THEN 'English'              
                        when bytlanguage = 4 THEN 'English'  end as 'Language', appt.phone, CONVERT(VARCHAR,APPTDATE, 101) as apptdate, time_hour, time_minute,apptreason,  name_last, name_first,appt.prprovid  FROM DDB_APPT_BASE as appt inner join ddb_pat_base as pat on appt.patid = pat.patid inner join ddb_rsc_base as rsc on appt.prprovid = rsc.urscid inner join tblpatient as p on appt.chart = p.strpatientid
WHERE CONVERT(VARCHAR,APPTDATE, 101) = CONVERT(VARCHAR,DateAdd(dd,
   CASE WHEN DATEPART(weekday, getdate()) IN ( 5,6) THEN  4 ELSE 2 END
  , GetDate()), 101) and rsc.defaultclinic = 30
ORDER BY APPTDATE DESC"  /o ' + @filename
exec master.dbo.xp_cmdshell @cmd
0
running32
Asked:
running32
  • 3
  • 3
1 Solution
 
Atlanta_MikeCommented:
You need to use two quotes around English because it is inside a string. Something like this:

declare @filename varchar(100)
declare @cmd varchar (1000)
set @filename='C:\testsqloutput.txt'
Set @cmd = 'osql -h -E -d healthdistrictQnA /Q "SELECT  lastname, firstname,case when bytlanguage = 2 then ''English''  when bytlanguage = 3 THEN ''Spanish' 'when bytlanguage = 1 THEN ''English''              
                        when bytlanguage = 4 THEN ''English''  end as ''Language'', appt.phone, CONVERT(VARCHAR,APPTDATE, 101) as apptdate, time_hour, time_minute,apptreason,  name_last, name_first,appt.prprovid  FROM DDB_APPT_BASE as appt inner join ddb_pat_base as pat on appt.patid = pat.patid inner join ddb_rsc_base as rsc on appt.prprovid = rsc.urscid inner join tblpatient as p on appt.chart = p.strpatientid
WHERE CONVERT(VARCHAR,APPTDATE, 101) = CONVERT(VARCHAR,DateAdd(dd,
   CASE WHEN DATEPART(weekday, getdate()) IN ( 5,6) THEN  4 ELSE 2 END
  , GetDate()), 101) and rsc.defaultclinic = 30
ORDER BY APPTDATE DESC"  /o ' + @filename
exec master.dbo.xp_cmdshell @cmd
0
 
running32Author Commented:
I received the error below

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near 'when bytlanguage = 1 THEN 'English'              
                        when bytlanguage = 4 THEN 'English'  end as 'Languag'.
0
 
running32Author Commented:
I found that problem but I still get the error when I use the new code you gave me.  

Msg 170, Level 15, State 1, Server BC-BIZTALK, Line 1
Line 1: Incorrect syntax near 'English'.
NULL
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Atlanta_MikeCommented:
Make sure you have two quotes where there should be one (inside the string) ... look at Spanish...
0
 
Atlanta_MikeCommented:
See the problem?

''Spanish' 'when
0
 
running32Author Commented:
Below is the code I tried to run.  Thanks

declare @filename varchar(100)
declare @cmd varchar (1000)
set @filename='C:\testsqloutput.txt'
Set @cmd = 'osql -h -E -d healthdistrictQnA /Q "SELECT  lastname, firstname,case when bytlanguage = 2 then ''English''  when bytlanguage = 3 THEN ''Spanish'' when bytlanguage = 1 THEN ''English''              
                        when bytlanguage = 4 THEN ''English''  end as ''Language'', appt.phone, CONVERT(VARCHAR,APPTDATE, 101) as apptdate, time_hour, time_minute,apptreason,  name_last, name_first,appt.prprovid  FROM DDB_APPT_BASE as appt inner join ddb_pat_base as pat on appt.patid = pat.patid inner join ddb_rsc_base as rsc on appt.prprovid = rsc.urscid inner join tblpatient as p on appt.chart = p.strpatientid
WHERE CONVERT(VARCHAR,APPTDATE, 101) = CONVERT(VARCHAR,DateAdd(dd,
   CASE WHEN DATEPART(weekday, getdate()) IN ( 5,6) THEN  4 ELSE 2 END
  , GetDate()), 101) and rsc.defaultclinic = 30
ORDER BY APPTDATE DESC"  /o ' + @filename
exec master.dbo.xp_cmdshell @cmd
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now