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.
Go to Top of Page

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 ?
Go to Top of Page

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.
Go to Top of Page

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... ..any other alternative please ?
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-27 : 11:44:30
I don't know of any other feature/facilities in SQL Server that would allow you to run OS commands and access OS objects from T-SQL.
Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2013-10-24 : 14:23:28
PowerShell might be an option.



-Chad
Microsoft Certified Master SQL Server 2008
Go to Top of Page
   

- Advertisement -