xp_cmdshell with dbsvc

[chris_keating]

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 🙂

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