2012年3月22日星期四
BCP question
I am trying to use the BCP utility. Here is what I did. From the dos prompt
I ran
bcp HPDELGt out C:\eldorado\elbcp.txt -SMyserver -Uusername -Ppassword
which generated a bcp.fmt file
then I ran
bcp HPDELGt in C:\elbcp.txt -fC:\bcp.fmt -SMyserver -Uusername -Ppassword
But it gives me a message that 0 rows copied - below is .fmt file - the
input file is a comma separated file. I would like to do it with a fixed
length file which would have no commas. how would I do that
8.0
68
1 SQLCHAR 0 3 "," 1 PUNBR
SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 3 "," 2 GRNBR
SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 9 "," 3 ESSN
SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 2 "," 4 SEQ
SQL_Latin1_General_CP1_CI_AS
5 SQLDATETIME 0 8 "," 5
EFFDATE ""
6 SQLCHAR 0 1 "," 6 STATUS
SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 1 "," 7
SELECT1 SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 1 "," 8
SELECT2 SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 1 "," 9
SELECT3 SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 1 "," 10
SELECT4 SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 1 "," 11
SELECT5 SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 1 "," 12
SELECT6 SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 1 "," 13
SELECT7 SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 1 "," 14
SELECT8 SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 1 "," 15
SELECT9 SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 1 "," 16
SELECT10 SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 1 "," 17
SELECT11 SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 1 "," 18
SELECT12 SQL_Latin1_General_CP1_CI_AS
19 SQLCHAR 0 1 "," 19
SELECT13 SQL_Latin1_General_CP1_CI_AS
20 SQLCHAR 0 1 "," 20
SELECT14 SQL_Latin1_General_CP1_CI_AS
21 SQLCHAR 0 1 "," 21
SELECT15 SQL_Latin1_General_CP1_CI_AS
22 SQLCHAR 0 10 "," 22 BEN1
SQL_Latin1_General_CP1_CI_AS
23 SQLCHAR 0 10 "," 23 BEN2
SQL_Latin1_General_CP1_CI_AS
24 SQLCHAR 0 10 "," 24 BEN3
SQL_Latin1_General_CP1_CI_AS
25 SQLCHAR 0 10 "," 25 BEN4
SQL_Latin1_General_CP1_CI_AS
26 SQLCHAR 0 10 "," 26 BEN5
SQL_Latin1_General_CP1_CI_AS
27 SQLCHAR 0 10 "," 27 BEN6
SQL_Latin1_General_CP1_CI_AS
28 SQLCHAR 0 10 "," 28 BEN7
SQL_Latin1_General_CP1_CI_AS
29 SQLDATETIME 1 8 "," 29
TRMDATE ""
30 SQLCHAR 0 1 "," 30
PRODUCT1 SQL_Latin1_General_CP1_CI_AS
31 SQLCHAR 0 1 "," 31
PRODUCT2 SQL_Latin1_General_CP1_CI_AS
32 SQLCHAR 0 1 "," 32
PRODUCT3 SQL_Latin1_General_CP1_CI_AS
33 SQLCHAR 0 1 "," 33
PRODUCT4 SQL_Latin1_General_CP1_CI_AS
34 SQLCHAR 0 1 "," 34
PRODUCT5 SQL_Latin1_General_CP1_CI_AS
35 SQLCHAR 0 1 "," 35
PRODUCT6 SQL_Latin1_General_CP1_CI_AS
36 SQLCHAR 0 1 "," 36
PRODUCT7 SQL_Latin1_General_CP1_CI_AS
37 SQLCHAR 0 1 "," 37
PRODUCT8 SQL_Latin1_General_CP1_CI_AS
38 SQLCHAR 0 1 "," 38
PRODUCT9 SQL_Latin1_General_CP1_CI_AS
39 SQLCHAR 0 1 "," 39
PRODUCT10 SQL_Latin1_General_CP1_CI_AS
40 SQLCHAR 0 1 "," 40
PRODUCT11 SQL_Latin1_General_CP1_CI_AS
41 SQLCHAR 0 1 "," 41
PRODUCT12 SQL_Latin1_General_CP1_CI_AS
42 SQLCHAR 0 1 "," 42
PRODUCT13 SQL_Latin1_General_CP1_CI_AS
43 SQLCHAR 0 1 "," 43
PRODUCT14 SQL_Latin1_General_CP1_CI_AS
44 SQLCHAR 0 1 "," 44
PRODUCT15 SQL_Latin1_General_CP1_CI_AS
45 SQLCHAR 0 1 "," 45
COBFLAG1 SQL_Latin1_General_CP1_CI_AS
46 SQLCHAR 0 1 "," 46
COBFLAG2 SQL_Latin1_General_CP1_CI_AS
47 SQLCHAR 0 1 "," 47
COBFLAG3 SQL_Latin1_General_CP1_CI_AS
48 SQLCHAR 0 1 "," 48
COBFLAG4 SQL_Latin1_General_CP1_CI_AS
49 SQLCHAR 0 1 "," 49
COBFLAG5 SQL_Latin1_General_CP1_CI_AS
50 SQLCHAR 0 1 "," 50 PEND
SQL_Latin1_General_CP1_CI_AS
51 SQLCHAR 0 3 "," 51 PNDRS
SQL_Latin1_General_CP1_CI_AS
52 SQLCHAR 0 1 "," 52
COBTYPE1 SQL_Latin1_General_CP1_CI_AS
53 SQLCHAR 0 1 "," 53
COBTYPE2 SQL_Latin1_General_CP1_CI_AS
54 SQLCHAR 0 1 "," 54
COBTYPE3 SQL_Latin1_General_CP1_CI_AS
55 SQLCHAR 0 1 "," 55
COBTYPE4 SQL_Latin1_General_CP1_CI_AS
56 SQLCHAR 0 1 "," 56
COBTYPE5 SQL_Latin1_General_CP1_CI_AS
57 SQLCHAR 0 8 "," 57 PCP1
SQL_Latin1_General_CP1_CI_AS
58 SQLCHAR 0 8 "," 58 PCP2
SQL_Latin1_General_CP1_CI_AS
59 SQLCHAR 0 3 "," 59 COBCR1
SQL_Latin1_General_CP1_CI_AS
60 SQLCHAR 0 3 "," 60 COBCR2
SQL_Latin1_General_CP1_CI_AS
61 SQLCHAR 0 3 "," 61 COBCR3
SQL_Latin1_General_CP1_CI_AS
62 SQLCHAR 0 3 "," 62 COBCR4
SQL_Latin1_General_CP1_CI_AS
63 SQLCHAR 0 3 "," 63 COBCR5
SQL_Latin1_General_CP1_CI_AS
64 SQLCHAR 0 3 "," 64 COBLT1
SQL_Latin1_General_CP1_CI_AS
65 SQLCHAR 0 3 "," 65 COBLT2
SQL_Latin1_General_CP1_CI_AS
66 SQLCHAR 0 3 "," 66 COBLT3
SQL_Latin1_General_CP1_CI_AS
67 SQLCHAR 0 3 "," 67 COBLT4
SQL_Latin1_General_CP1_CI_AS
68 SQLCHAR 0 3 "\n" 68 COBLT5
SQL_Latin1_General_CP1_CI_ASRahul Chatterjee (rahul@.benesysinc.com) writes:
> I am trying to use the BCP utility. Here is what I did. From the dos
> prompt I ran
> bcp HPDELGt out C:\eldorado\elbcp.txt -SMyserver -Uusername -Ppassword
> which generated a bcp.fmt file
> then I ran
> bcp HPDELGt in C:\elbcp.txt -fC:\bcp.fmt -SMyserver -Uusername -Ppassword
> But it gives me a message that 0 rows copied
Maybe I'm pointing out something irrelevant, but the BCP IN does not have
the same directory for the file as the BCP out command.
> - below is .fmt file - the
> input file is a comma separated file. I would like to do it with a fixed
> length file which would have no commas. how would I do that
You replace "," with "". There are already length indicators in your
format file.
Look BCP in Books Online. At the end of that page there is a link to
a page about format files.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
BCP problems
The task i want to use bcp for is to export 2 tables from a database to ASCIIfiles to be archived. Any other tools that I could use for that?
As you have probably guessed I'm a total newbie with SQL Server so any help or ideas would be really appreciated.
Thanks!
/aI've never used 6.5 so I'm not sure of it's DTS capabilities, but in 7.0 and on exporting tables can be accomplished fairly easy in EM by right clicking on the table -> All tasks -> Export Data into a variety of formats.
Also, you can open a command window outside of EM and enter BCP statements directly. Entering BCP at the prompt should give you the format and parameters.|||Wohoo it works! I was so focused that bcp was only used in EM, but now it works. Thanks a bunch. Now I only have to figure out how to format it the way I want. :-o|||Ever look at xp_cmdshell and bcp?|||Originally posted by Brett Kaiser
Ever look at xp_cmdshell and bcp?
Only brief, is it good? Or the question I should be asking, can I use it on this crappy old NTserver?
Hmm.. it seems like bcp does not like . How can that be solved?|||If you can live with a non-standard (binary) file format, you can use -n. If you want to stay pure ASCII, use -w but be prepared for Unicode (16 bit) characters.
-PatP
2012年3月19日星期一
BCP newbie question
works. When I try it from a bat it goes into this crazy endless loop
repeating the command again and again. Any ideas what I am doing wrong?
Regards, Chris.
bcp pubs..publishers out C:\test.txt -c -U****** -P***** -S******
pause
Is your command file by chance named BCP.BAT or BCP.CMD? In that case, it's
calling itself rather than BCP.EXE. You can remedy the problem by
specifying BCP.EXE and/or using a different name for you command file. It's
a good practice to avoid naming command files the same as EXE or command
names.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris" <123@.123.com> wrote in message
news:OO3%23CBbLGHA.1124@.TK2MSFTNGP10.phx.gbl...
>I am trying to run the bcp utility. When I run it from the command prompt
>it
> works. When I try it from a bat it goes into this crazy endless loop
> repeating the command again and again. Any ideas what I am doing wrong?
> Regards, Chris.
> bcp pubs..publishers out C:\test.txt -c -U****** -P***** -S******
> pause
>
BCP newbie question
works. When I try it from a bat it goes into this crazy endless loop
repeating the command again and again. Any ideas what I am doing wrong?
Regards, Chris.
bcp pubs..publishers out C:\test.txt -c -U****** -P***** -S******
pauseIs your command file by chance named BCP.BAT or BCP.CMD? In that case, it's
calling itself rather than BCP.EXE. You can remedy the problem by
specifying BCP.EXE and/or using a different name for you command file. It's
a good practice to avoid naming command files the same as EXE or command
names.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris" <123@.123.com> wrote in message
news:OO3%23CBbLGHA.1124@.TK2MSFTNGP10.phx.gbl...
>I am trying to run the bcp utility. When I run it from the command prompt
>it
> works. When I try it from a bat it goes into this crazy endless loop
> repeating the command again and again. Any ideas what I am doing wrong?
> Regards, Chris.
> bcp pubs..publishers out C:\test.txt -c -U****** -P***** -S******
> pause
>
BCP newbie question
works. When I try it from a bat it goes into this crazy endless loop
repeating the command again and again. Any ideas what I am doing wrong?
Regards, Chris.
bcp pubs..publishers out C:\test.txt -c -U****** -P***** -S******
pauseIs your command file by chance named BCP.BAT or BCP.CMD? In that case, it's
calling itself rather than BCP.EXE. You can remedy the problem by
specifying BCP.EXE and/or using a different name for you command file. It's
a good practice to avoid naming command files the same as EXE or command
names.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris" <123@.123.com> wrote in message
news:OO3%23CBbLGHA.1124@.TK2MSFTNGP10.phx.gbl...
>I am trying to run the bcp utility. When I run it from the command prompt
>it
> works. When I try it from a bat it goes into this crazy endless loop
> repeating the command again and again. Any ideas what I am doing wrong?
> Regards, Chris.
> bcp pubs..publishers out C:\test.txt -c -U****** -P***** -S******
> pause
>
BCP in xp_cmdshell
exec xp_cmdshell 'bcp '
It gives me generic usage message like
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"]
EXCEPT in one server where I get the error below when executed through
xp_cmdshell (works fine in Command Prompt)
'bcp' is not recognized as an internal or external command,
operable program or batch file.
NULL
I am not using any account to run the SQL Server services.
Anyone, any idea?
--
Regards,
MZeeshanYou're going to need to give SQL Server more info to go on.
> exec xp_cmdshell 'bcp '
You need to specify "in","out",where the file is, etc. That's what the
generic message is telling you.
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:3980BBF1-688F-48EB-9BE3-7EBF6DFB0B4C@.microsoft.com...
> Whenever I execute 'bcp' in Command Prompt or in xp_cmdshell such as
> exec xp_cmdshell 'bcp '
> It gives me generic usage message like
> 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"]
> EXCEPT in one server where I get the error below when executed through
> xp_cmdshell (works fine in Command Prompt)
> 'bcp' is not recognized as an internal or external command,
> operable program or batch file.
> NULL
> I am not using any account to run the SQL Server services.
> Anyone, any idea?
> --
> Regards,
> MZeeshan|||I was just trying to give the simplest of the bcp options. Here, I was just
trying to explain there is some PATH issue in the shell when bcp is executed
from xp_cmdshell.
Regards,
MZeeshan
"ChrisR" wrote:
> You're going to need to give SQL Server more info to go on.
> > exec xp_cmdshell 'bcp '
> You need to specify "in","out",where the file is, etc. That's what the
> generic message is telling you.
>
> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
> news:3980BBF1-688F-48EB-9BE3-7EBF6DFB0B4C@.microsoft.com...
> > Whenever I execute 'bcp' in Command Prompt or in xp_cmdshell such as
> >
> > exec xp_cmdshell 'bcp '
> >
> > It gives me generic usage message like
> >
> > 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"]
> >
> > EXCEPT in one server where I get the error below when executed through
> > xp_cmdshell (works fine in Command Prompt)
> >
> > 'bcp' is not recognized as an internal or external command,
> > operable program or batch file.
> > NULL
> >
> > I am not using any account to run the SQL Server services.
> >
> > Anyone, any idea?
> >
> > --
> > Regards,
> > MZeeshan
>
>|||May be the binn (example: C:\Program Files\Microsoft SQL
Server\80\Tools\BINN) directory is not in the os variable "path" and
xp_cmdshell is trying to execute the cmd from another dir.
AMB
"MZeeshan" wrote:
> Whenever I execute 'bcp' in Command Prompt or in xp_cmdshell such as
> exec xp_cmdshell 'bcp '
> It gives me generic usage message like
> 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"]
> EXCEPT in one server where I get the error below when executed through
> xp_cmdshell (works fine in Command Prompt)
> 'bcp' is not recognized as an internal or external command,
> operable program or batch file.
> NULL
> I am not using any account to run the SQL Server services.
> Anyone, any idea?
> --
> Regards,
> MZeeshan|||I have checked the path is present in the PATH variable in System Variables.
Plus, I can run bcp in Command Prompt
--
Regards,
MZeeshan
"Alejandro Mesa" wrote:
> May be the binn (example: C:\Program Files\Microsoft SQL
> Server\80\Tools\BINN) directory is not in the os variable "path" and
> xp_cmdshell is trying to execute the cmd from another dir.
>
> AMB
> "MZeeshan" wrote:
> > Whenever I execute 'bcp' in Command Prompt or in xp_cmdshell such as
> >
> > exec xp_cmdshell 'bcp '
> >
> > It gives me generic usage message like
> >
> > 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"]
> >
> > EXCEPT in one server where I get the error below when executed through
> > xp_cmdshell (works fine in Command Prompt)
> >
> > 'bcp' is not recognized as an internal or external command,
> > operable program or batch file.
> > NULL
> >
> > I am not using any account to run the SQL Server services.
> >
> > Anyone, any idea?
> >
> > --
> > Regards,
> > MZeeshan|||Can you run this statement from QA?
exec master..xp_cmdshell 'path && cd'
> I have checked the path is present in the PATH variable in System Variables.
> Plus, I can run bcp in Command Prompt
How are accesing the command prompt?
AMB
"MZeeshan" wrote:
> I have checked the path is present in the PATH variable in System Variables.
> Plus, I can run bcp in Command Prompt
> --
> Regards,
> MZeeshan
>
> "Alejandro Mesa" wrote:
> > May be the binn (example: C:\Program Files\Microsoft SQL
> > Server\80\Tools\BINN) directory is not in the os variable "path" and
> > xp_cmdshell is trying to execute the cmd from another dir.
> >
> >
> > AMB
> >
> > "MZeeshan" wrote:
> >
> > > Whenever I execute 'bcp' in Command Prompt or in xp_cmdshell such as
> > >
> > > exec xp_cmdshell 'bcp '
> > >
> > > It gives me generic usage message like
> > >
> > > 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"]
> > >
> > > EXCEPT in one server where I get the error below when executed through
> > > xp_cmdshell (works fine in Command Prompt)
> > >
> > > 'bcp' is not recognized as an internal or external command,
> > > operable program or batch file.
> > > NULL
> > >
> > > I am not using any account to run the SQL Server services.
> > >
> > > Anyone, any idea?
> > >
> > > --
> > > Regards,
> > > MZeeshan|||Thanks... this indeed worked.
What I found out that due to re-installation of SQL Server, the path
'c:\program files..\binn' has been pushed to the end and it was not appearing
in the xp_cmdshell 'path'.
Once the change was made, I had to reboot the server (just merely
stopping/restarting SQL server didn't help).
It's fine now!!!
--
Regards,
MZeeshan
"Alejandro Mesa" wrote:
> Can you run this statement from QA?
> exec master..xp_cmdshell 'path && cd'
> > I have checked the path is present in the PATH variable in System Variables.
> > Plus, I can run bcp in Command Prompt
> How are accesing the command prompt?
>
> AMB
> "MZeeshan" wrote:
> > I have checked the path is present in the PATH variable in System Variables.
> > Plus, I can run bcp in Command Prompt
> >
> > --
> > Regards,
> > MZeeshan
> >
> >
> > "Alejandro Mesa" wrote:
> >
> > > May be the binn (example: C:\Program Files\Microsoft SQL
> > > Server\80\Tools\BINN) directory is not in the os variable "path" and
> > > xp_cmdshell is trying to execute the cmd from another dir.
> > >
> > >
> > > AMB
> > >
> > > "MZeeshan" wrote:
> > >
> > > > Whenever I execute 'bcp' in Command Prompt or in xp_cmdshell such as
> > > >
> > > > exec xp_cmdshell 'bcp '
> > > >
> > > > It gives me generic usage message like
> > > >
> > > > 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"]
> > > >
> > > > EXCEPT in one server where I get the error below when executed through
> > > > xp_cmdshell (works fine in Command Prompt)
> > > >
> > > > 'bcp' is not recognized as an internal or external command,
> > > > operable program or batch file.
> > > > NULL
> > > >
> > > > I am not using any account to run the SQL Server services.
> > > >
> > > > Anyone, any idea?
> > > >
> > > > --
> > > > Regards,
> > > > MZeeshan
BCP in xp_cmdshell
exec xp_cmdshell 'bcp '
It gives me generic usage message like
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 ident
ifier]
[-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"]
EXCEPT in one server where I get the error below when executed through
xp_cmdshell (works fine in Command Prompt)
'bcp' is not recognized as an internal or external command,
operable program or batch file.
NULL
I am not using any account to run the SQL Server services.
Anyone, any idea?
Regards,
MZeeshanYou're going to need to give SQL Server more info to go on.
> exec xp_cmdshell 'bcp '
You need to specify "in","out",where the file is, etc. That's what the
generic message is telling you.
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:3980BBF1-688F-48EB-9BE3-7EBF6DFB0B4C@.microsoft.com...
> Whenever I execute 'bcp' in Command Prompt or in xp_cmdshell such as
> exec xp_cmdshell 'bcp '
> It gives me generic usage message like
> 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 char
acter type]
> [-N keep non-text native] [-6 6x file format] [-q quoted id
entifier]
> [-C code page specifier] [-t field terminator] [-r row termi
nator]
> [-i inputfile] [-o outfile] [-a packetsiz
e]
> [-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"]
> EXCEPT in one server where I get the error below when executed through
> xp_cmdshell (works fine in Command Prompt)
> 'bcp' is not recognized as an internal or external command,
> operable program or batch file.
> NULL
> I am not using any account to run the SQL Server services.
> Anyone, any idea?
> --
> Regards,
> MZeeshan|||I was just trying to give the simplest of the bcp options. Here, I was just
trying to explain there is some PATH issue in the shell when bcp is executed
from xp_cmdshell.
Regards,
MZeeshan
"ChrisR" wrote:
> You're going to need to give SQL Server more info to go on.
> You need to specify "in","out",where the file is, etc. That's what the
> generic message is telling you.
>
> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
> news:3980BBF1-688F-48EB-9BE3-7EBF6DFB0B4C@.microsoft.com...
>
>|||May be the binn (example: C:\Program Files\Microsoft SQL
Server\80\Tools\BINN) directory is not in the os variable "path" and
xp_cmdshell is trying to execute the cmd from another dir.
AMB
"MZeeshan" wrote:
> Whenever I execute 'bcp' in Command Prompt or in xp_cmdshell such as
> exec xp_cmdshell 'bcp '
> It gives me generic usage message like
> usage: bcp {dbtable | query} {in | out | queryout | format} data
file
> [-m maxerrors] [-f formatfile] [-e errfile]
> [-F firstrow] [-L lastrow] [-b batchsiz
e]
> [-n native type] [-c character type] [-w wide cha
racter type]
> [-N keep non-text native] [-6 6x file format] [-q quoted i
dentifier]
> [-C code page specifier] [-t field terminator] [-r row term
inator]
> [-i inputfile] [-o outfile] [-a packetsi
ze]
> [-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"]
> EXCEPT in one server where I get the error below when executed through
> xp_cmdshell (works fine in Command Prompt)
> 'bcp' is not recognized as an internal or external command,
> operable program or batch file.
> NULL
> I am not using any account to run the SQL Server services.
> Anyone, any idea?
> --
> Regards,
> MZeeshan|||I have checked the path is present in the PATH variable in System Variables.
Plus, I can run bcp in Command Prompt
Regards,
MZeeshan
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> May be the binn (example: C:\Program Files\Microsoft SQL
> Server\80\Tools\BINN) directory is not in the os variable "path" and
> xp_cmdshell is trying to execute the cmd from another dir.
>
> AMB
> "MZeeshan" wrote:
>|||Can you run this statement from QA?
exec master..xp_cmdshell 'path && cd'
> I have checked the path is present in the PATH variable in System Variable
s.
> Plus, I can run bcp in Command Prompt
How are accesing the command prompt?
AMB
"MZeeshan" wrote:
[vbcol=seagreen]
> I have checked the path is present in the PATH variable in System Variable
s.
> Plus, I can run bcp in Command Prompt
> --
> Regards,
> MZeeshan
>
> "Alejandro Mesa" wrote:
>|||Thanks... this indeed worked.
What I found out that due to re-installation of SQL Server, the path
'c:\program files..\binn' has been pushed to the end and it was not appearin
g
in the xp_cmdshell 'path'.
Once the change was made, I had to reboot the server (just merely
stopping/restarting SQL server didn't help).
It's fine now!!!
Regards,
MZeeshan
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Can you run this statement from QA?
> exec master..xp_cmdshell 'path && cd'
>
> How are accesing the command prompt?
>
> AMB
> "MZeeshan" wrote:
>
BCP in xp_cmdshell
exec xp_cmdshell 'bcp '
It gives me generic usage message like
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"]
EXCEPT in one server where I get the error below when executed through
xp_cmdshell (works fine in Command Prompt)
'bcp' is not recognized as an internal or external command,
operable program or batch file.
NULL
I am not using any account to run the SQL Server services.
Anyone, any idea?
Regards,
MZeeshan
You're going to need to give SQL Server more info to go on.
> exec xp_cmdshell 'bcp '
You need to specify "in","out",where the file is, etc. That's what the
generic message is telling you.
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:3980BBF1-688F-48EB-9BE3-7EBF6DFB0B4C@.microsoft.com...
> Whenever I execute 'bcp' in Command Prompt or in xp_cmdshell such as
> exec xp_cmdshell 'bcp '
> It gives me generic usage message like
> 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"]
> EXCEPT in one server where I get the error below when executed through
> xp_cmdshell (works fine in Command Prompt)
> 'bcp' is not recognized as an internal or external command,
> operable program or batch file.
> NULL
> I am not using any account to run the SQL Server services.
> Anyone, any idea?
> --
> Regards,
> MZeeshan
|||I was just trying to give the simplest of the bcp options. Here, I was just
trying to explain there is some PATH issue in the shell when bcp is executed
from xp_cmdshell.
Regards,
MZeeshan
"ChrisR" wrote:
> You're going to need to give SQL Server more info to go on.
> You need to specify "in","out",where the file is, etc. That's what the
> generic message is telling you.
>
> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
> news:3980BBF1-688F-48EB-9BE3-7EBF6DFB0B4C@.microsoft.com...
>
>
|||May be the binn (example: C:\Program Files\Microsoft SQL
Server\80\Tools\BINN) directory is not in the os variable "path" and
xp_cmdshell is trying to execute the cmd from another dir.
AMB
"MZeeshan" wrote:
> Whenever I execute 'bcp' in Command Prompt or in xp_cmdshell such as
> exec xp_cmdshell 'bcp '
> It gives me generic usage message like
> 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"]
> EXCEPT in one server where I get the error below when executed through
> xp_cmdshell (works fine in Command Prompt)
> 'bcp' is not recognized as an internal or external command,
> operable program or batch file.
> NULL
> I am not using any account to run the SQL Server services.
> Anyone, any idea?
> --
> Regards,
> MZeeshan
|||I have checked the path is present in the PATH variable in System Variables.
Plus, I can run bcp in Command Prompt
Regards,
MZeeshan
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> May be the binn (example: C:\Program Files\Microsoft SQL
> Server\80\Tools\BINN) directory is not in the os variable "path" and
> xp_cmdshell is trying to execute the cmd from another dir.
>
> AMB
> "MZeeshan" wrote:
|||Can you run this statement from QA?
exec master..xp_cmdshell 'path && cd'
> I have checked the path is present in the PATH variable in System Variables.
> Plus, I can run bcp in Command Prompt
How are accesing the command prompt?
AMB
"MZeeshan" wrote:
[vbcol=seagreen]
> I have checked the path is present in the PATH variable in System Variables.
> Plus, I can run bcp in Command Prompt
> --
> Regards,
> MZeeshan
>
> "Alejandro Mesa" wrote:
|||Thanks... this indeed worked.
What I found out that due to re-installation of SQL Server, the path
'c:\program files..\binn' has been pushed to the end and it was not appearing
in the xp_cmdshell 'path'.
Once the change was made, I had to reboot the server (just merely
stopping/restarting SQL server didn't help).
It's fine now!!!
Regards,
MZeeshan
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Can you run this statement from QA?
> exec master..xp_cmdshell 'path && cd'
>
> How are accesing the command prompt?
>
> AMB
> "MZeeshan" wrote:
2012年3月11日星期日
BCP in Stored Procedure
exec master..xp_cmdshell 'bcp FeeScheduleValidation..FS_PhysicianCOSMOSSystemFSFile in C:\FeeScheduleValidationTool\Data\testfile.txt -c -t~ -r\n -S(local) -Ufeescheduleuser -Pfeescheduleuser'
Is there something special that needs to be done to allow bcp to run from a stored procedure?Execute permissions for xp_cmdshell default to members of the sysadmin fixed server role, but can be granted to other users.
Important If you choose to use a Windows NT account that is not a member of the local administrator's group for the MSSQLServer service, users who are not members of the sysadmin fixed server role cannot execute xp_cmdshell.
I think you should check the permissions of that stored procedure to make sure it allows "execute".|||Have you looked at using the Bulk Insert command in a stored proc rather than using the command shell...?
2012年3月8日星期四
bcp file format...its killing me...pleaseeeeeeeee help...
i wanted to ask on the same context of bcp...i m trying to insert two rows into a table using the bcp command prompt and the bcp file looks as follow(format.fmt):
7.0
3
1 SQLCHAR 0 4 "\"," 1 numbers
2 SQLCHAR 0 15 "\"" 2 values
3 SQLCHAR 0 2 "\r\n" 3 finish
and the input file has the following contents
1234,"other"
5678,"column"
now when i m running the command as
bcp master.dbo.two_column in c:\temp\input.txt -fc:\temp\format.fmt -Sserver -Uuser -Ppasword -T
it gives me the error as sqlstate 07009 with native state =0 and the error as = [Microsoft][SQL Native Client] Invalid descriptor index
i m using sql server 2005 (YUKON)...can anyone pleaseeeeeeeeeeeeee tell me how can i solve this problem???????so that i can insert these rows into my table two_column...
thanks ...pleaseeeeeeeeee help me...First off, line one should not be 7.0 for 2005.
For SQL 2k it.s8.0
I guess 2005, it should be 9.0, but I don't know that for sure.
Did you get the 2005 BOL Beta as well?|||Just curious, but what's the DDL for creating your table? I suspect it only has two columns.
-PatP|||Thanks for your replies...but now...i have changed the version of format file as follow...n yeah i have 2columns only:
9.0
2
1 SQLCHAR 0 4 "," 1 in_numbers SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 5 "\r\n" 2 values SQL_Latin1_General_CP1_CI_AS
and the input file is
1234,other
5678,column
but now its givin me the error as I\O error while reading BCP format file...
it was workin for sometime..it copied these two rows as well...but again its givin me this error again n again...i cant understand...wat m i suppose to do now...PLEASEEEEE TRY TO HELPPPP
thnks in advance|||Can you post the exact error ?|||hi,
the error was just that I\O error while reading the BCP file format...but now finally its over since i had to press enter twice...after the last row in the format file...just some empty rows in the format files had solved my problem
thnks.