Many Users have the need to Automate using Microsoft Office Products. This Article will outline some troubleshooting steps when trying to automate these Jobs within JAMS. While this Article will speak about Excel, it can be applied to other Microsoft Office Products with minimal tweaks.
The Job executes but never finishes in JAMS.
When creating an Excel Job in JAMS, it may be necessary to run these Jobs in Interactive Mode. Many times when these Excel Jobs run they open Interactive Windows on the Server where they are executing.
- Create an Interactive Execution Method in JAMS
- Ensure Interactive Services Detection is Started
- Resolve Jobs that fail with a Permissions or File not Found Error
Create a New Interactive Execution Method in JAMS
- Select Execution Method from the Shortcuts menu in JAMS.
- Select + in the Command Bar to open the Add an Execution Method dialog.
- Enter the Name of the new Execution Method. For example, if the original Execution Method name was "Command," we might name the new Execution Method "Command_Interactive." Select the corresponding Base Method which, in this example, is "Command." Check Edit After Adding.
- Once you click Ok, the Method Name dialog opens. Click the Properties tab, scroll to Options, and check Interactive. Click the Save and Close icon.
- It may be necessary to Refresh Execution Method view to see the new Execution Method.
- Set the newly created Execution Method on your Interactive Microsoft Office Job(s). You can use the newly created Execution Method in the Job Creation Wizard when creating new Jobs or set the Execution Method on an existing Job. To edit the Execution Method of an existing Job, navigate to the Job Definitions, and click the Properties tab. Select the new Execution Method name from the Execution Method drop-down.
Ensure Interactive Services Detection is Started
Now that the interactive Execution Method has been created and set on the Job, we need to ensure that the Interactive Services Detection service is turned on.
- Open Windows Services on the server where the Job needs to run. If this is a remote server, RDP into the server.
- Find the Interactive Services Detection service. If this service is not running, start the service.
- Users with Microsoft SQL 2012 will now see an error message. Users who do not experience this error may skip to step 9. This error appears because the Service is disabled by default in Microsoft Server 2012. We will resolve this error in the next steps.
- To resolve the error, open Regedit.
- Navigate to
- Change the value of NoInteractiveServices from 1 to 0.
- Reboot the Server.
- Repeat steps 1 and 2.
- The Job can now be executed in JAMS. If the Job does not complete, it may require user input. Go to the server the Job is executing on and view the toolbar. If you see the Interactive Detection Service blinking, click on it and select View Message. This will allow you to see if the program may be prompting for any user input.
Resolve Jobs that fail with a Permissions or File not Found Error
If the Job runs but fails with a permissions error or cannot find a file, it may be necessary to modify the DCOM permissions on the server where the Job is executing.
- On the server where the Job is executing, open a Run box and type in
dcomcnfg.exeto open Component Services.
- Navigate to DCOM Config, located at Component Services>Computers>My Computer>DCOM Config.
- In the middle pane, right-click Microsoft Excel Application and select Properties to open the Microsoft Excel Application Properties window.
- On the Identity tab, select This User, then enter the information for the JAMS User that is executing the JAMS Job.
- Navigate to the Security tab. Note there are three permissions sections: Launch and Activation Permissions, Access Permissions, and Configuration Permissions.
- For each of the three permissions sections, select Customize and then Edit to open the Permission Properties. Give the JAMS user that is executing the Job Full Permissions in each section.
NOTE: If the JAMS user is a local administrator on the server where the Job is executing, this step is not necessary.
- The Job should now complete successfully.