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

asked 01 Apr '19, 06:20

Baron's gravatar image

Baron
2.1k134146174
accept rate: 46%

1

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.

(01 Apr '19, 07:36) Chris Keating

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

(01 Apr '19, 07:43) Baron
Replies hidden

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"

(01 Apr '19, 14:04) Breck Carter
Comment Text Removed

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?

(02 Apr '19, 03:02) Baron

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

(02 Apr '19, 03:50) Volker Barth

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

(02 Apr '19, 04:50) Baron
2

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.

(02 Apr '19, 06:13) Volker Barth
1

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

(02 Apr '19, 07:09) Chris Keating

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.

(02 Apr '19, 07:39) Baron
Replies hidden

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

(02 Apr '19, 08:16) Volker Barth
1

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.

(03 Apr '19, 01:24) John Smirnios

V17 no longer supports dbsvc -h either :)

(03 Apr '19, 09:01) Breck Carter

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

(03 Apr '19, 09:15) Volker Barth
1

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

(03 Apr '19, 10:41) Breck Carter

Ah, looks like v5.5.05 :)

(03 Apr '19, 11:55) Volker Barth
showing 4 of 15 show all flat view

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.

permanent link

answered 03 Apr '19, 03:42

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 03 Apr '19, 03:47

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.

permanent link

answered 09 Apr '19, 10:00

Baron's gravatar image

Baron
2.1k134146174
accept rate: 46%

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

(09 Apr '19, 10:41) Breck Carter
Replies hidden

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

(09 Apr '19, 10:51) Volker Barth

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

(09 Apr '19, 12:08) Baron

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 :)

(09 Apr '19, 13:57) Breck Carter

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

(09 Apr '19, 16:59) 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)

(09 Apr '19, 17:03) Baron
Replies hidden

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:)

(10 Apr '19, 03:24) Volker Barth

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

(10 Apr '19, 15:12) Breck Carter
showing 2 of 8 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×12

question asked: 01 Apr '19, 06:20

question was seen: 1,680 times

last updated: 10 Apr '19, 15:12