bcp syntax

When I run the following from the command line (referencing SQL 2K):
bcp [cms user messaging archive].dbo.archivepurge out D:\SQLData\TEST\
archive.bcp -n -eD:\SQLData\TEST\error.txt
I get the following message:
Copy direction must be either 'in', 'out' or 'format'.
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character
type]
[-N keep non-text native] [-6 6x file format] [-q quoted
identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
Please help.
Message posted via http://www.sqlmonster.com
Bcp uses parsename() to parse for object name. It seems to have problem
parsing back bracket.
Do this instead.
bcp "cms user messaging archive"."dbo"."archivepurge" out
"D:\SQLData\TEST\archive.bcp" -n -e"D:\SQLData\TEST\error.txt" -q
-oj
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:e4ec7d6406fa4ad9955a3a244ea8b1f7@.SQLMonster.c om...
> When I run the following from the command line (referencing SQL 2K):
> bcp [cms user messaging archive].dbo.archivepurge out D:\SQLData\TEST\
> archive.bcp -n -eD:\SQLData\TEST\error.txt
> I get the following message:
> Copy direction must be either 'in', 'out' or 'format'.
> usage: bcp {dbtable | query} {in | out | queryout | format} datafile
> [-m maxerrors] [-f formatfile] [-e errfile]
> [-F firstrow] [-L lastrow] [-b batchsize]
> [-n native type] [-c character type] [-w wide character
> type]
> [-N keep non-text native] [-6 6x file format] [-q quoted
> identifier]
> [-C code page specifier] [-t field terminator] [-r row terminator]
> [-i inputfile] [-o outfile] [-a packetsize]
> [-S server name] [-U username] [-P password]
> [-T trusted connection] [-v version] [-R regional enable]
> [-k keep null values] [-E keep identity values]
> [-h "load hints"]
> Please help.
> --
> Message posted via http://www.sqlmonster.com

bcp syntax

When I run the following from the command line (referencing SQL 2K):
bcp [cms user messaging archive].dbo.archivepurge out D:\SQLData\TEST\
archive.bcp -n -eD:\SQLData\TEST\error.txt
I get the following message:
Copy direction must be either 'in', 'out' or 'format'.
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character
type]
[-N keep non-text native] [-6 6x file format] [-q quoted
identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
Please help.
--
Message posted via http://www.sqlmonster.comBcp uses parsename() to parse for object name. It seems to have problem
parsing back bracket.
Do this instead.
bcp "cms user messaging archive"."dbo"."archivepurge" out
"D:\SQLData\TEST\archive.bcp" -n -e"D:\SQLData\TEST\error.txt" -q
--
-oj
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:e4ec7d6406fa4ad9955a3a244ea8b1f7@.SQLMonster.com...
> When I run the following from the command line (referencing SQL 2K):
> bcp [cms user messaging archive].dbo.archivepurge out D:\SQLData\TEST\
> archive.bcp -n -eD:\SQLData\TEST\error.txt
> I get the following message:
> Copy direction must be either 'in', 'out' or 'format'.
> usage: bcp {dbtable | query} {in | out | queryout | format} datafile
> [-m maxerrors] [-f formatfile] [-e errfile]
> [-F firstrow] [-L lastrow] [-b batchsize]
> [-n native type] [-c character type] [-w wide character
> type]
> [-N keep non-text native] [-6 6x file format] [-q quoted
> identifier]
> [-C code page specifier] [-t field terminator] [-r row terminator]
> [-i inputfile] [-o outfile] [-a packetsize]
> [-S server name] [-U username] [-P password]
> [-T trusted connection] [-v version] [-R regional enable]
> [-k keep null values] [-E keep identity values]
> [-h "load hints"]
> Please help.
> --
> Message posted via http://www.sqlmonster.com

bcp syntax

When I run the following from the command line (referencing SQL 2K):
bcp [cms user messaging archive].dbo.archivepurge out D:\SQLData\TEST\
archive.bcp -n -eD:\SQLData\TEST\error.txt
I get the following message:
Copy direction must be either 'in', 'out' or 'format'.
usage: bcp {dbtable | query} {in | out | queryout | format} datafi
le
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide charact
er
type]
[-N keep non-text native] [-6 6x file format] [-q quoted
identifier]
[-C code page specifier] [-t field terminator] [-r row terminat
or]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional ena
ble]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
Please help.
Message posted via http://www.droptable.comBcp uses parsename() to parse for object name. It seems to have problem
parsing back bracket.
Do this instead.
bcp "cms user messaging archive"."dbo"."archivepurge" out
"D:\SQLData\TEST\archive.bcp" -n -e"D:\SQLData\TEST\error.txt" -q
-oj
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:e4ec7d6406fa4ad9955a3a244ea8b1f7@.SQ
droptable.com...
> When I run the following from the command line (referencing SQL 2K):
> bcp [cms user messaging archive].dbo.archivepurge out D:\SQLData\TEST\
> archive.bcp -n -eD:\SQLData\TEST\error.txt
> I get the following message:
> Copy direction must be either 'in', 'out' or 'format'.
> usage: bcp {dbtable | query} {in | out | queryout | format} data
file
> [-m maxerrors] [-f formatfile] [-e errfile]
> [-F firstrow] [-L lastrow] [-b batchsize]
> [-n native type] [-c character type] [-w wide chara
cter
> type]
> [-N keep non-text native] [-6 6x file format] [-q quoted
> identifier]
> [-C code page specifier] [-t field terminator] [-r row termin
ator]
> [-i inputfile] [-o outfile] [-a packetsize
]
> [-S server name] [-U username] [-P password]
> [-T trusted connection] [-v version] [-R regional e
nable]
> [-k keep null values] [-E keep identity values]
> [-h "load hints"]
> Please help.
> --
> Message posted via http://www.droptable.comsql

BCP Problem

I have some bcp archive files without the data format. Is there a way to get
the data format from the archive files.
ThanksHi
This is not possible. You may want to use DTS/Import wizard to graphically
assign data to columns. If you know your destination table maps onto the dat
a
file or have access to the source table, then you can use the format option
to create a format file from the table definition.
John
"Tom" wrote:

> I have some bcp archive files without the data format. Is there a way to g
et
> the data format from the archive files.
> Thanks|||No you cant get it from the output file but if you know what table the data
came from you can use the code below to make a format file of the table so
you can reassociate the columns with data values.
EXEC master.dbo.xp_cmdshell 'BCP dbname.dbo.tablename FORMAT -Usa -Ppass -N
-fc:\TBL_Format.fmt'
From what I have experienced with format files the column width that gets
outputted from using the format option does not always match whats in the
table. From my experience the format option outputs more width than what is
stored in the table its self.
Hope this helps.
"Tom" wrote:

> I have some bcp archive files without the data format. Is there a way to g
et
> the data format from the archive files.
> Thanks

bcp and transaction problem

Hi,
We are creating a procedure in which we use bcp
(xp_cmdshell) to archive out the data for particular
month. When I execute procedure without begin tran/commit
tran it works fine and bcp out the data from table to hard
disk. But when i try to use transaction handling using
begin tran i see it to be waiting and dbcc inputbuffer of
waiting process shows:
SET FMTONLY ON select * from db..bcptable SET FMTONLY OFF
Why sql server is starting seperate process within
procedure ...IS there any special way to handle
transaction when using bcp and xp_cmdshell in the
procedure?
This is the only procedure running on machine.
Thanks
--harvinderharvinder,
> We are creating a procedure in which we use bcp
> (xp_cmdshell) to archive out the data for particular
> month. When I execute procedure without begin tran/commit
> tran it works fine and bcp out the data from table to hard
> disk. But when i try to use transaction handling using
> begin tran i see it to be waiting and dbcc inputbuffer of
> waiting process shows:
> SET FMTONLY ON select * from db..bcptable SET FMTONLY OFF
> Why sql server is starting seperate process within
> procedure ...IS there any special way to handle
> transaction when using bcp and xp_cmdshell in the
> procedure?
Bcp is running as a separate connection, not as a part of your
transaction. This is normal. It is being blocked by the connection
that opened the transaction. You cannot wrap a call to bcp in a
transaction.
Linda