Query fails when executed through BCP command

• drWatson

  Grasshopper

  Points: 15

  More actions

  January 15, 2008 at 10:46 am

  #127509

  Hello,

  When I run this from MS QueryAnalyzer, it works fine, and returns the required results.

  ****************************************************************

  declare @TIMELIMIT varchar(10)

  set @TIMELIMIT = left(convert(char,DATEADD (day , -1, GETDATE()),120), 10)

  SELECT COUNT(*) as COUNTER, LEFT(CONVERT(char,F.CRT_TS,121),13) as DATE_TIME,

  ORD_TYP_CD

  FROM DIFDSB.DBO.DI_FND_ORD F with(nolock),

  DIFDSB.DBO.DI_ORD_TYP O with(nolock)

  WHERE F.CRT_TS between @TIMELIMIT and DATEADD(day,1,@TIMELIMIT)

  AND F.SND_ORD_TYP_ID = O.SND_ORD_TYP_ID

  AND F.SND_ORD_TYP_ID not in (8,9)

  GROUP BY LEFT(CONVERT(char,F.CRT_TS,121),13), ORD_TYP_CD

  ORDER BY DATE_TIME, ORD_TYP_CD

  ********************************************************************

  However, if I run this using bcp using this command:

  bcp "[queryabove]" queryout "c:\temp\output.csv" -c -t , -S [ServerName] -T

  --> fails with error SQLState = S1000 BCP Host-Files must contain at least one column.

  I am very new to BCP, and have no idea what this message means. Is it because I am using DECLARE statements? Is there anything I should do to further debug my command?

  Thanks in advance or your help.

• Loner

  SSC-Insane

  Points: 21279

  More actions

  January 15, 2008 at 11:19 am

  #768250

  You cannot declare variable within the query inside the bcp. So you need to change @Timeout to 'DATEADD..'.

• drWatson

  Grasshopper

  Points: 15

  More actions

  January 16, 2008 at 1:51 am

  #768501

  Hello,

  Thank you so much for your reply! This worked for me. I have another question though: instead of writing the whole query into the bcp command, is it possible to have bcp execute a query that is contained into a file? In that case, the bcp command would take the file path as input argument. I couldn't find this in the documentation, but this seems likely to be possible...

  Kind regards,

  jeanmichel

• Loner

  SSC-Insane

  Points: 21279

  More actions

  January 16, 2008 at 9:04 am

  #768742

  You can put the query in a view but I don't think you can use it in a text file.

• drWatson

  Grasshopper

  Points: 15

  More actions

  January 16, 2008 at 9:18 am

  #768758

  Hello,

  Thanks again for the pointers. In the meantime, I 'discovered' I could achieve the same results using osql.exe.

  ... this is to tell you how new I am to this whole thing 😉

  Thanks for your help,

  jeanmichel