xp_cmdshell with dbsvc

[Baron]

I have difficulty using the xp_cmdshell to start an .exe file or a .batch file, which starts an exe, which in turns is a desktop application connecting to the database. so, from dbisql I write xp_cmdshell 'C:\\temp\\startreports.bat' Actually I have this problem only when I start the DBSRV10 from within a service (dbsvc).

Our startreports.exe is on environment and registry dependant, so that I start the service with a specific user (which has an appropriate profile for the exe) so it looks something like:

dbsvc -a WIN_USER -p WIN_PWD -s Manual -sd "SOME_DESCRIPTION" -t network -w SVC_NAME "C:\\Program Files (x86)\\SQL Anywhere 10\\win32\\dbsrv10.exe" -xs http(port=8888) -n SRV_NAME -c 768M "C:\\TEST\\DB1.db"

I dont see any other possibility to change the service (could the -i change something) (-i = allow service to interact with desktop), but I had always difficulty to add this option to the dbsvc!!

[chris_keating]

If you application that you are launching in xp_cmdshell has a GUI, it cannot be started in the context of a service in Windows Vista and later. This is likely why the -i option will not work.

[Baron]

Yes it is an .exe with GUI. I tried to start the exe directly from xp_cmdshell or to call a .bat file which in turn starts the .exe and in both cases no success!!

[Breck_Carter]

You said "it is an .exe with GUI"

Chris said "it cannot be started in the context of a service in Windows Vista and later"

[Baron]

Should then I understand, that xp_cmdshell can execute any external program / OS commands with one single exception: The exception is that the DB is started as service, and the executed program has GUI interface?

[VolkerBarth]

IMHO that has not really much to do with xp_cmdshell or SQL Anywhere but with security restrictions in Windows:

See that MS article on how to build "Interactive Services" (and why that needs particular consideration).

[Baron]

Thanks for the explanation, but the question again, why I cant create the service with -i option? According to the documentation this is a valid option for (allow service to interact with desktop), but once I use it with dbsvc.exe, then I get an error crating service (invalid parameter).

[VolkerBarth]

Well, you are using SQL Anywhere 10, and when that was published most Windows OS versions would allow the -i parameter. I'm not sure whether Vista was even published then...

Note that for v17, dbsvc's -i parameter is no longer available because v17 does not support any Windows OS that allows service interaction, it requires Windows 7 and above.

[chris_keating]

If you are using Windows Vista (and server equivalent) or later, that feature will not be available.

[Baron]

OK, so as a summary, if I have windows 10, then I can't call/srtart an .exe with GUI using xp_cmdshell if the DB is running as Windows-Service.

[VolkerBarth]

Well, unless you find a tool that is able to do so. AFAIK, such general Windows topics ("How to start/run a GUI from a service?") are discussed in StackOverflow, SuperUser and the like...

[johnsmirnios]

Somewhat correct. You can call/start the .exe but the GUI will not show on the desktop. That's an OS security restriction, not a limitation of SQLAnywhere.

[Breck_Carter]

V17 no longer supports dbsvc -h either 🙂

[VolkerBarth]

Has -h been ever an official option? It is not listed for v8, v12, v16 and v17...

[Breck_Carter]

Never documented, only supported on the Lada Handcrank Edition of SQL Anywhere 🙂

[VolkerBarth]

Ah, looks like v5.5.05 🙂

[VolkerBarth]

So far we have tried to explain the problem. If you really need a SQL Anywhere server running as a service to display a GUI, you will need some kind of "man in the middle" in my understanding. That is because a service uses an invisible "window station" whereas a visible GUI must be run within the one and only "interactive window station".

Let's use a very silly example: Say, your SQL Anywhere server service would do regular backups of its database but want to ask the user to confirm that. Therefore it would need to display a simple message box awaiting a "yes" or "no" answer.

1. AFAIK, you would need a process running in the session of the logged-in interactive user (say, automatically started via "AutoStart"). That process would be silently waiting for an event triggered by the database service. One means would be to check for modified database contents but I would prefer a basic OS IPC facility like a named Win32 event. So that "waiter" would just use one of the Win32 wait functions to wait on the event to be set.

2. When the database server wants to ask for a backup, it would "do something" to signal that event. That's (rather easily) doable via an external C function that maintains and sets the according event. (FWIW, that's something we use ourselves to synchronize processes - for different reasons, apparently...). So some database code calls that external function which in turn sets the event. (Alternatively, you could use xp_cmdshell to call an application that sets such an event.)

3. Now the "waiter" process in the interactive session is notified that the event is set and therefore displays the GUI, either as part of its own process or by starting a GUI app. As the "waiter" runs within the interactive window station, its output and that of processes it has started are visible for the logged-in user. So a message box is displayed, and the user chooses "yes" or "no".

4. In this sample, the database service needs to get the GUI's result. So there must be another IPC in the opposite direction: The database code would need to wait for the response, either by waiting for some database modification (say, the GUI would change a database row based on the user's input) and/or by another event set by the "waiter" that the GUI has closed. As a consequence, the database server would now do a backup or skip that task. (As stated, it's a silly sample...:)).

5. The waiter would reset the first event and would return to its "waitning" state and wait until the database server sets that event again.

---

I have not used code like that to make a SQL Anywhere service display a GUI, however we have used IPC to synchronize tasks within SQL Anywhere with other processes for years, and that has worked fine.

[Baron]

an interesting alternative is the SYSTEM statement, which can start an GUI from within DBISQL even if the DB is started as Service. The drawback is that this statement can only be used within dbisql, and can not be called within a procedure.

[Breck_Carter]

Have you tried the Windows START command from a call to xp_cmdshell?

[VolkerBarth]

The question is what process runs the xp_cmdshell command. If the database server running as a service does start that command, you are pretty much still in the invisible window station and cannot show a GUI to the interactive user. That's my limited understanding, of course...

[Baron]

Yes I tried, it does the same thing, except that it starts the program in a new dos-window

[Breck_Carter]

So you are saying that you CAN run START from xp_cmdshell in a procedure running in a service, AND get a GUI showing?

FWIW START has a lot of options / capabilities, some of them [cough] magically confusing 🙂

[Baron]

No, I didnt say that. I said the SYSTEM statement can do that (start GUI application even if the DB is started as service).

[Baron]

I know that it took long time to discuss this subject, and I understand that it goes on the OS to allow or not allow desktop interaction for a service. I got the answer thankfully since last week, and with my new answer wanted just to mention the SYSTEM statement (which was for me new)

[VolkerBarth]

wanted just to mention the SYSTEM statement (which was for me new)

FWIW, I wasn't aware of that command either, so thanks for that:)

[Breck_Carter]

New to me too, and embarrassing... because it dates back to V5.5 at least:

 User's Guide 
   Part VI. SQL Anywhere Reference
     Chapter 43. Watcom-SQL Statements
      SYSTEM statement
Function
To execute an operating system command from within ISQL.
Syntax
SYSTEM [ operating-system-command ]
Usage
ISQL (DOS and QNX only).