September 27, 2010 at 11:16 am
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!
September 27, 2010 at 2:14 pm
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.
September 27, 2010 at 2:30 pm
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
September 15, 2011 at 6:43 pm
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)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy