xp_cmdshell alternative?

Question

xp_cmdshell alternative?

K Warnock

SSC Journeyman

Points: 85
More actions
December 2, 2004 at 12:59 pm

#63577

"Extended stored procedures allow DBAs to execute any non-interactive DLL file from an ISQL prompt... The xp_cmdshell procedure allows the DBA to execute any operating system command via TSQL."
Is there an alternative to xp_cmdshell enabling an execution of operating system command via TSQL for Microsoft SQLServer 2000 Enterprise Edition?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

mike gercevich SSCrazy Points: 2961 More actions · Answer 1

Assuming you have the SQLAgent running on your server, you could build a stored procedure to create a job and job step(s) that will execute and delete itself on success or failure.

Another option is to pre-create the job on the server and then just execute it by job_name. Having a job on your server does not mean that you need to have a execution schedule assigned to it

The job will execute asynchronously, so you would only be able to return the status of the execution job successfully started and would not be able to return the command errorlevel, unless you add some tricks to the job to retain the values for post execution query or returned the jobid and queried the step logs.

-Mike

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Of what "operating system commands" are we speaking here?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

K Warnock SSC Journeyman Points: 85 More actions · Answer 3

Hi, Frank. I want to launch either an .exe or .bat from within TSQL sproc.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Using the sp_OA* procedures you can virtually launch any OLE object, but is there anything wrong with xp_cmdshell?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

K Warnock SSC Journeyman Points: 85 More actions · Answer 5

Actually no, xp_cmdshell is one of my faves. The development request coming from above asked me to investigate alternatives to its use.

Frank, I didn't know about sp_OA*s. Thank you. I'll check it out.

-----------

I've been looking at those sp_OA*s for about 6 hrs now - I'm going to use xp_cmdshell. Much easier.

Thank you.