bcp Errors

Question

bcp Errors

Charlie-551146

Say Hey Kid

Points: 677
More actions
August 30, 2011 at 9:05 am

#256449

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
[font="Arial"]Thank You,
[/font]
[font="Comic Sans MS"]Charlie[/font]
[font="Arial"]Charles Eaton[/font]

Viewing 9 posts - 1 through 8 (of 8 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Charlie-551146 Say Hey Kid Points: 677 More actions · Answer 2

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

[font="Arial"]Thank You,
[/font]
[font="Comic Sans MS"]Charlie[/font]

[font="Arial"]Charles Eaton[/font]

Lowell SSC Guru Points: 323518 More actions · Answer 3

[: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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Charlie-551146 Say Hey Kid Points: 677 More actions · Answer 4

No Sybase installed.

[font="Arial"]Thank You,
[/font]
[font="Comic Sans MS"]Charlie[/font]

[font="Arial"]Charles Eaton[/font]

Lowell SSC Guru Points: 323518 More actions · Answer 5

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Charlie-551146 Say Hey Kid Points: 677 More actions · Answer 6

Got it. Someone had changed the Server. It is a dev box.

the Production box is fine. All is well.

Thank You Lowell!

[font="Arial"]Thank You,
[/font]
[font="Comic Sans MS"]Charlie[/font]

[font="Arial"]Charles Eaton[/font]

John Olynn-373146 Valued Member Points: 54 More actions · Answer 7

John Olynn-373146

Valued Member

Points: 54

April 20, 2012 at 3:28 am

#1476481

Thanks Lowell, your answer has helped me too.

kmclin Valued Member Points: 61 More actions · Answer 8

Thank Lowell! After an entire day of troubleshooting, your answer finally helped us solve the issue! 🙂