can someone please advise me on the correct syntax to bcp export executing a
stored procedures results to xml
I know it begins
bcp "exe sp_procedure" queryout
but unser on the rest of the syntax
many thanks
bcp "SELECT * FROM TABLE FOR XML RAW" queryout
c:\table.xml -Sserver -Uusername -Ppassword -c -r -t
"Matthew Butler" <matthew.butler@.srjdebt.co.uk> wrote in message
news:KuZ%i.24820$6v.5502@.newsfe2-gui.ntli.net...
> can someone please advise me on the correct syntax to bcp export executing
> a stored procedures results to xml
> I know it begins
> bcp "exe sp_procedure" queryout
> but unser on the rest of the syntax
> many thanks
>
2012年3月25日星期日
bcp to xml
can someone please advise me on the correct syntax to bcp export executing a
stored procedures results to xml
I know it begins
bcp "exe sp_procedure" queryout
but unser on the rest of the syntax
many thanksbcp "SELECT * FROM TABLE FOR XML RAW" queryout
c:\table.xml -Sserver -Uusername -Ppassword -c -r -t
"Matthew Butler" <matthew.butler@.srjdebt.co.uk> wrote in message
news:KuZ%i.24820$6v.5502@.newsfe2-gui.ntli.net...
> can someone please advise me on the correct syntax to bcp export executing
> a stored procedures results to xml
> I know it begins
> bcp "exe sp_procedure" queryout
> but unser on the rest of the syntax
> many thanks
>sql
stored procedures results to xml
I know it begins
bcp "exe sp_procedure" queryout
but unser on the rest of the syntax
many thanksbcp "SELECT * FROM TABLE FOR XML RAW" queryout
c:\table.xml -Sserver -Uusername -Ppassword -c -r -t
"Matthew Butler" <matthew.butler@.srjdebt.co.uk> wrote in message
news:KuZ%i.24820$6v.5502@.newsfe2-gui.ntli.net...
> can someone please advise me on the correct syntax to bcp export executing
> a stored procedures results to xml
> I know it begins
> bcp "exe sp_procedure" queryout
> but unser on the rest of the syntax
> many thanks
>sql
bcp syntax error help?
I have finally created (with your help) a stored procedure that is working and giving me the correct results,but when I have included the last part of my select statement where I am always trying to grab dates for the previous month it gives me the following syntax error "Incorrect syntax near '01'."
Here comes the precudere:
DECLARE @.returnDay int
DECLARE @.query varchar(8000)
--Looking at current date,
SELECT @.returnDay = DatePart(day,GetDate())
If @.returnDay = 8
BEGIN
SELECT @.query = 'bcp "SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.Unit sDiscarded,d.FateOfProducts,b.DateEntered,b.DateCo mpleted,b.CompiledBy FROM Ivana_test.dbo.Units b INNER JOIN Ivana_test.dbo.Hospitals a ON (a.HospitalID = b.HospitalID)INNER JOIN Ivana_test.dbo.Products c ON (b.ProductID = c.ProductID)INNER JOIN Ivana_test.dbo.FateOfProducts d ON (d.FateID = b.FateID) where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01')ORDER BY a.HospitalID" queryout c:\test.txt -c -test -Usa -Ptest'
EXEC master.dbo.xp_cmdshell @.query
EXEC master.dbo.xp_sendmail @.recipients=test@.test.com',
@.copy_recipients = test@.test.com',
@.message='Submitting BloodBank Results for the previous month.
@.subject='BloodBank results for the previous month',@.attachments = '\\test\c$\test.txt'
SELECT @.@.ERROR As ErrorNumber
END
Could somebody help me and suggest something as I am going crazy.....here...
ThanksI have finally created (with your help) a stored procedure that is working and giving me the correct results,but when I have included the last part of my select statement where I am always trying to grab dates for the previous month it gives me the following syntax error "Incorrect syntax near '01'."
Here comes the precudere:
DECLARE @.returnDay int
DECLARE @.query varchar(8000)
--Looking at current date,
SELECT @.returnDay = DatePart(day,GetDate())
If @.returnDay = 8
BEGIN
SELECT @.query = 'bcp "SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.Unit sDiscarded,d.FateOfProducts,b.DateEntered,b.DateCo mpleted,b.CompiledBy FROM Ivana_test.dbo.Units b INNER JOIN Ivana_test.dbo.Hospitals a ON (a.HospitalID = b.HospitalID)INNER JOIN Ivana_test.dbo.Products c ON (b.ProductID = c.ProductID)INNER JOIN Ivana_test.dbo.FateOfProducts d ON (d.FateID = b.FateID) where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01')ORDER BY a.HospitalID" queryout c:\test.txt -c -test -Usa -Ptest'
EXEC master.dbo.xp_cmdshell @.query
EXEC master.dbo.xp_sendmail @.recipients=test@.test.com',
@.copy_recipients = test@.test.com',
@.message='Submitting BloodBank Results for the previous month.
@.subject='BloodBank results for the previous month',@.attachments = '\\test\c$\test.txt'
SELECT @.@.ERROR As ErrorNumber
END
Could somebody help me and suggest something as I am going crazy.....here...
Thanks
I have realized how to go around this thanks to all
Here comes the precudere:
DECLARE @.returnDay int
DECLARE @.query varchar(8000)
--Looking at current date,
SELECT @.returnDay = DatePart(day,GetDate())
If @.returnDay = 8
BEGIN
SELECT @.query = 'bcp "SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.Unit sDiscarded,d.FateOfProducts,b.DateEntered,b.DateCo mpleted,b.CompiledBy FROM Ivana_test.dbo.Units b INNER JOIN Ivana_test.dbo.Hospitals a ON (a.HospitalID = b.HospitalID)INNER JOIN Ivana_test.dbo.Products c ON (b.ProductID = c.ProductID)INNER JOIN Ivana_test.dbo.FateOfProducts d ON (d.FateID = b.FateID) where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01')ORDER BY a.HospitalID" queryout c:\test.txt -c -test -Usa -Ptest'
EXEC master.dbo.xp_cmdshell @.query
EXEC master.dbo.xp_sendmail @.recipients=test@.test.com',
@.copy_recipients = test@.test.com',
@.message='Submitting BloodBank Results for the previous month.
@.subject='BloodBank results for the previous month',@.attachments = '\\test\c$\test.txt'
SELECT @.@.ERROR As ErrorNumber
END
Could somebody help me and suggest something as I am going crazy.....here...
ThanksI have finally created (with your help) a stored procedure that is working and giving me the correct results,but when I have included the last part of my select statement where I am always trying to grab dates for the previous month it gives me the following syntax error "Incorrect syntax near '01'."
Here comes the precudere:
DECLARE @.returnDay int
DECLARE @.query varchar(8000)
--Looking at current date,
SELECT @.returnDay = DatePart(day,GetDate())
If @.returnDay = 8
BEGIN
SELECT @.query = 'bcp "SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.Unit sDiscarded,d.FateOfProducts,b.DateEntered,b.DateCo mpleted,b.CompiledBy FROM Ivana_test.dbo.Units b INNER JOIN Ivana_test.dbo.Hospitals a ON (a.HospitalID = b.HospitalID)INNER JOIN Ivana_test.dbo.Products c ON (b.ProductID = c.ProductID)INNER JOIN Ivana_test.dbo.FateOfProducts d ON (d.FateID = b.FateID) where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01')ORDER BY a.HospitalID" queryout c:\test.txt -c -test -Usa -Ptest'
EXEC master.dbo.xp_cmdshell @.query
EXEC master.dbo.xp_sendmail @.recipients=test@.test.com',
@.copy_recipients = test@.test.com',
@.message='Submitting BloodBank Results for the previous month.
@.subject='BloodBank results for the previous month',@.attachments = '\\test\c$\test.txt'
SELECT @.@.ERROR As ErrorNumber
END
Could somebody help me and suggest something as I am going crazy.....here...
Thanks
I have realized how to go around this thanks to all
BCP syntax
is there anything wrong with my syntax ?
bcp AGENCY out E:\Spreadsheets\Agency.txt -c -Svicbranch1 -Usqlsvc -Pabc123
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.I can't see anything obviously wrong.
I always specify database.owner.table in the first parameter to bcp, though.|||BCP is run from a command line, rather than from Query Analyzer.|||I would suggest fully qualifing the table eg. dbname.dbo.tablename
Or the problem may be that the table you are trying to access is in a different database to the default database of the username you are using..
Just a thoughysql
bcp AGENCY out E:\Spreadsheets\Agency.txt -c -Svicbranch1 -Usqlsvc -Pabc123
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.I can't see anything obviously wrong.
I always specify database.owner.table in the first parameter to bcp, though.|||BCP is run from a command line, rather than from Query Analyzer.|||I would suggest fully qualifing the table eg. dbname.dbo.tablename
Or the problem may be that the table you are trying to access is in a different database to the default database of the username you are using..
Just a thoughysql
BCP Syntax
Please I need some help with bcp syntax. I have the default installation of SQL Express on my system. Also, I'm using windows authentication. Here are some syntax tried:
bcp database.dbo.table out "c:\table.dat" -U? -P? -c
bcp database.dbo.table out "c:\table.dat" -T -c
Do I have to be in App_data directory where my database reside.
There are 2 authentication modes that bcp supports:
a) SQL Server authentication in which case you have to specify your SQL Server login and password, say your SQL Server login ID is foo and password is bar then you should specify -Ufoo -Pbar.
b) If you can login to SQL Server using Windows authentication, then just specify -T and it will used Trusted connection.
Thanks
BCP Syntax
I am trying to use the BCP utility to copy a stored procedure out to a text
file.
1) Is there a way to include the field headers in the text file?
2) Is there a way to turn off the quotes between data?
So far, here is the syntax I have so far:
bcp "database..sp" queryout "c:\test.txt" -t, -Sserver -Uuser -Ppassword
Thank you,
JLFlemingOne option is to get the column names from the metadata & use UNION operator
to get a single resultset like:
SELECT -1 AS "sort_col",
MAX( CASE ORDINAL_POSITION WHEN 1 THEN col1 END ) AS "col1",
MAX( CASE ORDINAL_POSITION WHEN 2 THEN col2 END ) AS "col2",
..
MAX( CASE ORDINAL_POSITION WHEN n THEN coln END ) AS "coln"
FROM TABLE_NAME = 'tbl'
UNION
SELECT 0, col1, col2,... coln
FROM tbl ;
You can warp this into a view & BCP it out pretty easily, but watch out of
type mismatches with SYSNAME types. The above shows a general approach, but
you can avoid any reference to the meta-data by directly typing out your
column names like:
SELECT -1 AS "sort_col", "col1", "col2", ... "coln"
UNION
SELECT 0, col1, col2, ... coln
FROM tbl
ORDER BY "sort_col" ;
Another option is to create a ASCII file with the headers & then BCP out the
data to another file. Simply use the DOS COPY command like:
copy header.txt + body.txt data.txt
Anith
file.
1) Is there a way to include the field headers in the text file?
2) Is there a way to turn off the quotes between data?
So far, here is the syntax I have so far:
bcp "database..sp" queryout "c:\test.txt" -t, -Sserver -Uuser -Ppassword
Thank you,
JLFlemingOne option is to get the column names from the metadata & use UNION operator
to get a single resultset like:
SELECT -1 AS "sort_col",
MAX( CASE ORDINAL_POSITION WHEN 1 THEN col1 END ) AS "col1",
MAX( CASE ORDINAL_POSITION WHEN 2 THEN col2 END ) AS "col2",
..
MAX( CASE ORDINAL_POSITION WHEN n THEN coln END ) AS "coln"
FROM TABLE_NAME = 'tbl'
UNION
SELECT 0, col1, col2,... coln
FROM tbl ;
You can warp this into a view & BCP it out pretty easily, but watch out of
type mismatches with SYSNAME types. The above shows a general approach, but
you can avoid any reference to the meta-data by directly typing out your
column names like:
SELECT -1 AS "sort_col", "col1", "col2", ... "coln"
UNION
SELECT 0, col1, col2, ... coln
FROM tbl
ORDER BY "sort_col" ;
Another option is to create a ASCII file with the headers & then BCP out the
data to another file. Simply use the DOS COPY command like:
copy header.txt + body.txt data.txt
Anith
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 [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
2012年3月22日星期四
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 [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 [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
2012年3月20日星期二
bcp problem
I'm not having a syntax problem, I don't think, but the following is telling 0 rows copied, but I do know the file contains data...anyone have any ideas?
bcp "Privacy_OHM.[dbo].[Dat_up_log]" in D:\DATA\OHM\T0000001.log -T -S<servername> -fD:\DATA\OHM\Dat_log_In.fmtwhat does the fmt file look like?
and the first few rows of your .log file?|||Perhaps toss in an error file to catch the rows that bomb out?|||it was the format file
The redirected out put is essentially 1 column
The table has 3 columns and I forgot to "zero out" the column in the format file
Thanks guys...sql
bcp "Privacy_OHM.[dbo].[Dat_up_log]" in D:\DATA\OHM\T0000001.log -T -S<servername> -fD:\DATA\OHM\Dat_log_In.fmtwhat does the fmt file look like?
and the first few rows of your .log file?|||Perhaps toss in an error file to catch the rows that bomb out?|||it was the format file
The redirected out put is essentially 1 column
The table has 3 columns and I forgot to "zero out" the column in the format file
Thanks guys...sql
2012年3月8日星期四
bcp hanging in SQL2000
I am attempting a very simple bcp out to a text file in SQL 2000 for the first time. I've tried various syntax but the command consistently hangs no matter which i use. Nothing is being directed to the output log.........
"bcp rfsspd..bcp_product_view out product.out -T -c -b500 -r>>test.log"
I have no problems with this command in SQL 6.5, could it be initial setup or config of 2000 or something really daft??
Any help greatly appreciatedMake sure your SQL server can find bcp.exe
C:\Program Files\Microsoft SQL Server\80\Tools\Binn\
Try using this command, see if it makes a difference
DECLARE @.command VARCHAR(255)
SELECT @.command = 'bcp dbName.dbo.tableName in c:\yourFolderName\....\yourTextFile.txt -n -UyourUserName -PyourPassword -fc:\yourFolderName\......\yourFormatFile.fmt -ec:\yourFolderName\.....\yourErrorFile.err'
EXEC spCmdShell @.command
This uses a format file - which you'll have to creat and replace the in with out
"bcp rfsspd..bcp_product_view out product.out -T -c -b500 -r>>test.log"
I have no problems with this command in SQL 6.5, could it be initial setup or config of 2000 or something really daft??
Any help greatly appreciatedMake sure your SQL server can find bcp.exe
C:\Program Files\Microsoft SQL Server\80\Tools\Binn\
Try using this command, see if it makes a difference
DECLARE @.command VARCHAR(255)
SELECT @.command = 'bcp dbName.dbo.tableName in c:\yourFolderName\....\yourTextFile.txt -n -UyourUserName -PyourPassword -fc:\yourFolderName\......\yourFormatFile.fmt -ec:\yourFolderName\.....\yourErrorFile.err'
EXEC spCmdShell @.command
This uses a format file - which you'll have to creat and replace the in with out
2012年3月6日星期二
bcp error, Syntax Error in '-C'
Hi
When running the following on a customer system Iget an error. However
when I run it on my system it works fine. Any help would be
appreciated.
bcp MYDATABASE..MYTABLE in TABLE.dat -c -C RAW >>result.txt -Usa -P
It gives the following error:
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]
Syntax Error in '-C'.
Thanks
/PatrikHi,
What version of SQL Server/client is using from clients place. "-C"
parameter is not available in
versions before SQL 7.
Based on the error log I feel that the customer place they are using SQL
6.5. Please verify that.
--
Thanks
Hari
MCDBA
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0406240242.7aff6ec3@.posting.google.com...
> Hi
> When running the following on a customer system Iget an error. However
> when I run it on my system it works fine. Any help would be
> appreciated.
> bcp MYDATABASE..MYTABLE in TABLE.dat -c -C RAW >>result.txt -Usa -P
> It gives the following error:
> 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]
> Syntax Error in '-C'.
>
> Thanks
> /Patrik|||Hi
I can not re-create this error! Posting the version of SQL Server you are
using may help as I don't recognise some of the options that are given
and -C is not one of them!!
I would also move the output redirection to the end of the line.
John
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0406240242.7aff6ec3@.posting.google.com...
> Hi
> When running the following on a customer system Iget an error. However
> when I run it on my system it works fine. Any help would be
> appreciated.
> bcp MYDATABASE..MYTABLE in TABLE.dat -c -C RAW >>result.txt -Usa -P
> It gives the following error:
> 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]
> Syntax Error in '-C'.
>
> Thanks
> /Patrik|||Hi again and thanks for the replies.
The customer claims to have SQL Server 2000 with SP3. However I'm
thinking that they may have an old/other bcp installed which doesn't
recognize the -C option
(as we can see in the error msg, their bcp doesn't seem to support the
-C).
/Patrik
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<O57OAvdWEHA.1164@.tk2msftngp13.phx.gbl>...
> Hi
> I can not re-create this error! Posting the version of SQL Server you are
> using may help as I don't recognise some of the options that are given
> and -C is not one of them!!
> I would also move the output redirection to the end of the line.
> John
>
> "Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
> news:8d5c526d.0406240242.7aff6ec3@.posting.google.com...
> > Hi
> > When running the following on a customer system Iget an error. However
> > when I run it on my system it works fine. Any help would be
> > appreciated.
> >
> > bcp MYDATABASE..MYTABLE in TABLE.dat -c -C RAW >>result.txt -Usa -P
> >
> > It gives the following error:
> >
> > 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]
> > Syntax Error in '-C'.
> >
> >
> > Thanks
> > /Patrik|||Hi
-C would be used for backward compatibility, therefore there should not be
any need to use it!!
You may try the command
where bcp.exe
And then check size/date/file versions
John
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0406280400.79dffaad@.posting.google.com...
> Hi again and thanks for the replies.
> The customer claims to have SQL Server 2000 with SP3. However I'm
> thinking that they may have an old/other bcp installed which doesn't
> recognize the -C option
> (as we can see in the error msg, their bcp doesn't seem to support the
> -C).
> /Patrik
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:<O57OAvdWEHA.1164@.tk2msftngp13.phx.gbl>...
> > Hi
> >
> > I can not re-create this error! Posting the version of SQL Server you
are
> > using may help as I don't recognise some of the options that are given
> > and -C is not one of them!!
> >
> > I would also move the output redirection to the end of the line.
> >
> > John
> >
> >
> > "Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
> > news:8d5c526d.0406240242.7aff6ec3@.posting.google.com...
> > > Hi
> > > When running the following on a customer system Iget an error. However
> > > when I run it on my system it works fine. Any help would be
> > > appreciated.
> > >
> > > bcp MYDATABASE..MYTABLE in TABLE.dat -c -C RAW >>result.txt -Usa -P
> > >
> > > It gives the following error:
> > >
> > > 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]
> > > Syntax Error in '-C'.
> > >
> > >
> > > Thanks
> > > /Patrik
When running the following on a customer system Iget an error. However
when I run it on my system it works fine. Any help would be
appreciated.
bcp MYDATABASE..MYTABLE in TABLE.dat -c -C RAW >>result.txt -Usa -P
It gives the following error:
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]
Syntax Error in '-C'.
Thanks
/PatrikHi,
What version of SQL Server/client is using from clients place. "-C"
parameter is not available in
versions before SQL 7.
Based on the error log I feel that the customer place they are using SQL
6.5. Please verify that.
--
Thanks
Hari
MCDBA
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0406240242.7aff6ec3@.posting.google.com...
> Hi
> When running the following on a customer system Iget an error. However
> when I run it on my system it works fine. Any help would be
> appreciated.
> bcp MYDATABASE..MYTABLE in TABLE.dat -c -C RAW >>result.txt -Usa -P
> It gives the following error:
> 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]
> Syntax Error in '-C'.
>
> Thanks
> /Patrik|||Hi
I can not re-create this error! Posting the version of SQL Server you are
using may help as I don't recognise some of the options that are given
and -C is not one of them!!
I would also move the output redirection to the end of the line.
John
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0406240242.7aff6ec3@.posting.google.com...
> Hi
> When running the following on a customer system Iget an error. However
> when I run it on my system it works fine. Any help would be
> appreciated.
> bcp MYDATABASE..MYTABLE in TABLE.dat -c -C RAW >>result.txt -Usa -P
> It gives the following error:
> 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]
> Syntax Error in '-C'.
>
> Thanks
> /Patrik|||Hi again and thanks for the replies.
The customer claims to have SQL Server 2000 with SP3. However I'm
thinking that they may have an old/other bcp installed which doesn't
recognize the -C option
(as we can see in the error msg, their bcp doesn't seem to support the
-C).
/Patrik
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<O57OAvdWEHA.1164@.tk2msftngp13.phx.gbl>...
> Hi
> I can not re-create this error! Posting the version of SQL Server you are
> using may help as I don't recognise some of the options that are given
> and -C is not one of them!!
> I would also move the output redirection to the end of the line.
> John
>
> "Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
> news:8d5c526d.0406240242.7aff6ec3@.posting.google.com...
> > Hi
> > When running the following on a customer system Iget an error. However
> > when I run it on my system it works fine. Any help would be
> > appreciated.
> >
> > bcp MYDATABASE..MYTABLE in TABLE.dat -c -C RAW >>result.txt -Usa -P
> >
> > It gives the following error:
> >
> > 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]
> > Syntax Error in '-C'.
> >
> >
> > Thanks
> > /Patrik|||Hi
-C would be used for backward compatibility, therefore there should not be
any need to use it!!
You may try the command
where bcp.exe
And then check size/date/file versions
John
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0406280400.79dffaad@.posting.google.com...
> Hi again and thanks for the replies.
> The customer claims to have SQL Server 2000 with SP3. However I'm
> thinking that they may have an old/other bcp installed which doesn't
> recognize the -C option
> (as we can see in the error msg, their bcp doesn't seem to support the
> -C).
> /Patrik
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:<O57OAvdWEHA.1164@.tk2msftngp13.phx.gbl>...
> > Hi
> >
> > I can not re-create this error! Posting the version of SQL Server you
are
> > using may help as I don't recognise some of the options that are given
> > and -C is not one of them!!
> >
> > I would also move the output redirection to the end of the line.
> >
> > John
> >
> >
> > "Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
> > news:8d5c526d.0406240242.7aff6ec3@.posting.google.com...
> > > Hi
> > > When running the following on a customer system Iget an error. However
> > > when I run it on my system it works fine. Any help would be
> > > appreciated.
> > >
> > > bcp MYDATABASE..MYTABLE in TABLE.dat -c -C RAW >>result.txt -Usa -P
> > >
> > > It gives the following error:
> > >
> > > 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]
> > > Syntax Error in '-C'.
> > >
> > >
> > > Thanks
> > > /Patrik
bcp error, Syntax Error in '-C'
Hi
When running the following on a customer system Iget an error. However
when I run it on my system it works fine. Any help would be
appreciated.
bcp MYDATABASE..MYTABLE in TABLE.dat -c -C RAW >>result.txt -Usa -P
It gives the following error:
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]
Syntax Error in '-C'.
Thanks
/Patrik
Hi,
What version of SQL Server/client is using from clients place. "-C"
parameter is not available in
versions before SQL 7.
Based on the error log I feel that the customer place they are using SQL
6.5. Please verify that.
Thanks
Hari
MCDBA
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0406240242.7aff6ec3@.posting.google.c om...
> Hi
> When running the following on a customer system Iget an error. However
> when I run it on my system it works fine. Any help would be
> appreciated.
> bcp MYDATABASE..MYTABLE in TABLE.dat -c -C RAW >>result.txt -Usa -P
> It gives the following error:
> 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]
> Syntax Error in '-C'.
>
> Thanks
> /Patrik
|||Hi
I can not re-create this error! Posting the version of SQL Server you are
using may help as I don't recognise some of the options that are given
and -C is not one of them!!
I would also move the output redirection to the end of the line.
John
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0406240242.7aff6ec3@.posting.google.c om...
> Hi
> When running the following on a customer system Iget an error. However
> when I run it on my system it works fine. Any help would be
> appreciated.
> bcp MYDATABASE..MYTABLE in TABLE.dat -c -C RAW >>result.txt -Usa -P
> It gives the following error:
> 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]
> Syntax Error in '-C'.
>
> Thanks
> /Patrik
|||Hi again and thanks for the replies.
The customer claims to have SQL Server 2000 with SP3. However I'm
thinking that they may have an old/other bcp installed which doesn't
recognize the -C option
(as we can see in the error msg, their bcp doesn't seem to support the
-C).
/Patrik
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<O57OAvdWEHA.1164@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
> Hi
> I can not re-create this error! Posting the version of SQL Server you are
> using may help as I don't recognise some of the options that are given
> and -C is not one of them!!
> I would also move the output redirection to the end of the line.
> John
>
> "Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
> news:8d5c526d.0406240242.7aff6ec3@.posting.google.c om...
|||Hi
-C would be used for backward compatibility, therefore there should not be
any need to use it!!
You may try the command
where bcp.exe
And then check size/date/file versions
John
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0406280400.79dffaad@.posting.google.c om...
> Hi again and thanks for the replies.
> The customer claims to have SQL Server 2000 with SP3. However I'm
> thinking that they may have an old/other bcp installed which doesn't
> recognize the -C option
> (as we can see in the error msg, their bcp doesn't seem to support the
> -C).
> /Patrik
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:<O57OAvdWEHA.1164@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
are[vbcol=seagreen]
|||Hi
-C would be used for backward compatibility, therefore there should not be
any need to use it!!
You may try the command
where bcp.exe
And then check size/date/file versions
John
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0406280400.79dffaad@.posting.google.c om...
> Hi again and thanks for the replies.
> The customer claims to have SQL Server 2000 with SP3. However I'm
> thinking that they may have an old/other bcp installed which doesn't
> recognize the -C option
> (as we can see in the error msg, their bcp doesn't seem to support the
> -C).
> /Patrik
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:<O57OAvdWEHA.1164@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
are[vbcol=seagreen]
When running the following on a customer system Iget an error. However
when I run it on my system it works fine. Any help would be
appreciated.
bcp MYDATABASE..MYTABLE in TABLE.dat -c -C RAW >>result.txt -Usa -P
It gives the following error:
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]
Syntax Error in '-C'.
Thanks
/Patrik
Hi,
What version of SQL Server/client is using from clients place. "-C"
parameter is not available in
versions before SQL 7.
Based on the error log I feel that the customer place they are using SQL
6.5. Please verify that.
Thanks
Hari
MCDBA
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0406240242.7aff6ec3@.posting.google.c om...
> Hi
> When running the following on a customer system Iget an error. However
> when I run it on my system it works fine. Any help would be
> appreciated.
> bcp MYDATABASE..MYTABLE in TABLE.dat -c -C RAW >>result.txt -Usa -P
> It gives the following error:
> 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]
> Syntax Error in '-C'.
>
> Thanks
> /Patrik
|||Hi
I can not re-create this error! Posting the version of SQL Server you are
using may help as I don't recognise some of the options that are given
and -C is not one of them!!
I would also move the output redirection to the end of the line.
John
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0406240242.7aff6ec3@.posting.google.c om...
> Hi
> When running the following on a customer system Iget an error. However
> when I run it on my system it works fine. Any help would be
> appreciated.
> bcp MYDATABASE..MYTABLE in TABLE.dat -c -C RAW >>result.txt -Usa -P
> It gives the following error:
> 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]
> Syntax Error in '-C'.
>
> Thanks
> /Patrik
|||Hi again and thanks for the replies.
The customer claims to have SQL Server 2000 with SP3. However I'm
thinking that they may have an old/other bcp installed which doesn't
recognize the -C option
(as we can see in the error msg, their bcp doesn't seem to support the
-C).
/Patrik
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<O57OAvdWEHA.1164@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
> Hi
> I can not re-create this error! Posting the version of SQL Server you are
> using may help as I don't recognise some of the options that are given
> and -C is not one of them!!
> I would also move the output redirection to the end of the line.
> John
>
> "Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
> news:8d5c526d.0406240242.7aff6ec3@.posting.google.c om...
|||Hi
-C would be used for backward compatibility, therefore there should not be
any need to use it!!
You may try the command
where bcp.exe
And then check size/date/file versions
John
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0406280400.79dffaad@.posting.google.c om...
> Hi again and thanks for the replies.
> The customer claims to have SQL Server 2000 with SP3. However I'm
> thinking that they may have an old/other bcp installed which doesn't
> recognize the -C option
> (as we can see in the error msg, their bcp doesn't seem to support the
> -C).
> /Patrik
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:<O57OAvdWEHA.1164@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
are[vbcol=seagreen]
|||Hi
-C would be used for backward compatibility, therefore there should not be
any need to use it!!
You may try the command
where bcp.exe
And then check size/date/file versions
John
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0406280400.79dffaad@.posting.google.c om...
> Hi again and thanks for the replies.
> The customer claims to have SQL Server 2000 with SP3. However I'm
> thinking that they may have an old/other bcp installed which doesn't
> recognize the -C option
> (as we can see in the error msg, their bcp doesn't seem to support the
> -C).
> /Patrik
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:<O57OAvdWEHA.1164@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
are[vbcol=seagreen]
bcp error, Syntax Error in '-C'
Hi
When running the following on a customer system Iget an error. However
when I run it on my system it works fine. Any help would be
appreciated.
bcp MYDATABASE..MYTABLE in TABLE.dat -c -C RAW >>result.txt -Usa -P
It gives the following error:
usage: bcp [[database_name.]owner.]table_name[:slice_number]
23;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]
Syntax Error in '-C'.
Thanks
/PatrikHi,
What version of SQL Server/client is using from clients place. "-C"
parameter is not available in
versions before SQL 7.
Based on the error log I feel that the customer place they are using SQL
6.5. Please verify that.
Thanks
Hari
MCDBA
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0406240242.7aff6ec3@.posting.google.com...
> Hi
> When running the following on a customer system Iget an error. However
> when I run it on my system it works fine. Any help would be
> appreciated.
> bcp MYDATABASE..MYTABLE in TABLE.dat -c -C RAW >>result.txt -Usa -P
> It gives the following error:
> usage: bcp [[database_name.]owner.]table_name[:slice_number] &
#123;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 serve
r]
> [-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]
> Syntax Error in '-C'.
>
> Thanks
> /Patrik|||Hi
I can not re-create this error! Posting the version of SQL Server you are
using may help as I don't recognise some of the options that are given
and -C is not one of them!!
I would also move the output redirection to the end of the line.
John
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0406240242.7aff6ec3@.posting.google.com...
> Hi
> When running the following on a customer system Iget an error. However
> when I run it on my system it works fine. Any help would be
> appreciated.
> bcp MYDATABASE..MYTABLE in TABLE.dat -c -C RAW >>result.txt -Usa -P
> It gives the following error:
> usage: bcp [[database_name.]owner.]table_name[:slice_number] &
#123;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 serve
r]
> [-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]
> Syntax Error in '-C'.
>
> Thanks
> /Patrik|||Hi again and thanks for the replies.
The customer claims to have SQL Server 2000 with SP3. However I'm
thinking that they may have an old/other bcp installed which doesn't
recognize the -C option
(as we can see in the error msg, their bcp doesn't seem to support the
-C).
/Patrik
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<O57OAvdWEHA.1164@.tk2msftngp1
3.phx.gbl>...[vbcol=seagreen]
> Hi
> I can not re-create this error! Posting the version of SQL Server you are
> using may help as I don't recognise some of the options that are given
> and -C is not one of them!!
> I would also move the output redirection to the end of the line.
> John
>
> "Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
> news:8d5c526d.0406240242.7aff6ec3@.posting.google.com...|||Hi
-C would be used for backward compatibility, therefore there should not be
any need to use it!!
You may try the command
where bcp.exe
And then check size/date/file versions
John
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0406280400.79dffaad@.posting.google.com...
> Hi again and thanks for the replies.
> The customer claims to have SQL Server 2000 with SP3. However I'm
> thinking that they may have an old/other bcp installed which doesn't
> recognize the -C option
> (as we can see in the error msg, their bcp doesn't seem to support the
> -C).
> /Patrik
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:<O57OAvdWEHA.1164@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
are[vbcol=seagreen]|||The command you are running is not the SQL Server bcp.exe, but the Sybase bc
p.exe. To fix the problem, specify the full path to the SQL Server version.
If you are using xp_cmdshell you may have to put the bcp command into a .B
AT file.
Hope this helps,
Bill
P.S. Check out http://www.sqleditor.com for a great SQL Server explorer.
When running the following on a customer system Iget an error. However
when I run it on my system it works fine. Any help would be
appreciated.
bcp MYDATABASE..MYTABLE in TABLE.dat -c -C RAW >>result.txt -Usa -P
It gives the following error:
usage: bcp [[database_name.]owner.]table_name[:slice_number]
23;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]
Syntax Error in '-C'.
Thanks
/PatrikHi,
What version of SQL Server/client is using from clients place. "-C"
parameter is not available in
versions before SQL 7.
Based on the error log I feel that the customer place they are using SQL
6.5. Please verify that.
Thanks
Hari
MCDBA
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0406240242.7aff6ec3@.posting.google.com...
> Hi
> When running the following on a customer system Iget an error. However
> when I run it on my system it works fine. Any help would be
> appreciated.
> bcp MYDATABASE..MYTABLE in TABLE.dat -c -C RAW >>result.txt -Usa -P
> It gives the following error:
> usage: bcp [[database_name.]owner.]table_name[:slice_number] &
#123;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 serve
r]
> [-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]
> Syntax Error in '-C'.
>
> Thanks
> /Patrik|||Hi
I can not re-create this error! Posting the version of SQL Server you are
using may help as I don't recognise some of the options that are given
and -C is not one of them!!
I would also move the output redirection to the end of the line.
John
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0406240242.7aff6ec3@.posting.google.com...
> Hi
> When running the following on a customer system Iget an error. However
> when I run it on my system it works fine. Any help would be
> appreciated.
> bcp MYDATABASE..MYTABLE in TABLE.dat -c -C RAW >>result.txt -Usa -P
> It gives the following error:
> usage: bcp [[database_name.]owner.]table_name[:slice_number] &
#123;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 serve
r]
> [-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]
> Syntax Error in '-C'.
>
> Thanks
> /Patrik|||Hi again and thanks for the replies.
The customer claims to have SQL Server 2000 with SP3. However I'm
thinking that they may have an old/other bcp installed which doesn't
recognize the -C option
(as we can see in the error msg, their bcp doesn't seem to support the
-C).
/Patrik
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<O57OAvdWEHA.1164@.tk2msftngp1
3.phx.gbl>...[vbcol=seagreen]
> Hi
> I can not re-create this error! Posting the version of SQL Server you are
> using may help as I don't recognise some of the options that are given
> and -C is not one of them!!
> I would also move the output redirection to the end of the line.
> John
>
> "Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
> news:8d5c526d.0406240242.7aff6ec3@.posting.google.com...|||Hi
-C would be used for backward compatibility, therefore there should not be
any need to use it!!
You may try the command
where bcp.exe
And then check size/date/file versions
John
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0406280400.79dffaad@.posting.google.com...
> Hi again and thanks for the replies.
> The customer claims to have SQL Server 2000 with SP3. However I'm
> thinking that they may have an old/other bcp installed which doesn't
> recognize the -C option
> (as we can see in the error msg, their bcp doesn't seem to support the
> -C).
> /Patrik
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:<O57OAvdWEHA.1164@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
are[vbcol=seagreen]|||The command you are running is not the SQL Server bcp.exe, but the Sybase bc
p.exe. To fix the problem, specify the full path to the SQL Server version.
If you are using xp_cmdshell you may have to put the bcp command into a .B
AT file.
Hope this helps,
Bill
P.S. Check out http://www.sqleditor.com for a great SQL Server explorer.
2012年2月25日星期六
BCP data import
Anyone knows what is the syntax for BCP import for a comma delimited and
double quote text qualifier text file ? I have tried almost all the options
but still can't figure it out. I keep getting different error messages.
T.I.A
Hi
You can use BCP with the format option to get a format file that you can
change. Alternatively look at using DTS/SSIS see
http://www.sqldts.com/default.aspx?246
John
"DXC" wrote:
[vbcol=seagreen]
> Format file for 221 text files !!!!!
> "John Bell" wrote:
|||Format file for 221 text files !!!!!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> See Erlands post http://tinyurl.com/yf6z2u
> John
> "DXC" wrote:
|||Hi
See Erlands post http://tinyurl.com/yf6z2u
John
"DXC" wrote:
> Anyone knows what is the syntax for BCP import for a comma delimited and
> double quote text qualifier text file ? I have tried almost all the options
> but still can't figure it out. I keep getting different error messages.
> T.I.A
double quote text qualifier text file ? I have tried almost all the options
but still can't figure it out. I keep getting different error messages.
T.I.A
Hi
You can use BCP with the format option to get a format file that you can
change. Alternatively look at using DTS/SSIS see
http://www.sqldts.com/default.aspx?246
John
"DXC" wrote:
[vbcol=seagreen]
> Format file for 221 text files !!!!!
> "John Bell" wrote:
|||Format file for 221 text files !!!!!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> See Erlands post http://tinyurl.com/yf6z2u
> John
> "DXC" wrote:
|||Hi
See Erlands post http://tinyurl.com/yf6z2u
John
"DXC" wrote:
> Anyone knows what is the syntax for BCP import for a comma delimited and
> double quote text qualifier text file ? I have tried almost all the options
> but still can't figure it out. I keep getting different error messages.
> T.I.A
BCP data import
Anyone knows what is the syntax for BCP import for a comma delimited and
double quote text qualifier text file ? I have tried almost all the options
but still can't figure it out. I keep getting different error messages.
T.I.AHi
You can use BCP with the format option to get a format file that you can
change. Alternatively look at using DTS/SSIS see
http://www.sqldts.com/default.aspx?246
John
"DXC" wrote:
[vbcol=seagreen]
> Format file for 221 text files !!!!!
> "John Bell" wrote:
>|||Format file for 221 text files !!!!!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> See Erlands post http://tinyurl.com/yf6z2u
> John
> "DXC" wrote:
>|||Hi
See Erlands post http://tinyurl.com/yf6z2u
John
"DXC" wrote:
> Anyone knows what is the syntax for BCP import for a comma delimited and
> double quote text qualifier text file ? I have tried almost all the option
s
> but still can't figure it out. I keep getting different error messages.
> T.I.A
double quote text qualifier text file ? I have tried almost all the options
but still can't figure it out. I keep getting different error messages.
T.I.AHi
You can use BCP with the format option to get a format file that you can
change. Alternatively look at using DTS/SSIS see
http://www.sqldts.com/default.aspx?246
John
"DXC" wrote:
[vbcol=seagreen]
> Format file for 221 text files !!!!!
> "John Bell" wrote:
>|||Format file for 221 text files !!!!!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> See Erlands post http://tinyurl.com/yf6z2u
> John
> "DXC" wrote:
>|||Hi
See Erlands post http://tinyurl.com/yf6z2u
John
"DXC" wrote:
> Anyone knows what is the syntax for BCP import for a comma delimited and
> double quote text qualifier text file ? I have tried almost all the option
s
> but still can't figure it out. I keep getting different error messages.
> T.I.A
2012年2月23日星期四
BCP and keyword column names
When trying to import data from a text file using BCP (where one of the
column names of the table is the keyword 'Key'), I get a syntax error which
is to be expected. For keyword table names, you use [tablename] in the bcp
command. Is there a special command that I can use that will do something
similar for keyword column names?You can specify the '-q' BCP parameter to SET QUOTED_IDENTIFIER ON.
Also, it's a good practice to avoid using reserved words as column and
object names.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hielien" <Hielien@.discussions.microsoft.com> wrote in message
news:0B8AE489-061B-4F4E-B954-FF4439A1D5AD@.microsoft.com...
> When trying to import data from a text file using BCP (where one of the
> column names of the table is the keyword 'Key'), I get a syntax error
> which
> is to be expected. For keyword table names, you use [tablename] in the
> bcp
> command. Is there a special command that I can use that will do something
> similar for keyword column names?|||Thanks!! Can't believe it is that easy... If I had a choice, I wouldn't use
reserved words. Unfortunately the database that I have to connect to,
already have such columns names... :)
"Dan Guzman" wrote:
> You can specify the '-q' BCP parameter to SET QUOTED_IDENTIFIER ON.
> Also, it's a good practice to avoid using reserved words as column and
> object names.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hielien" <Hielien@.discussions.microsoft.com> wrote in message
> news:0B8AE489-061B-4F4E-B954-FF4439A1D5AD@.microsoft.com...
> > When trying to import data from a text file using BCP (where one of the
> > column names of the table is the keyword 'Key'), I get a syntax error
> > which
> > is to be expected. For keyword table names, you use [tablename] in the
> > bcp
> > command. Is there a special command that I can use that will do something
> > similar for keyword column names?
>
>
column names of the table is the keyword 'Key'), I get a syntax error which
is to be expected. For keyword table names, you use [tablename] in the bcp
command. Is there a special command that I can use that will do something
similar for keyword column names?You can specify the '-q' BCP parameter to SET QUOTED_IDENTIFIER ON.
Also, it's a good practice to avoid using reserved words as column and
object names.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hielien" <Hielien@.discussions.microsoft.com> wrote in message
news:0B8AE489-061B-4F4E-B954-FF4439A1D5AD@.microsoft.com...
> When trying to import data from a text file using BCP (where one of the
> column names of the table is the keyword 'Key'), I get a syntax error
> which
> is to be expected. For keyword table names, you use [tablename] in the
> bcp
> command. Is there a special command that I can use that will do something
> similar for keyword column names?|||Thanks!! Can't believe it is that easy... If I had a choice, I wouldn't use
reserved words. Unfortunately the database that I have to connect to,
already have such columns names... :)
"Dan Guzman" wrote:
> You can specify the '-q' BCP parameter to SET QUOTED_IDENTIFIER ON.
> Also, it's a good practice to avoid using reserved words as column and
> object names.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hielien" <Hielien@.discussions.microsoft.com> wrote in message
> news:0B8AE489-061B-4F4E-B954-FF4439A1D5AD@.microsoft.com...
> > When trying to import data from a text file using BCP (where one of the
> > column names of the table is the keyword 'Key'), I get a syntax error
> > which
> > is to be expected. For keyword table names, you use [tablename] in the
> > bcp
> > command. Is there a special command that I can use that will do something
> > similar for keyword column names?
>
>
BCP and keyword column names
When trying to import data from a text file using BCP (where one of the
column names of the table is the keyword 'Key'), I get a syntax error which
is to be expected. For keyword table names, you use [tablename] in the bcp
command. Is there a special command that I can use that will do something
similar for keyword column names?
You can specify the '-q' BCP parameter to SET QUOTED_IDENTIFIER ON.
Also, it's a good practice to avoid using reserved words as column and
object names.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hielien" <Hielien@.discussions.microsoft.com> wrote in message
news:0B8AE489-061B-4F4E-B954-FF4439A1D5AD@.microsoft.com...
> When trying to import data from a text file using BCP (where one of the
> column names of the table is the keyword 'Key'), I get a syntax error
> which
> is to be expected. For keyword table names, you use [tablename] in the
> bcp
> command. Is there a special command that I can use that will do something
> similar for keyword column names?
|||Thanks!! Can't believe it is that easy... If I had a choice, I wouldn't use
reserved words. Unfortunately the database that I have to connect to,
already have such columns names...
"Dan Guzman" wrote:
> You can specify the '-q' BCP parameter to SET QUOTED_IDENTIFIER ON.
> Also, it's a good practice to avoid using reserved words as column and
> object names.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hielien" <Hielien@.discussions.microsoft.com> wrote in message
> news:0B8AE489-061B-4F4E-B954-FF4439A1D5AD@.microsoft.com...
>
>
column names of the table is the keyword 'Key'), I get a syntax error which
is to be expected. For keyword table names, you use [tablename] in the bcp
command. Is there a special command that I can use that will do something
similar for keyword column names?
You can specify the '-q' BCP parameter to SET QUOTED_IDENTIFIER ON.
Also, it's a good practice to avoid using reserved words as column and
object names.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hielien" <Hielien@.discussions.microsoft.com> wrote in message
news:0B8AE489-061B-4F4E-B954-FF4439A1D5AD@.microsoft.com...
> When trying to import data from a text file using BCP (where one of the
> column names of the table is the keyword 'Key'), I get a syntax error
> which
> is to be expected. For keyword table names, you use [tablename] in the
> bcp
> command. Is there a special command that I can use that will do something
> similar for keyword column names?
|||Thanks!! Can't believe it is that easy... If I had a choice, I wouldn't use
reserved words. Unfortunately the database that I have to connect to,
already have such columns names...
"Dan Guzman" wrote:
> You can specify the '-q' BCP parameter to SET QUOTED_IDENTIFIER ON.
> Also, it's a good practice to avoid using reserved words as column and
> object names.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hielien" <Hielien@.discussions.microsoft.com> wrote in message
> news:0B8AE489-061B-4F4E-B954-FF4439A1D5AD@.microsoft.com...
>
>
Bcp and column delimiters
Where can I get the complet info on Bcp parameters ?
I want to export a table with "$" delimiting columns
what would be the syntax of the bcp command ?You need to use a format file...
Do you know what one looks like?
What version of SQL Server?|||Brett,
If he were executing bcp through query analyzer could he not just use the -t field terminator?
e.g.
bcp database.dbo.tablename out -c -t'$' -r'\n' -Uusername -Ppassword
-Sservername|||Originally posted by rocket39
Brett,
If he were executing bcp through query analyzer could he not just use the -t field terminator?
e.g.
bcp database.dbo.tablename out -c -t'$' -r'\n' -Uusername -Ppassword
-Sservername
oops mispoke, bcp is not run through query analyzer...and documentation for all the of the flags for bcp can be found in BOL by searching on bcp.|||Been working with mainframe flatfiles too long..
And who says you can't do it out of QA?
(Still need to tweak the paramters I'm afraid)
USE Northwind
GO
DECLARE @.cmd varchar(8000), @.username varchar(30), @.password varchar(30), @.File varchar(255), @.Table sysname
SELECT @.username = 'sa', @.password = 'xxx', @.Table = 'Orders', @.File = 'D:\Tax\Order.dat'
SET @.cmd = 'bcp '+ db_name() + '.dbo.' + @.Table + ' out '
+ @.File
+ ' -c -t'
+ '''' + '$' + ''''
+ '-r'
+ '''' + '\nl' + ''''
+ '-U' + @.username +' -P' + @.password +' -S' + @.@.servername
EXEC master..xp_cmdshell @.cmd|||Thanks to both of you,
that will work great I think !
Hey, look I've got a OneStar promotion ! :))|||Originally posted by Karolyn
Hey, look I've got a OneStar promotion ! :))
Who da man...uhh...woman(?)
You go (fill in gender)!
Lots of great SQL stuff out here...
check out..
http://www.sqlteam.com/Default.asp
I want to export a table with "$" delimiting columns
what would be the syntax of the bcp command ?You need to use a format file...
Do you know what one looks like?
What version of SQL Server?|||Brett,
If he were executing bcp through query analyzer could he not just use the -t field terminator?
e.g.
bcp database.dbo.tablename out -c -t'$' -r'\n' -Uusername -Ppassword
-Sservername|||Originally posted by rocket39
Brett,
If he were executing bcp through query analyzer could he not just use the -t field terminator?
e.g.
bcp database.dbo.tablename out -c -t'$' -r'\n' -Uusername -Ppassword
-Sservername
oops mispoke, bcp is not run through query analyzer...and documentation for all the of the flags for bcp can be found in BOL by searching on bcp.|||Been working with mainframe flatfiles too long..
And who says you can't do it out of QA?
(Still need to tweak the paramters I'm afraid)
USE Northwind
GO
DECLARE @.cmd varchar(8000), @.username varchar(30), @.password varchar(30), @.File varchar(255), @.Table sysname
SELECT @.username = 'sa', @.password = 'xxx', @.Table = 'Orders', @.File = 'D:\Tax\Order.dat'
SET @.cmd = 'bcp '+ db_name() + '.dbo.' + @.Table + ' out '
+ @.File
+ ' -c -t'
+ '''' + '$' + ''''
+ '-r'
+ '''' + '\nl' + ''''
+ '-U' + @.username +' -P' + @.password +' -S' + @.@.servername
EXEC master..xp_cmdshell @.cmd|||Thanks to both of you,
that will work great I think !
Hey, look I've got a OneStar promotion ! :))|||Originally posted by Karolyn
Hey, look I've got a OneStar promotion ! :))
Who da man...uhh...woman(?)
You go (fill in gender)!
Lots of great SQL stuff out here...
check out..
http://www.sqlteam.com/Default.asp
2012年2月18日星期六
BCP - File generate and name HELP
Hi All
I am trying to generate and name a file with the
following syntax : but get Error: 'bcpSelect' is not
recognized as an internal or external command,
DECLARE @.A char(100)
DECLARE @.str varchar(200)
DECLARE @.B Char(100)
DECLARE @.Con varchar(500)
Select @.Con = DataCon from FileNameCreate
Select @.A = SerialNo from FileNameCreate
Select @.B = 'Select * from FileNameCreate + +'
Select @.str='bcp' + @.B + 'queryout c:\Lut' + @.A + '.txt -
c -Usa -P -SUKLUN005\UKLUS0004H'
EXEC master..XP_cmdshell @.str"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:09e701c3a606$1561a040$a301280a@.phx.gbl...
> Hi All
> I am trying to generate and name a file with the
> following syntax : but get Error: 'bcpSelect' is not
> recognized as an internal or external command,
>
> DECLARE @.A char(100)
> DECLARE @.str varchar(200)
> DECLARE @.B Char(100)
> DECLARE @.Con varchar(500)
> Select @.Con = DataCon from FileNameCreate
> Select @.A = SerialNo from FileNameCreate
> Select @.B = 'Select * from FileNameCreate + +'
> Select @.str='bcp' + @.B + 'queryout c:\Lut' + @.A + '.txt -
> c -Usa -P -SUKLUN005\UKLUS0004H'
> EXEC master..XP_cmdshell @.str
You need to leave additional spaces in your command string, and you also
need double quotes around a query used with BCP:
select @.str = 'bcp "' + @.B + '" queryout...'
If you're building dynamic strings to execute, it usually helps to 'SELECT
@.str' just before you execute it, at least during development, to make sure
that the command is being built as you expect.
Simon|||Hi Simon
I have edited the file like this: and get error: SQLState
= 37000, NativeError = 170
Error = [Microsoft][ODBC SQL Server Driver]
[SQL Server]Line 1: Incorrect syntax near 'Test2'.
Test2 is correct information from the table....
DECLARE @.A char(100)
DECLARE @.str varchar(200)
DECLARE @.B Char(100)
Select @.B = Datacon from FileNameCreate
Select @.A = SerialNo from FileNameCreate
Select @.str = 'bcp "'+ @.B +'" queryout "c:\Lut.txt" -c -
Usa -P -"SUKLUN005\UKLUS004H"'
EXEC master..XP_cmdshell @.str|||Brian,
> I have edited the file like this: and get error: SQLState
> = 37000, NativeError = 170
> Error = [Microsoft][ODBC SQL Server Driver]
> [SQL Server]Line 1: Incorrect syntax near 'Test2'.
> Test2 is correct information from the table....
> DECLARE @.A char(100)
> DECLARE @.str varchar(200)
> DECLARE @.B Char(100)
You have declare @.A and @.B as fixed character data type. Change them
to varchar(100).
Then substitute the following for the exec statement:
print @.str
--EXEC master..XP_cmdshell @.st
It will then be obvious what your problems are.
Linda|||Brian;
I'd suggest that you put a print statement after you have constructed the
dynamic SQL and comment out the EXECUTE. Run the script to see what SQL
statement you are getting. When you are satisfied with the SQL statement,
comment out the print statement and uncomment the EXECUTE statement. Often,
this is much quicker to a solution than getting feedback from others.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:003f01c3a610$76060760$a101280a@.phx.gbl...
> Hi Simon
> I have edited the file like this: and get error: SQLState
> = 37000, NativeError = 170
> Error = [Microsoft][ODBC SQL Server Driver]
> [SQL Server]Line 1: Incorrect syntax near 'Test2'.
> Test2 is correct information from the table....
> DECLARE @.A char(100)
> DECLARE @.str varchar(200)
> DECLARE @.B Char(100)
> Select @.B = Datacon from FileNameCreate
> Select @.A = SerialNo from FileNameCreate
> Select @.str = 'bcp "'+ @.B +'" queryout "c:\Lut.txt" -c -
> Usa -P -"SUKLUN005\UKLUS004H"'
> EXEC master..XP_cmdshell @.str
>|||The Print does help. However i now get the following error
Line 7: Incorrect syntax near 'C:\Lut.txt'.
I have tryed the following:
The file exists.
Using ', " and both.|||This is now corrected by 'queryout C:\Lut.txt'
-S Server name
Now i get: Line 7: Incorrect syntax near 'UKLUN005
\UKLUS004H'.
>--Original Message--
>The Print does help. However i now get the following
error
>Line 7: Incorrect syntax near 'C:\Lut.txt'.
>I have tryed the following:
>The file exists.
>Using ', " and both.
>.
>|||Thanks all
BCP is now working and i am now using it in my live
system..............
>--Original Message--
>This is now corrected by 'queryout C:\Lut.txt'
>-S Server name
>Now i get: Line 7: Incorrect syntax near 'UKLUN005
>\UKLUS004H'.
>
>>--Original Message--
>>The Print does help. However i now get the following
>error
>>Line 7: Incorrect syntax near 'C:\Lut.txt'.
>>I have tryed the following:
>>The file exists.
>>Using ', " and both.
>>.
>.
>
I am trying to generate and name a file with the
following syntax : but get Error: 'bcpSelect' is not
recognized as an internal or external command,
DECLARE @.A char(100)
DECLARE @.str varchar(200)
DECLARE @.B Char(100)
DECLARE @.Con varchar(500)
Select @.Con = DataCon from FileNameCreate
Select @.A = SerialNo from FileNameCreate
Select @.B = 'Select * from FileNameCreate + +'
Select @.str='bcp' + @.B + 'queryout c:\Lut' + @.A + '.txt -
c -Usa -P -SUKLUN005\UKLUS0004H'
EXEC master..XP_cmdshell @.str"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:09e701c3a606$1561a040$a301280a@.phx.gbl...
> Hi All
> I am trying to generate and name a file with the
> following syntax : but get Error: 'bcpSelect' is not
> recognized as an internal or external command,
>
> DECLARE @.A char(100)
> DECLARE @.str varchar(200)
> DECLARE @.B Char(100)
> DECLARE @.Con varchar(500)
> Select @.Con = DataCon from FileNameCreate
> Select @.A = SerialNo from FileNameCreate
> Select @.B = 'Select * from FileNameCreate + +'
> Select @.str='bcp' + @.B + 'queryout c:\Lut' + @.A + '.txt -
> c -Usa -P -SUKLUN005\UKLUS0004H'
> EXEC master..XP_cmdshell @.str
You need to leave additional spaces in your command string, and you also
need double quotes around a query used with BCP:
select @.str = 'bcp "' + @.B + '" queryout...'
If you're building dynamic strings to execute, it usually helps to 'SELECT
@.str' just before you execute it, at least during development, to make sure
that the command is being built as you expect.
Simon|||Hi Simon
I have edited the file like this: and get error: SQLState
= 37000, NativeError = 170
Error = [Microsoft][ODBC SQL Server Driver]
[SQL Server]Line 1: Incorrect syntax near 'Test2'.
Test2 is correct information from the table....
DECLARE @.A char(100)
DECLARE @.str varchar(200)
DECLARE @.B Char(100)
Select @.B = Datacon from FileNameCreate
Select @.A = SerialNo from FileNameCreate
Select @.str = 'bcp "'+ @.B +'" queryout "c:\Lut.txt" -c -
Usa -P -"SUKLUN005\UKLUS004H"'
EXEC master..XP_cmdshell @.str|||Brian,
> I have edited the file like this: and get error: SQLState
> = 37000, NativeError = 170
> Error = [Microsoft][ODBC SQL Server Driver]
> [SQL Server]Line 1: Incorrect syntax near 'Test2'.
> Test2 is correct information from the table....
> DECLARE @.A char(100)
> DECLARE @.str varchar(200)
> DECLARE @.B Char(100)
You have declare @.A and @.B as fixed character data type. Change them
to varchar(100).
Then substitute the following for the exec statement:
print @.str
--EXEC master..XP_cmdshell @.st
It will then be obvious what your problems are.
Linda|||Brian;
I'd suggest that you put a print statement after you have constructed the
dynamic SQL and comment out the EXECUTE. Run the script to see what SQL
statement you are getting. When you are satisfied with the SQL statement,
comment out the print statement and uncomment the EXECUTE statement. Often,
this is much quicker to a solution than getting feedback from others.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:003f01c3a610$76060760$a101280a@.phx.gbl...
> Hi Simon
> I have edited the file like this: and get error: SQLState
> = 37000, NativeError = 170
> Error = [Microsoft][ODBC SQL Server Driver]
> [SQL Server]Line 1: Incorrect syntax near 'Test2'.
> Test2 is correct information from the table....
> DECLARE @.A char(100)
> DECLARE @.str varchar(200)
> DECLARE @.B Char(100)
> Select @.B = Datacon from FileNameCreate
> Select @.A = SerialNo from FileNameCreate
> Select @.str = 'bcp "'+ @.B +'" queryout "c:\Lut.txt" -c -
> Usa -P -"SUKLUN005\UKLUS004H"'
> EXEC master..XP_cmdshell @.str
>|||The Print does help. However i now get the following error
Line 7: Incorrect syntax near 'C:\Lut.txt'.
I have tryed the following:
The file exists.
Using ', " and both.|||This is now corrected by 'queryout C:\Lut.txt'
-S Server name
Now i get: Line 7: Incorrect syntax near 'UKLUN005
\UKLUS004H'.
>--Original Message--
>The Print does help. However i now get the following
error
>Line 7: Incorrect syntax near 'C:\Lut.txt'.
>I have tryed the following:
>The file exists.
>Using ', " and both.
>.
>|||Thanks all
BCP is now working and i am now using it in my live
system..............
>--Original Message--
>This is now corrected by 'queryout C:\Lut.txt'
>-S Server name
>Now i get: Line 7: Incorrect syntax near 'UKLUN005
>\UKLUS004H'.
>
>>--Original Message--
>>The Print does help. However i now get the following
>error
>>Line 7: Incorrect syntax near 'C:\Lut.txt'.
>>I have tryed the following:
>>The file exists.
>>Using ', " and both.
>>.
>.
>
订阅：
博文 (Atom)