MS SQL - Running Executable From Stored Procedure

Posted on 2014-07-17
Last Modified: 2014-07-18

I need to write a SSRS report but before the report runs data must be collected from an import file using an executable.

I have read up a little on this and have found out that it is possible to run an executable from inside a stored procedure using xp_CmdShell

By default xp_CmdShell is disabled so I have to run this script to enable it:

EXEC sp_configure 'show advanced options', 1 

EXEC sp_configure 'xp_cmdshell', 1;

Open in new window

Is OK to then disable each option after the EXE has ran or will this cause problems as I am installing and uninstalling constantly:

EXEC sp_configure 'show advanced options', 0

EXEC sp_configure 'xp_cmdshell', 0;

Open in new window

Is there a better way to do this?

Thanks, Greg
Question by:spen_lang
    1 Comment
    LVL 51

    Accepted Solution

    It is a bit of a pity to have to run a separate exe to import the data instead of getting SQL to import for you.

    But guess that has to happens for a variety of reasons...

    You can put the enable xp_cmdshell, exec xp_cmdshell, disable xp_cmdshell all in the one procedure.

    If you are handy with programming languages, and/or the author of that external EXE you can entertain the idea of creating a CLR and write equivalent code as a CLR stored procedure : and read the last post in for a comprehensive example of launching an exe via CLR (it is the launch + wait for IE example).

    So, there are a few choices...

    1) Do as you are doing all in a single Stored Procedure (possibly launching the report, or, making the SSRS call the procedure)
    2) Get SQL to do the actual import itself
    3) Write a CLR proc

    I am a bit open minded about which one might be best, and some purists will resist the first choice, but at the end of the day, you need to import that data and to manage the code / processes that enable it. So, your own comfort levels are important.

    The window of opportunity to infiltrate security are small, and you could probably build in some extra restrictions (such as "EXECUTE AS <priv user>" )

    Have you got the processes working manually ?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Hi All, I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to …
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now