MS SQL - Running Executable From Stored Procedure

Hi,

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 
GO
RECONFIGURE

EXEC sp_configure 'xp_cmdshell', 1;
Go
RECONFIGURE;
Go

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
GO
RECONFIGURE

EXEC sp_configure 'xp_cmdshell', 0;
Go
RECONFIGURE;
Go

Open in new window


Is there a better way to do this?

Thanks, Greg
spen_langAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
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 ?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.