MS SQL - Running Executable From Stored Procedure

Posted on 2014-07-17
Medium Priority
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

Mark Wills earned 2000 total points
ID: 40204508
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 : http://msdn.microsoft.com/en-us/library/5czye81z(v=vs.90).aspx and read the last post in http://social.msdn.microsoft.com/Forums/vstudio/en-US/9e13e54a-886b-4f72-b41e-3bf558d8d0d3/start-16-bit-exe-from-sql-server-clr-stored-procedure-vb?forum=clr 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 ?

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

864 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