Using BCP to export to a CSV file

• robert693

  SSChasing Mays

  Points: 623

  More actions

  September 27, 2010 at 11:16 am

  #387874

  Hello,

  I am trying to export data to a CSV file. I am using the BCP command but I get an error message that says Msg 102, Level 15, State 1, Line 7 Incorrect syntax near 'QUERYOUT'. This is my query:

  BCP

  SELECT CONVERT(CHAR(8), [vw_Schedule].[App_DtTm],1) as Date, CONVERT(CHAR(8), [vw_Schedule].[App_DtTm],8) as Time, [vw_Schedule].[Activity], [vw_Schedule].[STF_INITIALS], [vw_Schedule].[Notes], [vw_Schedule].[LOC_INITIALS], (dbo.fn_ConvertTimeIntToDurationhrmin([vw_Schedule].[Duration_time]))

  FROM [MOSAIQ].[dbo].[vw_Schedule] [vw_Schedule]

  WHERE ([vw_Schedule].[App_DtTm]>={ts '2010-09-27 00:00:00'} AND [vw_Schedule].[App_DtTm]<{ts '2010-09-28 00:00:00'}) AND [vw_Schedule].[Activity]='MTG' QUERYOUT c:\scheduletext.csv -mydatabase -mypassword -c

  Is there something missing from the QUERYOUT area?

  Thank you!

• blackbird

  Ten Centuries

  Points: 1127

  More actions

  September 27, 2010 at 2:14 pm

  #1227992

  My guess is that bcp isn't liking your WHERE clause. Either the quotes are throwing it off or just in general, it doesn't like to process a query with a WHERE clause in it. Can you take the data and throw it into a temp table and query straight out of the temp table (so your bcp-related query doesn't have a where clause in it). Try that and let me know if it works.

• WayneS

  SSC Guru

  Points: 95461

  More actions

  September 27, 2010 at 2:30 pm

  #1228008

  Put your query in double-quotes. From BOL:

  Use double quotation marks around the query and single quotation marks around anything embedded in the query.

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• steve.harlington

  SSC-Addicted

  Points: 474

  More actions

  September 15, 2011 at 6:43 pm

  #1382341

  I think I am having a simular issue which I have been unable to solve so far. Any ideas?

  This works in a query window in SQL Management Studio;

  Select v.EntryValue, FirstName, GroupID From VC..UserDetails LEFT JOIN VC..UserGroup ON VC..UserDetails.UserID = VC..UserGroup.UserID

  CROSS JOIN

  (Select EntryValue From VC..VCSystemData WHERE EntryName = 'CurDBSchemaVersion') v

  While the same query fails in bcp;

  When @import_type = 'UserDetails' Then

  'exec master..xp_cmdshell ''bcp "Select v.EntryValue, FirstName, GroupID From VC..UserDetails LEFT JOIN VC..UserGroup ON VC..UserDetails.UserID = VC..UserGroup.UserID

  CROSS JOIN

  (Select EntryValue From VC..VCSystemData WHERE EntryName = ''CurDBSchemaVersion'') v" queryout "'+@file_name+'" -c -t"|" -T -S "'+@MachineName+'"'''

  Result;

  C:\VCP\VCPDataRoot\VCPRS\Exports\UserDetails_20110916_100126.txt

  (1 row(s) affected)

  (1 row(s) affected)

  (1 row(s) affected)

  exec master..xp_cmdshell 'bcp "Select v.EntryValue, FirstName, GroupID From VC..UserDetails LEFT JOIN VC..UserGroup ON VC..UserDetails.UserID = VC..UserGroup.UserID

  CROSS JOIN

  (Select EntryValue From VC..VCSystemData WHERE EntryName = 'CurDBSchemaVersion') v" queryout "C:\VCP\VCPDataRoot\VCPRS\Exports\UserDetails_20110916_100126.txt" -c -t"|" -T -S "ADE-LT-6WLK52S\XTRALIS_CYCLOPS"'

  Msg 102, Level 15, State 1, Line 3

  Incorrect syntax near 'CurDBSchemaVersion'.

  (1 row(s) affected)