ERROR in xp_cmdshell syntax

hi!
I'm usng SQL server and i want to rename a file using the xp_cmdshell.

here's my code:

declare @.path varchar (20)
declare @.filename varchar (20)
declare @.new varchar (20)
declare @.cmd varchar (20)

set @.path = 'E:\folder1\folder2\folder3\'
set @.filename = @.path+'filename.txt'
set @.new = 'newfilename.txt'
set @.cmd = 'REN '+@.filename+' '+@.new

exec master..xp_cmdshell @.cmd

--
after running, i get this error:
The syntax of the command is incorrect.

Need technical help. please advise asap.
thnx again.hi
i am not sure but i think u have to delclare another variable such as @.result and then
execute @.result=master..xp_cmdshell @.cmd
i hope it work|||This will help you. first declare the parameters and use the following code.

set @.backup_filePartialname = @.diff_backup_path+@.db_name + '_*.bak'
set @.backup_filename = @.db_name + '.bak'
set @.dos_cmd = 'RENAME ' + @.backup_filePartialname + ' ' + @.backup_filename

EXEC @.dos_result = master.dbo.xp_cmdshell @.dos_cmd, NO_OUTPUT

Error in using xp_cmdshell and osql

Hi, can anyone help me with the following xp_cmdshell/osql problem?
To solve a problem with osql I use the two sql test-scripts. Script1 is
loaded in Sql Query Analyzer. Executing scripts 1: this calls en execute
script2, which prints a message to the output window. Quite simple.
My problem: this is functioning well on a my develop pc (Windows XP-Pro),
but is not functioning on a production server with Windows 2000 Server (SP4)
.
On this production machine, recently sql server 2000 SP3 has been installed,
and everything of sqlserver 2000 looks the same as on my develop pc under
XP-pro. The error reported is about the syntax usage of osql: "usage: osql
[-U login id] [-P password] [-S server]" and the all the rest of the option
s
..... So I should think it has something to do with the used syntax, but i
t
IS functioning on my develop pc!
I don't understand why these testscripts do work on my develop pc, and don't
on the production server. I guess it has something to do with either the
difference in OS, or a setting in the new sql installation on the production
server, but I can't find out the cause.
****************************************
**
Script1.sql:
DECLARE @.SqlPath sysname, @.SqlScript as sysname, @.Cmd sysname, @.CmdCpl
nvarchar(400), @.CmdAndPath sysname
SET @.Cmd = 'osql -S MyServer -d MyDb -Usa -PPwd -i'
SET @.SqlPath = 'C:\MyPathToScript2'
SET @.CmdAndPath = @.Cmd + @.SqlPath
SET @.SqlScript = 'Script2.sql'
SET @.CmdCpl = @.CmdAndPath + @.SqlScript
EXEC master..xp_cmdshell @.CmdCpl
****************************************
**
Script2.sql:
DECLARE @.Test varchar(255)
SET @.Test = 'Hello world'
PRINT @.Test
****************************************
**
Any suggestions are welcome,
Regards Coen.One possible cause is that @.CmdCpl contains whitespace in the prod
environment so the command line parameters are not parsed as expected. In
that case, you can enclose the path in double-quotes:
SET @.CmdCpl = '"' + @.CmdAndPath + @.SqlScript + '"'
If that isn't the problem, try printing the actual @.CmdCpl value and running
from the command line.
Hope this helps.
Dan Guzman
SQL Server MVP
"Coen" <cbusker@.newsgroups.microsoft.com> wrote in message
news:D4DCA3BF-E67B-437C-ADE1-9071EA673BCE@.microsoft.com...
> Hi, can anyone help me with the following xp_cmdshell/osql problem?
> To solve a problem with osql I use the two sql test-scripts. Script1 is
> loaded in Sql Query Analyzer. Executing scripts 1: this calls en execute
> script2, which prints a message to the output window. Quite simple.
> My problem: this is functioning well on a my develop pc (Windows XP-Pro),
> but is not functioning on a production server with Windows 2000 Server
> (SP4).
> On this production machine, recently sql server 2000 SP3 has been
> installed,
> and everything of sqlserver 2000 looks the same as on my develop pc under
> XP-pro. The error reported is about the syntax usage of osql: "usage: osql
> [-U login id] [-P password] [-S server]" and the all the rest of the
> options
> ..... So I should think it has something to do with the used syntax, but
> it
> IS functioning on my develop pc!
> I don't understand why these testscripts do work on my develop pc, and
> don't
> on the production server. I guess it has something to do with either the
> difference in OS, or a setting in the new sql installation on the
> production
> server, but I can't find out the cause.
> ****************************************
**
> Script1.sql:
> DECLARE @.SqlPath sysname, @.SqlScript as sysname, @.Cmd sysname, @.CmdCpl
> nvarchar(400), @.CmdAndPath sysname
> SET @.Cmd = 'osql -S MyServer -d MyDb -Usa -PPwd -i'
> SET @.SqlPath = 'C:\MyPathToScript2'
> SET @.CmdAndPath = @.Cmd + @.SqlPath
> SET @.SqlScript = 'Script2.sql'
> SET @.CmdCpl = @.CmdAndPath + @.SqlScript
> EXEC master..xp_cmdshell @.CmdCpl
> ****************************************
**
> Script2.sql:
> DECLARE @.Test varchar(255)
> SET @.Test = 'Hello world'
> PRINT @.Test
> ****************************************
**
> Any suggestions are welcome,
> Regards Coen.
>|||Dan, thank you for your help. Indeed the problem was caused by whitespaces,
so simple. The double quotes resolved the problem. Thank you for your
assistance.
Regards, Coen
"Dan Guzman" wrote:

> One possible cause is that @.CmdCpl contains whitespace in the prod
> environment so the command line parameters are not parsed as expected. In
> that case, you can enclose the path in double-quotes:
> SET @.CmdCpl = '"' + @.CmdAndPath + @.SqlScript + '"'
> If that isn't the problem, try printing the actual @.CmdCpl value and runni
ng
> from the command line.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Coen" <cbusker@.newsgroups.microsoft.com> wrote in message
> news:D4DCA3BF-E67B-437C-ADE1-9071EA673BCE@.microsoft.com...
>
>sql

error in executing exec xp_cmdshell

Hello,
I am getting this error
Msg 50001, Level 1, State 50001
xpsql.cpp: Error 1314 from CreateProcessAsUser on line 636
when I try to execute this statement using sql user login
who is not having sysamin rights.
exec xp_cmdshell "copy D:\File1.txt E:\File1.txt"
I have configured the Proxy account for SQL Agent, but
still getting this error.
Can any help me ?
Regds,
ManojDoes the ID have rights to Execute xp_cmdshell? Did you check the NT id for
Proxy and make sure it has adequate rights for your copy? Read from the
root of D and write to the root of E. To check for sure make the Id a
temporary local admin for the windows box and rerun your query. Double
check by creating 2 folders and giving the ID full control of both folders
and change your query to copy to and from the folders instead.
Jeff Duncan
MCDBA, MCSE+I
"Manoj Raheja" <manoj_raheja@.hotmail.com> wrote in message
news:889301c43299$96196bd0$a601280a@.phx.gbl...
> Hello,
> I am getting this error
> Msg 50001, Level 1, State 50001
> xpsql.cpp: Error 1314 from CreateProcessAsUser on line 636
> when I try to execute this statement using sql user login
> who is not having sysamin rights.
> exec xp_cmdshell "copy D:\File1.txt E:\File1.txt"
> I have configured the Proxy account for SQL Agent, but
> still getting this error.
> Can any help me ?
> Regds,
> Manoj|||Make sure the SQL Server startup account has the necessary rights:
- Act as part of the operating system.
- Increase quotas.
- replace process level token.
- Log on as a batch job.
Having increase quotas missing has been a cause of this problem.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||The loging wich I am using is a member of Local and Domain
admin group.

>--Original Message--
>Does the ID have rights to Execute xp_cmdshell? Did you
check the NT id for
>Proxy and make sure it has adequate rights for your
copy? Read from the
>root of D and write to the root of E. To check for sure
make the Id a
>temporary local admin for the windows box and rerun your
query. Double
>check by creating 2 folders and giving the ID full
control of both folders
>and change your query to copy to and from the folders
instead.
>--
>Jeff Duncan
>MCDBA, MCSE+I
>"Manoj Raheja" <manoj_raheja@.hotmail.com> wrote in message
>news:889301c43299$96196bd0$a601280a@.phx.gbl...
636[vbcol=seagreen]
login[vbcol=seagreen]
>
>.
>|||> The loging wich I am using is a member of Local and Domain
> admin group.
Did you assign the SQL Server service account the advanced user rights
detailed in this thread by Rand? The rights are needed so that SQL Server
can change security context to the proxy account. The permissions are set
automatically when you specify the SQL Server service account during
installation or change it from Enterprise manager. However, these are not
set when you change the account directly.
Output from command Windows command NET HELPMSG 1314:
A required privilege is not held by the client.
Hope this helps.
Dan Guzman
SQL Server MVP
"Manoj Raheja" <manoj_raheja@.hotmail.com> wrote in message
news:9a2301c433ea$81364d80$a001280a@.phx.gbl...[vbcol=seagreen]
> The loging wich I am using is a member of Local and Domain
> admin group.
>
> check the NT id for
> copy? Read from the
> make the Id a
> query. Double
> control of both folders
> instead.
> 636
> login|||The problem got solved, The login user was not having the
Increase quotas on the server, which after setting worked
out
Thanks,
Manoj
>--Original Message--
Domain[vbcol=seagreen]
>Did you assign the SQL Server service account the
advanced user rights
>detailed in this thread by Rand? The rights are needed
so that SQL Server
>can change security context to the proxy account. The
permissions are set
>automatically when you specify the SQL Server service
account during
>installation or change it from Enterprise manager.
However, these are not
>set when you change the account directly.
>Output from command Windows command NET HELPMSG 1314:
> A required privilege is not held by the client.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Manoj Raheja" <manoj_raheja@.hotmail.com> wrote in message
>news:9a2301c433ea$81364d80$a001280a@.phx.gbl...
Domain[vbcol=seagreen]
you[vbcol=seagreen]
sure[vbcol=seagreen]
your[vbcol=seagreen]
message[vbcol=seagreen]
line[vbcol=seagreen]
but[vbcol=seagreen]
>
>.
>|||Hi,
One quick question - If I want to have my SQL service startup using a system
account, can I still set it to have "Increase Quotas on the server"? If so,
could you let me know where I can set that?
Thanks!
/ec
"Manoj Raheja" <manoj_raheja@.hotmail.com> wrote in message
news:ac9201c4368c$aa05ba80$a001280a@.phx.gbl...[vbcol=seagreen]
> The problem got solved, The login user was not having the
> Increase quotas on the server, which after setting worked
> out
> Thanks,
> Manoj
> Domain
> advanced user rights
> so that SQL Server
> permissions are set
> account during
> However, these are not
> Domain
> you
> sure
> your
> message
> line
> but

error in --> EXEC xp_cmdshell ''bcp "SELECT * FROM tbl a where a.flag=N" queryout

some one can tell if i can make a "where" clause inside the BCP , when i say : select * FROM tbl where flag=N , usually the where flag='N' works with the ' ' , but it gives no error when save the store procedure without it ' ' saves nicely the SP,

but

like this it saves well "SELECT * FROM tbl where flag=N" and

when i execute it, give a sql statement error "Error = [Microsoft][SQL Native Client][SQL Server]Invalid column name N."

i dont now , any help

PS: perhaps at the end of the BCP > -c -T , must have some more or less

Here the solution,

You have to use the escape sequence (Like \' or \" in other programming langauages).

SQL escape sequence for single quote is consecutive 2 single quotes ''.

EXEC xp_cmdshell 'bcp "SELECT * FROM tbl a where a.flag=''N''"

|||

Manivannan.D.Sekaran wrote:

Here the solution,

You have to use the escape sequence (Like \' or \" in other programming langauages).

SQL escape sequence for single quote is consecutive 2 single quotes ''.

EXEC xp_cmdshell 'bcp "SELECT * FROM tbl a where a.flag=''N''"

yes i tryed both ways with 'N' and "N" and it says the same error message

with this

'bcp "SELECT * FROM delta.dbo.tblRANGEL_O_STOCKS where updt="N"" queryout "'

Error = [Microsoft][SQL Native Client][SQL Server]Invalid column name 'N'.

with this says

'bcp "SELECT * FROM delta.dbo.tblRANGEL_O_STOCKS where updt= 'N' " queryout "'

Msg 102, Level 15, State 1, Procedure spDELTA_P1_RANGEL_STOCKS, Line 59

Incorrect syntax near ' " queryout "'.

|||

Please copy and paste the following code,

Instead of 2 single quotes you are trying with 1 double quote.

Code Snippet

EXEC xp_cmdshell 'bcp "SELECT * FROM tbl a where a.flag=''N''" queryout F.txt -c -T'

|||

THANK YOU A LOT

and it was only a little thing like a '