SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Other SQL Server 2012 Topics
 Execute OS commands instead of Xp_cmdshell
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rajuk13
Starting Member

3 Posts

Posted - 09/27/2013 :  07:45:32  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3328 Posts

Posted - 09/27/2013 :  08:10:18  Show Profile  Reply with Quote
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 - 09/27/2013 :  08:42:31  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3328 Posts

Posted - 09/27/2013 :  09:07:57  Show Profile  Reply with Quote
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 - 09/27/2013 :  10:53:36  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3328 Posts

Posted - 09/27/2013 :  11:44:30  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 10/24/2013 :  14:23:28  Show Profile  Visit chadmat's Homepage  Reply with Quote
PowerShell might be an option.



-Chad
Microsoft Certified Master SQL Server 2008
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000