Using BCP to export to a CSV file

Question

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!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

blackbird Ten Centuries Points: 1127 More actions · Answer 1

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: 95460 More actions · Answer 2

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 · Answer 3

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)

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)