Execute OS commands instead of Xp_cmdshell

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Other SQL Server 2012 Topics

Execute OS commands instead of Xp_cmdshell

Author

Topic

rajuk13
Starting Member

3 Posts

Posted - 2013-09-27 : 07:45:32

Hi All,
We have a legacy code running under Sql 2000. Lots of procs calling DTS , OS commands like Dir, Ping, Erase , Copy , Move ,Create Directory etc using Xp_Cmdshell. We are migrating the DB , and was told not to use Xp_cmdshell , so triyng to find alternative.Situation is Planning to either call DTS packages using Autosys, or move to SSIS can schedule using Autosys. The procedures has got many usages of Xp_cmdshell and logic of doign various things , so seperating the proc from OS commands is really a tough task , But just an alternative of XP_cmdshell is also not available ??? Please help , not sure my research was sufficient or did I miss something.
Use of SQLCMD (not possible as I need this replacement inside the Procs)
Use of Sp_OACreate,sp_OAMethod also not allowed in my organisation
Use of Sql proc calls from .NET - is little too much as I have around 35 procedures which heavily uses Xp_cmdshell... Please suggest if there is any other method of executing above OS commands.
regards
Raj

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-27 : 08:10:18

quote:
Originally posted by rajuk13

Hi All,
We have a legacy code running under Sql 2000. Lots of procs calling DTS , OS commands like Dir, Ping, Erase , Copy , Move ,Create Directory etc using Xp_Cmdshell. We are migrating the DB , and was told not to use Xp_cmdshell , so triyng to find alternative.Situation is Planning to either call DTS packages using Autosys, or move to SSIS can schedule using Autosys. The procedures has got many usages of Xp_cmdshell and logic of doign various things , so seperating the proc from OS commands is really a tough task , But just an alternative of XP_cmdshell is also not available ??? Please help , not sure my research was sufficient or did I miss something.
Use of SQLCMD (not possible as I need this replacement inside the Procs)
Use of Sp_OACreate,sp_OAMethod also not allowed in my organisation
Use of Sql proc calls from .NET - is little too much as I have around 35 procedures which heavily uses Xp_cmdshell... Please suggest if there is any other method of executing above OS commands.
regards
Raj

Lookup CLR stored procedures. You MAY be able to use those - but it is no snap-in replacement; you will have to invest time and efforts to do the conversion.

rajuk13
Starting Member

3 Posts

Posted - 2013-09-27 : 08:42:31

quote:
Originally posted by James K

quote:
Originally posted by rajuk13

Hi All,
We have a legacy code running under Sql 2000. Lots of procs calling DTS , OS commands like Dir, Ping, Erase , Copy , Move ,Create Directory etc using Xp_Cmdshell. We are migrating the DB , and was told not to use Xp_cmdshell , so triyng to find alternative.Situation is Planning to either call DTS packages using Autosys, or move to SSIS can schedule using Autosys. The procedures has got many usages of Xp_cmdshell and logic of doign various things , so seperating the proc from OS commands is really a tough task , But just an alternative of XP_cmdshell is also not available ??? Please help , not sure my research was sufficient or did I miss something.
Use of SQLCMD (not possible as I need this replacement inside the Procs)
Use of Sp_OACreate,sp_OAMethod also not allowed in my organisation
Use of Sql proc calls from .NET - is little too much as I have around 35 procedures which heavily uses Xp_cmdshell... Please suggest if there is any other method of executing above OS commands.
regards
Raj
Lookup CLR stored procedures. You MAY be able to use those - but it is no snap-in replacement; you will have to invest time and efforts to do the conversion.

Thanks James, I meant "Use of Sql proc calls from .NET" as CLR stored procedures only , and time is huge concern over this ... Any other options Gurus ?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-27 : 09:07:57

I don't know of anything that is a drop-in replacement for xp_cmdshell. Any option requires significat amount of work and testing, as you are finding out.

rajuk13
Starting Member

3 Posts

Posted - 2013-09-27 : 10:53:36

quote:
Originally posted by James K

I don't know of anything that is a drop-in replacement for xp_cmdshell. Any option requires significat amount of work and testing, as you are finding out.

Hi James,
Just came to know that even CLR is not permitted in our org...