Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


bcp Errors


bcp Errors

Author
Message
Charlie-551146
Charlie-551146
Old Hand
Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)

Group: General Forum Members
Points: 393 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
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14933 Visits: 38925
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
Charlie-551146
Old Hand
Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)

Group: General Forum Members
Points: 393 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
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14933 Visits: 38925

[: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
Charlie-551146
Old Hand
Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)

Group: General Forum Members
Points: 393 Visits: 74
No Sybase installed.

Thank You,

Charlie

Charles Eaton
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14933 Visits: 38925
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
Charlie-551146
Old Hand
Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)

Group: General Forum Members
Points: 393 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
John Olynn-373146
John Olynn-373146
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 61
Thanks Lowell, your answer has helped me too.
kmclin
kmclin
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 0
Thank Lowell! After an entire day of troubleshooting, your answer finally helped us solve the issue! :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search