bcp still more errors

I managed to coorect the earlier error but now it looks like this
master..xp_cmdshell 'bcp ##ttable
OUTPUT "res.xls" -C -S "server" -U "sa" -P "password"'
the new error is
NULL
Enter the file storage type of field state_id [int-null]:
(2 row(s) affected)
I have 2 fields in the temp table
Hi,
Instead of -C can use -c (small case). The -C is for code page.
-c does not prompt for each field; it uses char as the storage type
Thanks
Hari
MCDBA
"Anup" <anup_pokhrel@.hotmail.com> wrote in message
news:u8U6iVWaEHA.3016@.tk2msftngp13.phx.gbl...
> I managed to coorect the earlier error but now it looks like this
> master..xp_cmdshell 'bcp ##ttable
> OUTPUT "res.xls" -C -S "server" -U "sa" -P "password"'
> the new error is
> NULL
> Enter the file storage type of field state_id [int-null]:
> (2 row(s) affected)
> I have 2 fields in the temp table
>

bcp still more errors

I managed to coorect the earlier error but now it looks like this
master..xp_cmdshell 'bcp ##ttable
OUTPUT "res.xls" -C -S "server" -U "sa" -P "password"'
the new error is
NULL
Enter the file storage type of field state_id [int-null]:
(2 row(s) affected)
I have 2 fields in the temp tableHi,
Instead of -C can use -c (small case). The -C is for code page.
-c does not prompt for each field; it uses char as the storage type
Thanks
Hari
MCDBA
"Anup" <anup_pokhrel@.hotmail.com> wrote in message
news:u8U6iVWaEHA.3016@.tk2msftngp13.phx.gbl...
> I managed to coorect the earlier error but now it looks like this
> master..xp_cmdshell 'bcp ##ttable
> OUTPUT "res.xls" -C -S "server" -U "sa" -P "password"'
> the new error is
> NULL
> Enter the file storage type of field state_id [int-null]:
> (2 row(s) affected)
> I have 2 fields in the temp table
>

bcp still more errors

I managed to coorect the earlier error but now it looks like this
master..xp_cmdshell 'bcp ##ttable
OUTPUT "res.xls" -C -S "server" -U "sa" -P "password"'
the new error is
NULL
Enter the file storage type of field state_id [int-null]:
(2 row(s) affected)
I have 2 fields in the temp tableHi,
Instead of -C can use -c (small case). The -C is for code page.
-c does not prompt for each field; it uses char as the storage type
--
Thanks
Hari
MCDBA
"Anup" <anup_pokhrel@.hotmail.com> wrote in message
news:u8U6iVWaEHA.3016@.tk2msftngp13.phx.gbl...
> I managed to coorect the earlier error but now it looks like this
> master..xp_cmdshell 'bcp ##ttable
> OUTPUT "res.xls" -C -S "server" -U "sa" -P "password"'
> the new error is
> NULL
> Enter the file storage type of field state_id [int-null]:
> (2 row(s) affected)
> I have 2 fields in the temp table
>

BCP problem

Exec Master..xp_CmdShell 'bcp "exec mydb..SP_test ''200603''" queryout c:\test.txt -c -Slocalhost -Usa -Ppassword'

when i run this code in java , the file is created , but data not pump in , anyone know why is it so ?

it can run in query analyzer and no problemsorry, is my mistake , it doesn't give any error :( , just that i put in wrong parameter so no data|||sorry, is my mistake , it doesn't give any error :( , just that i put in wrong parameter so no data out

BCP in xp_cmdshell

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,
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

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} 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

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
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:

BCP in stored procedure

I have a stored procedure, which loops through a database and runs a BCP
statement (with changing criteria), as follows:
exec master..xp_cmdshell 'bcp "SELECT myfields FROM Database..Viewname WHERE
criteria ORDER BY criteria" queryout "C:\File.txt" -S ServerName -T -c'
When I run this, I get the following error:
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server
'MY DATABASE NAME' in sysservers. Execute sp_addlinkedserver to add the
server to sysservers.
I'm telling it which server to use, but it's reading the database name as a
server, for some reason. The server, however DOES show in sysservers. I've
also tried running this with -U sa -P ... with the same results. What would
be causing this? Thanks for advance for any help you can offer.
BariYou have to register that Server on the server you are running the query on.
You register the Server by executing
sp_addlinkedserver YOURSERVERNAME
Otherwise it won't be in systables.
Pain if you wanna distribute this code.

BCP Handling

Hi,

I am executing script like this. How to check for the errors if "master..xp_cmdshell @.bcpCommand" fails. Is there any way to verify that BCP is completed successfully

DECLARE @.FileName varchar(50),
@.bcpCommand varchar(2000)

SET @.FileName = 'E:\TestBCPOut.txt'
SET @.bcpCommand = 'bcp "SELECT * FROM pubs1..authors ORDER BY au_lname" queryout "'
SET @.bcpCommand = @.bcpCommand + @.FileName + '" -c -U -P'

EXEC master..xp_cmdshell @.bcpCommand

Thanks in Advance,declare @.ret int
EXEC @.ret=master..xp_cmdshell @.bcpCommand

|||Is is possible...........|||

In addition, you can also specify an error file for your bcp command, and then check it afterwards for any content:

DECLARE @.FileName varchar(50),
@.bcpCommand varchar(2000)
SET @.FileName = 'E:\TestBCPOut.txt'
SET @.bcpCommand = 'bcp "SELECT * FROM pubs1..authors ORDER BY au_lname" queryout "'
SET @.bcpCommand = @.bcpCommand + @.FileName + '" -c -U -P -ee:\myBCPerror.txt'

declare @.ret int
EXEC @.ret=master..xp_cmdshell @.bcpCommand

CREATE TABLE #bcperr(input varchar(255) null)
INSERT #bcperr(input) EXEC master..xp_cmdshell 'type e:\myBCPerror.txt'
IF EXISTS(SELECT * FROM #bcperror WHERE input IS NOT NULL)
RAISERROR('There was an error with the BCP command.', 16, 1)
DROP TABLE #bcperr

You can also just do a SELECT * FROM #bcperr to get the actual error rows.

|||

thanks a lot..

any idea how to trap the number of rows that were transffered during the bcp out process...

|||

If you also specify an output file with the -o parameter, you can 'parse' it and grab the line with the rows total in it.

declare @.rc int
EXEC @.rc = master..xp_cmdshell 'find c:\myBCPoutput.txt "rows copied"'

output
NULL
- C:\MYBCPOUTPUT.TXT
23 rows copied.
NULL

(4 row(s) affected)

=;o)

/Kenneth

|||Hey how do I capture this in a table... betn thanks !!!|||

Here's an example.

create table #bcpResult
( result varchar(50) null )
go

declare @.rc int

insert #bcpResult
EXEC @.rc = master..xp_cmdshell 'find c:\myBCPoutput.txt "rows copied"'
go
select * from #bcpResult where result like '%rows copied%'
go
drop table #bcpResult
go

=;o)
/Kenneth

bcp error: Unable to open BCP host data-file

I get this error message:

Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file

if I try to run

EXEC master..xp_cmdshell
'bcp TestDB.dbo.Employees out \\Ioana\Export\Employees.txt -c -t"," '

from Query Analyzer (or a store procedure).

If run the same command in cmd or run window (of course without EXEC and xp_cmdshell) I do not get any error and the content of the table is exported in the file I want...

If the exported file is on a local folder on the server everything is working fine even in Query Analyzer...

Thanking in AdvanceMy guess is that the user that runs SQL server (default is LocalSystem) has no permissions on the server called Ioana. If SQL Server is running under LocalSystem, you will have to create a domian (or domain local) account for SQL Server to run under. Then grant that user permission on this share on Ioana. You will also have to add the user to the local administrators group on the SQL Server, too. Hope this helps.|||Originally posted by MCrowley
My guess is that the user that runs SQL server (default is LocalSystem) has no permissions on the server called Ioana. If SQL Server is running under LocalSystem, you will have to create a domian (or domain local) account for SQL Server to run under. Then grant that user permission on this share on Ioana. You will also have to add the user to the local administrators group on the SQL Server, too. Hope this helps.

SQL Server is install on SQLSERVER machine and Ioana is my machine.
I want to run this script on SQLSERVER machine (in Query Analyzer) and to export the file in Export directory on my machine. The Export directory on my machine is shared with all the rights.

If run the bcp from cmd window on SQLSERVER machine is working fine. From Query Analyzer on the same machine (SQLSERVER) is not working even if I am logged in as system administrator with windows authentication. The administrator of SQLSERVER have full permision on my shared directory.

Thanks again

bcp and xp_cmdshell

Hi Experts,
When I execute the command
"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp" "select Severity
from tempdb..EventsArchive" queryout
"c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210
From the command line the bcp works fine. (I need the full path for the
bcp because we have Sybase as well on the machine)
However, when I execute the code below it form a sp it fails.
declare @.statement varchar(2000)
set @.statement = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp"
"select Severity from tempdb..EventsArchive" queryout
c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210'
exec master..xp_cmdshell @.statement
Thanks,
Avi
Hi
What does BOL say about more than one double quote?
Syntax
xp_cmdshell {'command_string'} [, no_output]
Arguments
'command_string'
Is the command string to execute at the operating-system command shell.
command_string is varchar(8000) or nvarchar(4000), with no default.
command_string cannot contain more than one set of double quotation marks. A
single pair of quotation marks is necessary if any spaces are present in the
file paths or program names referenced by command_string. If you have trouble
with embedded spaces, consider using FAT 8.3 file names as a workaround.
no_output
Is an optional parameter executing the given command_string, and does not
return any output to the client.
Regards
Mike
"Avi" wrote:

> Hi Experts,
> When I execute the command
> "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp" "select Severity
> from tempdb..EventsArchive" queryout
> "c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210
> From the command line the bcp works fine. (I need the full path for the
> bcp because we have Sybase as well on the machine)
> However, when I execute the code below it form a sp it fails.
> declare @.statement varchar(2000)
> set @.statement = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp"
> "select Severity from tempdb..EventsArchive" queryout
> c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210'
> exec master..xp_cmdshell @.statement
> Thanks,
> Avi
>
>
>
|||Hi Avi,
You can also use it the following way;
SET @.conn = '" -U <username> -P <pwd> -c'
SET @.filename = '\\servername\foldername\test'+'.txt')
SET @.sql1 = 'BCP "SELECT * FROM <table name>)" QUERYOUT "'
SET @.sql1 = @.sql1 + @.filename + @.conn
EXEC master..xp_cmdshell @.sql1
You don't have to give full path of BCP utility.
Thanks
Yogish

bcp and xp_cmdshell

Hi Experts,
When I execute the command
"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp" "select Severity
from tempdb..EventsArchive" queryout
"c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210
From the command line the bcp works fine. (I need the full path for the
bcp because we have Sybase as well on the machine)
However, when I execute the code below it form a sp it fails.
declare @.statement varchar(2000)
set @.statement = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp"
"select Severity from tempdb..EventsArchive" queryout
c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210'
exec master..xp_cmdshell @.statement
Thanks,
AviHi
What does BOL say about more than one double quote?
Syntax
xp_cmdshell {'command_string'} [, no_output]
Arguments
'command_string'
Is the command string to execute at the operating-system command shell.
command_string is varchar(8000) or nvarchar(4000), with no default.
command_string cannot contain more than one set of double quotation marks. A
single pair of quotation marks is necessary if any spaces are present in the
file paths or program names referenced by command_string. If you have troubl
e
with embedded spaces, consider using FAT 8.3 file names as a workaround.
no_output
Is an optional parameter executing the given command_string, and does not
return any output to the client.
Regards
Mike
"Avi" wrote:

> Hi Experts,
> When I execute the command
> "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp" "select Severity
> from tempdb..EventsArchive" queryout
> "c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210
> From the command line the bcp works fine. (I need the full path for the
> bcp because we have Sybase as well on the machine)
> However, when I execute the code below it form a sp it fails.
> declare @.statement varchar(2000)
> set @.statement = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp
"
> "select Severity from tempdb..EventsArchive" queryout
> c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210'
> exec master..xp_cmdshell @.statement
> Thanks,
> Avi
>
>
>|||Hi Avi,
You can also use it the following way;
SET @.conn = '" -U <username> -P <pwd> -c'
SET @.filename = '\\servername\foldername\test'+'.txt')
SET @.sql1 = 'BCP "SELECT * FROM <table name> )" QUERYOUT "'
SET @.sql1 = @.sql1 + @.filename + @.conn
EXEC master..xp_cmdshell @.sql1
You don't have to give full path of BCP utility.
Thanks
Yogish

bcp and xp_cmdshell

Hi Experts,
When I execute the command
"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp" "select Severity
from tempdb..EventsArchive" queryout
"c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210
From the command line the bcp works fine. (I need the full path for the
bcp because we have Sybase as well on the machine)
However, when I execute the code below it form a sp it fails.
declare @.statement varchar(2000)
set @.statement = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp"
"select Severity from tempdb..EventsArchive" queryout
c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210'
exec master..xp_cmdshell @.statement
Thanks,
AviHi
What does BOL say about more than one double quote?
Syntax
xp_cmdshell {'command_string'} [, no_output]
Arguments
'command_string'
Is the command string to execute at the operating-system command shell.
command_string is varchar(8000) or nvarchar(4000), with no default.
command_string cannot contain more than one set of double quotation marks. A
single pair of quotation marks is necessary if any spaces are present in the
file paths or program names referenced by command_string. If you have trouble
with embedded spaces, consider using FAT 8.3 file names as a workaround.
no_output
Is an optional parameter executing the given command_string, and does not
return any output to the client.
Regards
Mike
"Avi" wrote:
> Hi Experts,
> When I execute the command
> "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp" "select Severity
> from tempdb..EventsArchive" queryout
> "c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210
> From the command line the bcp works fine. (I need the full path for the
> bcp because we have Sybase as well on the machine)
> However, when I execute the code below it form a sp it fails.
> declare @.statement varchar(2000)
> set @.statement = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp"
> "select Severity from tempdb..EventsArchive" queryout
> c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210'
> exec master..xp_cmdshell @.statement
> Thanks,
> Avi
>
>
>|||Hi Avi,
You can also use it the following way;
SET @.conn = '" -U <username> -P <pwd> -c'
SET @.filename = '\\servername\foldername\test'+'.txt')
SET @.sql1 = 'BCP "SELECT * FROM <table name>)" QUERYOUT "'
SET @.sql1 = @.sql1 + @.filename + @.conn
EXEC master..xp_cmdshell @.sql1
You don't have to give full path of BCP utility.
--
Thanks
Yogish

bcp and transaction problem

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

BCP and Stored Procedure

I am trying to use BCP to generate a textfile with the results of sp_help_revlogins.
xp_cmdshell 'bcp "execute sp_help_revlogin" queryout c:\test\test.txt -c -S"testserver" -U"sa" -P"test"'
The error I receive is the following:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain at least one column
I would appreciate any incite on the solution to this problem as well as the cause.
Thank you in advance.
Robert
If all you want is the output of sp_help_revlogin to be placed in a file try
using OSQL. Something like this:
OSQL -S<yourserver> -U<login> -P<password> -Q"exec
sp_help_revlogin" -oc:\test\test.txt
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Robert M." <Robert M.@.discussions.microsoft.com> wrote in message
news:0C360F11-4D5A-4BC4-976E-35650510E775@.microsoft.com...
> I am trying to use BCP to generate a textfile with the results of
sp_help_revlogins.
> xp_cmdshell 'bcp "execute sp_help_revlogin" queryout
c:\test\test.txt -c -S"testserver" -U"sa" -P"test"'
> The error I receive is the following:
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain at
least one column
> I would appreciate any incite on the solution to this problem as well as
the cause.
> Thank you in advance.
> Robert
>
>
|||If all you want is the output of sp_help_revlogin to be placed in a file try
using OSQL. Something like this:
OSQL -S<yourserver> -U<login> -P<password> -Q"exec
sp_help_revlogin" -oc:\test\test.txt
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Robert M." <Robert M.@.discussions.microsoft.com> wrote in message
news:0C360F11-4D5A-4BC4-976E-35650510E775@.microsoft.com...
> I am trying to use BCP to generate a textfile with the results of
sp_help_revlogins.
> xp_cmdshell 'bcp "execute sp_help_revlogin" queryout
c:\test\test.txt -c -S"testserver" -U"sa" -P"test"'
> The error I receive is the following:
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain at
least one column
> I would appreciate any incite on the solution to this problem as well as
the cause.
> Thank you in advance.
> Robert
>
>

BCP and Stored Procedure

I am trying to use BCP to generate a textfile with the results of sp_help_re
vlogins.
xp_cmdshell 'bcp "execute sp_help_revlogin" queryout c:\test\test.txt -c -S"
testserver" -U"sa" -P"test"'
The error I receive is the following:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must conta
in at least one column
I would appreciate any incite on the solution to this problem as well as the
cause.
Thank you in advance.
RobertIf all you want is the output of sp_help_revlogin to be placed in a file try
using OSQL. Something like this:
OSQL -S<yourserver> -U<login> -P<password> -Q"exec
sp_help_revlogin" -oc:\test\test.txt
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Robert M." <Robert M.@.discussions.microsoft.com> wrote in message
news:0C360F11-4D5A-4BC4-976E-35650510E775@.microsoft.com...
> I am trying to use BCP to generate a textfile with the results of
sp_help_revlogins.
> xp_cmdshell 'bcp "execute sp_help_revlogin" queryout
c:\test\test.txt -c -S"testserver" -U"sa" -P"test"'
> The error I receive is the following:
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain at[
/vbcol]
least one column[vbcol=seagreen]
> I would appreciate any incite on the solution to this problem as well as
the cause.
> Thank you in advance.
> Robert
>
>

bcp queryout

I have a process that does a BCP "queryout" using xp_cmdshell to a Network
share. The process is being invoked by a non-sysadmin account. I receive the
error Cannot open BCP file.
Sql7 on a Windows N/T box.
How do you setup permissions to make happen?
Thanks.
Hi Andy
If you are running SQL Server Service Account as local system then you will
not have access to the network share, change this to a domain account that
has access and you should be ok.
John
"Andy" wrote:

> I have a process that does a BCP "queryout" using xp_cmdshell to a Network
> share. The process is being invoked by a non-sysadmin account. I receive the
> error Cannot open BCP file.
> Sql7 on a Windows N/T box.
> How do you setup permissions to make happen?
>
> Thanks.
>
>
>

bcp queryout

I have a process that does a BCP "queryout" using xp_cmdshell to a Network
share. The process is being invoked by a non-sysadmin account. I receive the
error Cannot open BCP file.
Sql7 on a Windows N/T box.
How do you setup permissions to make happen?
Thanks.Hi Andy
If you are running SQL Server Service Account as local system then you will
not have access to the network share, change this to a domain account that
has access and you should be ok.
John
"Andy" wrote:

> I have a process that does a BCP "queryout" using xp_cmdshell to a Network
> share. The process is being invoked by a non-sysadmin account. I receive t
he
> error Cannot open BCP file.
> Sql7 on a Windows N/T box.
> How do you setup permissions to make happen?
>
> Thanks.
>
>
>