SQL Server Agent Jobs and GUI Automation

Scenario

An application vendor has provided an application database maintenance utility that only has a GUI interface. You want to automate this maintenance activity as a SQL Server Agent job.

The best solution would probably be to tell the vendor to add a command line interface to the utility to facilitate unattended use. Unfortunately the vendor may be unable or unwilling to achieve this.

A Solution

1. Use AutoIt to script and compile an executable that will perform unattended execution of the ute
2. Call the AutoIt-generated exe from a SQL job using CmdExec

Step 1 is challenging, particularly if the vendor's utility is complex or poorly documented. Step 2 can be difficult to implement due to environment constraints.

With AutoIt you have the choice of

• installing the AutoIt environment and running the .au3 script file
• compiling a .exe from the script

In my experience you'll have far less trouble getting the exe approved by change control than requesting deployment of the entire AutoIt installation.

1. AutoIt

AutoIt v3 is the latest available version. The download contains a useful help file and example scripts. The web also has plenty of documentation and forum discussions on most topics you might be interested in. I'll just cover off a few points I thought were important:

• The language is a variant of Pascal
• Line continuation is performed with _
• Carriage return & line feed is represented by @CRLF
• Variable $CmdLine[0] contains the number of command line parameters provided
• ConsoleWrite() can be used to provide feedback and debug info
• The included Au3Info.exe is a handy GUI spy for identifying those much-needed CLASS and INSTANCE values
• Some functions are not suited to unattended use (see Q21 excerpt from AutoIt FAQ below)
• If your script is too fast, add some Sleep(n) statements to let the GUI catch up
• With the WinExists() function, AutoIt is able to handle GUI events that are too fast for a human to intercept or even perceive. Therefore writing a script based on manual user steps may be insufficient to properly control the GUI

Q21. Why my script doesn't work on locked station
A21. On locked station any window will never be active (active is only dialog with text "Press Ctrl+Alt+Del")In Windows locked state applications runs hidden (behind that visible dialog) and haven't focus and active status.
So generally don't use Send() MouseClick() WinActivate() WinWaitActive() WinActive() etc.Instead use ControlSend() ControlSetText() ControlClick() WinWait() WinExists() WinMenuSelectItem() etc.
This way you may have your script resistive against another active windows.and it's possibe to run such script from scheduler on locked Windows station.

2. SQL job

The SQL Server Agent is a service. By default, services do not interact with the desktop. This means your AutoIt exe can launch the utility but will be unable to detect or use any of its GUI elements.

To allow SQL Server Agent to interact with the desktop:

1. Start:Administrative Tools:Services
2. Right-click SQL Server Agent and select Properties
3. Select the Log On tab
4. Select Local System Account, check "Allow service to interact with desktop" and click OK
5. Restart SQL Server Agent (right-click and Restart)

Note the checkbox "Allow service to interact with desktop" is only available for the Local System Account. To use other accounts, they would need to be enabled to "Act as part of the operating system".

Potential problems are around whether Local System is suitable for running the agent service. Local System may not be suitable for other jobs on the same server. Some jobs may need permissions to other servers and it may not be appropriate to grant them to Local System.

A workaround would be to create a separate SQL Server Agent instance on the same server, just for jobs that need to interact with the desktop. However the additional instance will consume server resources.

A minor annoyance: a desktop-interaction-enabled SQL Server Agent will insist on keeping a Command Prompt window open while it's running.