An executable file is not being run from store procedure

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
Who is Participating?
Ryan McCauleyConnect With a Mentor Data and 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.
MehramAuthor Commented:
I created a job for the same.

The job is running and running
Job is constantly running
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Jason Yousef, MSConnect With a Mentor Sr. BI  DeveloperCommented:

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...

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?
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.
MehramAuthor Commented:
Hi huslayer

I believe notepad.exe is just a test, right?
yes its a test
<<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
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.
MehramAuthor Commented:
<<.or you can add your .exe into a .bat batch  and let sql execute it.)
can we run a .bat without xp_cmdshell
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
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.

All Courses

From novice to tech pro — start learning today.