Sql Basic Security: generic

显示标签为“generic”的博文。显示所有博文

2012年3月19日星期一

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

2012年2月16日星期四

Batch Processing

/**
l'm trying to improve my batch processing routines by
creating a generic method which l can apply on most of
my jobs.Reason Being l'm working with large Data Sets
This is what l'm trying to achieve
1.Get The Rowcount of the Source
2.Split It Into manageable batches
3.Loop through the batches Until the last one is Done
**/
How do l intergrate these two so it can automatically pick up the
batchsizes and process until the last batch is done.
--Outer Loop
Declare @.RowCount Int
Declare @.LoopIteration Int
Declare @.BatchSize Int
--Get The RowCount Of the Table
Select @.RowCount = (Select Count(*) From tbTest)
--
Select @.LoopIteration = @.RowCount/2000000
Select @.BatchSize = @.RowCount/@.LoopIteration
--Select Distinct Client,Convert(Varchar(15),'') As CltNo
-- ,Convert(Int,0) As Status Into tbtest
from tbAddress
--Inner Loop --Works when run Manually
SET ROWCOUNT @.BatchSize--100000 --54 Secs
WHILE 1 = 1
BEGIN
Begin Transaction
Update tbTest
Set CltNo = Client + 1
,Status = 1 Where Status = 0
Commit
if @.@.rowcount = 0
break
END
SET ROWCOUNT 0
--Select * from tbTest Where Status = 1
Message posted via http://www.webservertalk.comWhy do you care what the table size is? You should pick a batch size that
can be accomplished in a short enough time so that it does not interfere
with other users and can have minimal overhead. It usually doesn't matter
if the table is 10M rows or 100K rows. Your batch size should be the same.
What that size is can only be found with proper testing and adjustment.
Usually 10K is a good place to start as it is small enough to finish quickly
and large enough to nibble at the work load. IF you check the @.@.ROWCOUNT
and break when it is 0 then you don't have to worry about how many loops
there will be. Another comment is that there is no need to wrap the Update
in a Begin - Commit. Each update is ATOMIC all by itself. If you update
10K rows in a single update statement all 10K will either work or they wont.
There will not be any middle ground. By adding it in there you now have to
put error checking and handle possible open trans.
Andrew J. Kelly SQL MVP
"Raymond M via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in
message news:a911f5b29ab44d0b837051f840966d55@.SQ
webservertalk.com...
> /**
> l'm trying to improve my batch processing routines by
> creating a generic method which l can apply on most of
> my jobs.Reason Being l'm working with large Data Sets
> This is what l'm trying to achieve
> 1.Get The Rowcount of the Source
> 2.Split It Into manageable batches
> 3.Loop through the batches Until the last one is Done
> **/
> How do l intergrate these two so it can automatically pick up the
> batchsizes and process until the last batch is done.
> --Outer Loop
> Declare @.RowCount Int
> Declare @.LoopIteration Int
> Declare @.BatchSize Int
>
> --Get The RowCount Of the Table
> Select @.RowCount = (Select Count(*) From tbTest)
> --
> Select @.LoopIteration = @.RowCount/2000000
> Select @.BatchSize = @.RowCount/@.LoopIteration
>
>
> --Select Distinct Client,Convert(Varchar(15),'') As CltNo
> -- ,Convert(Int,0) As Status Into tbtest
> from tbAddress
> --Inner Loop --Works when run Manually
> SET ROWCOUNT @.BatchSize--100000 --54 Secs
> WHILE 1 = 1
> BEGIN
> Begin Transaction
> Update tbTest
> Set CltNo = Client + 1
> ,Status = 1 Where Status = 0
> Commit
> if @.@.rowcount = 0
> break
> END
> SET ROWCOUNT 0
> --Select * from tbTest Where Status = 1
> --
> Message posted via http://www.webservertalk.com

订阅：博文 (Atom)

2012年3月19日星期一

BCP in xp_cmdshell

BCP in xp_cmdshell

BCP in xp_cmdshell

2012年2月16日星期四

Batch Processing

Sql Basic Security

Blog Archive

About Me