An executable file is not being run from store procedure

SYSTEM:
Microsoft Window server 2003 service pack 2 Enterprise Edition
Intel (R) xeon
24 gb ram
platform nt intel x86
Maximum server memory in mb = 20480

SQL SERVER 2005 standard edition

When physically run an exe file it takes one minute to start (acceptable).
but the same exe is not running even after 15 minutes time when run from
sql server store procedure.
Here is the procedure

ALTER procedure [dbo].[xpcmd_hoponline_exefile] as
declare @bcpCommand varchar(200)
SET @bcpCommand = 'C:\Progra~1\hponline\HPOnline.exe'
EXEC master..xp_cmdshell @bcpCommand
MehramAsked:
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.

MehramAuthor Commented:
I created a job for the same.

The job is running and running
Job is constantly running
0
MehramAuthor Commented:
There has to be some rights / service issue
because I created a job to run notepad.exe and it is also running constantly

sql server service and sql agent both running under local system account
0
Jason Yousef, MSSr. BI  DeveloperCommented:
HI,

xp_cmdshell is generally used to execute
operating-system command. It is not designed to start applications which have a user interface.

Please read this...
http://www.sqlservercentral.com/Forums/Topic19584-102-1.aspx

do more research...or you can add your .exe into a .bat batch  and let sql execute it.

what do you need to run anyway? I believe notepad.exe is just a test, right?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Aneesh RetnakaranDatabase AdministratorCommented:
You can call an executable as part a job step in sql server, you can choose 'PowerShell' or 'OperatingSystem(CmdExec)' instead of a TSQLScript type.
0
MehramAuthor Commented:
Hi huslayer

I believe notepad.exe is just a test, right?
yes its a test

http://www.sqlservercentral.com/Forums/Topic19584-102-1.aspx
<<f you start an application like Calc which has a user
interaction via xp_cmdshell, xp_cmdshell will start your application, pass the control to your application, and wait indefinitely until it ends. Since
you cannot close such an application manually while it is running in background, xp_cmdshell will never regain control from your application.
>>
In fact, I want exactly as this. The user will only run the executable
0
MehramAuthor Commented:
<<You can call an executable as part a job step in sql server, you can choose 'PowerShell' or 'OperatingSystem(CmdExec)' instead of a TSQLScript type. >.

I did the same for notepad.exe  with cmdExec    (powershell option is not there).

I will once again ask if there is any rights issue.
0
MehramAuthor Commented:
<<.or you can add your .exe into a .bat batch  and let sql execute it.)
how
can we run a .bat without xp_cmdshell
0
MehramAuthor Commented:
Dear Experts

hponline.exe has been set to run as administrator  in server machine

sqlagent ans sql server service both run under local system account

Is this the cause
0
Ryan McCauleyEnterprise Analytics ManagerCommented:
As others have said, the reason you see the application "hang" is because it's running properly, but in a different user session (the session of the SQL Service/Agent account). As such, you can't see the interface, and you can't interact with it or close it.

If you check Task Manager, you'll see it running in the list of processes, but you'll see it running under another user. For you to see the application, it needs to run under your user session. When you execute it from inside SQL Server, SQL has no way to launch that in your user session, so it launches it in its own session, and you never see it. For example, what happens if the stored procedure executes on a schedule, and you don't happen to be logged on to the computer at the time? Where would it launch it if not in the sql agent account session?

In short, there's no way to do what you're aksing - you can't run an EXE from a stored procedure (or a sql agent job) and have the interface appear in your session, where you can see it.

I hope this clarifies things - it's a common misunderstanding.
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.