Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » bcp Errors
Add to briefcase

bcp Errors Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Charlie-551146
Posted Tuesday, August 30, 2011 9:05 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 08, 2011 9:12 AM
Points: 378, Visits: 74
I am trying to run the following statement, but get errors. Any help would be appreciated.

DECLARE @SQL VARCHAR(4000)

SET @SQL= 'bcp "exec USP_EventXML" queryout c:\myfile.xml -w -r -t -S <servername removed> -U <loginid removed> -P <password removed>'

EXEC Master..xp_CmdShell @SQL

This is supposed to write an XML file of the table contents to c:\myfile.xml

Errors are:
Copy direction must be either 'in' or 'out'.
Syntax Error in 'queryout'.
usage: bcp [[database_name.]owner.]table_name[:slice_number] {in | out} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n] [-c] [-t field_terminator] [-r row_terminator]
[-U username] [-P password] [-I interfaces_file] [-S server]
[-a display_charset] [-q datafile_charset] [-z language] [-v]
[-A packet size] [-J client character set]
[-T text or image size] [-E] [-g id_start_value] [-N] [-X]
[-M LabelName LabelValue] [-labeled]
[-K keytab_file] [-R remote_server_principal]
[-V [security_options]] [-Z security_mechanism] [-Q]
NULL

Thank You,
Charlie

Thank You,

Charlie

Charles Eaton
Post #1167602
Lowell
Posted Tuesday, August 30, 2011 9:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:24 PM
Points: 11,605, Visits: 27,649
Charlie I only see two things.
i think the call to the procedure has to be fully qualified, i always put the servername.schemaname, but that might not be an issue.
the other thing i see that doesn't match my examples, is where i expect the -r or -t row and field terminators to be in double quotes...yours doesn't look like it did (i know the forum strips slahs-n unless you htmlize it)...but i'd expect the dbl quotes.

i just tested the example below, and it works fine.

DECLARE @sql varchar(2000),
@filename varchar(200)
SET @sql      = 'EXEC Sandbox.dbo.sp_find ''act'' '
set @filename = 'c:\data\myfile.xml '
--put it all together
--flags -w wide format
--t custom field terminator
--r custom row terminator
--U username
--Ppassword
--S server
SET @sql = 'bcp "' + @sql + '" queryout "' + @filename + '" -w -t"|" -r"\n"  -Ulowell -PNotTheRealPassword -SDEV223'
EXEC Master..xp_CmdShell @SQL

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1167650
Charlie-551146
Posted Tuesday, August 30, 2011 10:42 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 08, 2011 9:12 AM
Points: 378, Visits: 74
Thanks Lowell,
I changed the query to read like this:

DECLARE @Filename VARCHAR(100)
DECLARE @SQL VARCHAR(4000)

SET @Filename = 'C:\Event.xml'
SET @SQL = 'EXEC AllEvents.dbo.USP_EventXML'


SET @SQL= 'bcp "' + @SQL +'" queryout ' + @filename + ' -w -r "" -Uce672992 -PNot_Real -SSITW2K3SPA03 '

PRINT @SQL

EXEC Master..xp_CmdShell @SQL

Now I'm getting a syntax error in queryout

Copy direction must be either 'in' or 'out'.
Syntax Error in 'queryout'.
usage: bcp [[database_name.]owner.]table_name[:slice_number] {in | out} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n] [-c] [-t field_terminator] [-r row_terminator]
[-U username] [-P password] [-I interfaces_file] [-S server]
[-a display_charset] [-q datafile_charset] [-z language] [-v]
[-A packet size] [-J client character set]
[-T text or image size] [-E] [-g id_start_value] [-N] [-X]
[-M LabelName LabelValue] [-labeled]
[-K keytab_file] [-R remote_server_principal]
[-V [security_options]] [-Z security_mechanism] [-Q]
NULL

Thank You,

Charlie

Charles Eaton
Post #1167680
Lowell
Posted Tuesday, August 30, 2011 10:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:24 PM
Points: 11,605, Visits: 27,649

[:slice_number


THAT makes me thing you are calling the SyBase bcp.exe and Not the SQL Server's version. SQl doesn't have a slice.

do you have SyBase client tools installed?
if you change the command to" C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\bcp.exe, does it work then?

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1167693
Charlie-551146
Posted Tuesday, August 30, 2011 12:17 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 08, 2011 9:12 AM
Points: 378, Visits: 74
No Sybase installed.

Thank You,

Charlie

Charles Eaton
Post #1167752
Lowell
Posted Tuesday, August 30, 2011 12:34 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:24 PM
Points: 11,605, Visits: 27,649
Charlie-551146 (8/30/2011)
No Sybase installed.


Charlie don't hate me, i'm just the messenger, but that is defintiely the context menu from SyBase's bcp /?
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/util/@Generic__BookTextView/10086;pt=10086

try changing the path to bcp to explicitly go to teh microsoft folder, or edit your PATH environments to exclude the Sybase directory(on the server running xp_cmdshell....not your local box, unless they are the same machine).

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1167762
Charlie-551146
Posted Tuesday, August 30, 2011 1:11 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 08, 2011 9:12 AM
Points: 378, Visits: 74
Got it. Someone had changed the Server. It is a dev box.

the Production box is fine. All is well.

Thank You Lowell!

Thank You,

Charlie

Charles Eaton
Post #1167777
John Olynn-373146
Posted Friday, April 20, 2012 3:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 10, 2013 9:42 AM
Points: 2, Visits: 49
Thanks Lowell, your answer has helped me too.
Post #1286955
kmclin
Posted Wednesday, February 13, 2013 3:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 3:40 PM
Points: 1, Visits: 0
Thank Lowell! After an entire day of troubleshooting, your answer finally helped us solve the issue!
Post #1419762
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.