MS SQL Server - XP

Author

XP_CMDSHELL

Jim Heavey

2006-04-06, 8:23 pm

I am trying to figure out how to properly execute dos commands from
XP_CMDSHELL.

If I go down to the dos prompt and enter "Erase d:\tmp\*.*"
it erases all of the files.

If I enter exec XP-CMDSHELL 'Erase d:\tmp\*.*', I get a message back asking
me to confirm my deletes....Why? How can I respond and tell it to delete
everything?

It also does not seem to respond correctly to commands such as switching
drives and navigating a folder structure. Why not?

I'm really new to SQL Server, so pardon my dumb questions.

Thanks in advance for your assistance

Tibor Karaszi

2006-04-06, 8:23 pm

> If I enter exec XP-CMDSHELL 'Erase d:\tmp\*.*', I get a message back asking
> me to confirm my deletes....

You need to make the commands silent. At the DOS prompt, type "erase /?" and read about the /Q
switch.

> It also does not seem to respond correctly to commands such as switching
> drives and navigating a folder structure. Why not?

SQL Server doesn't remember what you did between the xp_cmdshell invocations. Put is all in a BAT
file and invoke that BAT file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/

"Jim Heavey" < JimHeavey@discussion
s.microsoft.com> wrote in message
news:9889DC44-8CD5-45B9-AEDB- 14EA33F200AA@microso
ft.com...
>I am trying to figure out how to properly execute dos commands from
> XP_CMDSHELL.
>
> If I go down to the dos prompt and enter "Erase d:\tmp\*.*"
> it erases all of the files.
>
> If I enter exec XP-CMDSHELL 'Erase d:\tmp\*.*', I get a message back asking
> me to confirm my deletes....Why? How can I respond and tell it to delete
> everything?
>
> It also does not seem to respond correctly to commands such as switching
> drives and navigating a folder structure. Why not?
>
> I'm really new to SQL Server, so pardon my dumb questions.
>
> Thanks in advance for your assistance
>
>

Geoff N. Hiten

2006-04-06, 8:23 pm

Use the /Q attribute for the ERASE command so it will not promt you for
confirmation. XP_CMDSHELL is not interactive.

If you invoke XP_CMDSHELL multiple times, each call is a completely new
shell. Each call is the equivalent to opening a new command window and
typing a single line into that window, so there is no context continuity
between commands.

--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

"Jim Heavey" < JimHeavey@discussion
s.microsoft.com> wrote in message
news:9889DC44-8CD5-45B9-AEDB- 14EA33F200AA@microso
ft.com...
>I am trying to figure out how to properly execute dos commands from
> XP_CMDSHELL.
>
> If I go down to the dos prompt and enter "Erase d:\tmp\*.*"
> it erases all of the files.
>
> If I enter exec XP-CMDSHELL 'Erase d:\tmp\*.*', I get a message back
> asking
> me to confirm my deletes....Why? How can I respond and tell it to delete
> everything?
>
> It also does not seem to respond correctly to commands such as switching
> drives and navigating a folder structure. Why not?
>
> I'm really new to SQL Server, so pardon my dumb questions.
>
> Thanks in advance for your assistance
>
>

Edgardo Valdez, MCSD, MCDBA

2006-04-06, 8:23 pm

Jim,

For the case of the prompt, you need to use:

exec XP_CMDSHELL 'Erase d:\tmp\*.* /Q'

which is the Quiet mode, do not ask if ok to delete on global wildcard

The intent of xp_cmdshell is to execute a given command string as an
operating-system command shell and returns any output as rows of text. It is
not designed to be used as a navigating tool like explorer.

It will open a shell session on the OS and it will release the session after
the directive is completed. It does not keep the actual values of the closed
session.

You can actually trick it to execute more than one directive on the same
active session by using the following:

exec XP_CMDSHELL 'dir d:\tmp & Erase d:\tmp\*.* /Q & dir D:\tmp'

It will show you the files in tmp, erase them, and show you the tmp content
afterwards

"Jim Heavey" wrote:

> I am trying to figure out how to properly execute dos commands from
> XP_CMDSHELL.
>
> If I go down to the dos prompt and enter "Erase d:\tmp\*.*"
> it erases all of the files.
>
> If I enter exec XP-CMDSHELL 'Erase d:\tmp\*.*', I get a message back asking
> me to confirm my deletes....Why? How can I respond and tell it to delete
> everything?
>
> It also does not seem to respond correctly to commands such as switching
> drives and navigating a folder structure. Why not?
>
> I'm really new to SQL Server, so pardon my dumb questions.
>
> Thanks in advance for your assistance
>
>

Sponsored Links