bcp Errors
-
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]
-
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 serverSET @sql = 'bcp "' + @sql + '" queryout "' + @filename + '" -w -t"|" -r"\n" -Ulowell -PNotTheRealPassword -SDEV223'EXEC Master..xp_CmdShell @sqlLowell
--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! -
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]
-
[: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! -
No Sybase installed.
[font="Arial"]Thank You,
[/font]
[font="Comic Sans MS"]Charlie[/font][font="Arial"]Charles Eaton[/font]
-
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! -
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]
-
Thanks Lowell, your answer has helped me too.
-
Thank Lowell! After an entire day of troubleshooting, your answer finally helped us solve the issue!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply