Execute Database Statement

Use the Task Execute Database Statement to run a preconfigured SQL (Structured Query Language) script on a database. This allows you to maintain your database with Ivanti Automation. For example, this allows you to schedule a recurring cleanup Task of the database.

Configuration

Settings tab

  • If you run the SQL script on a Microsoft SQL Server-based Datastore, select in the Database authentication field whether to connect to this Datastore using SQL Server Authentication or Windows Authentication. This field is only enabled when selecting Microsoft SQL Server in the Database type field.
    • When selecting Windows Authentication, specify logins in the format Domain\User name or Domain\Group name.
  • It can be very useful to use Module parameters in this Task. This allows you to make it generic: when you schedule the Task, you will then be prompted for example for the database server, the database name and the login credentials.
  • If you select Use Resource in the SQL script section, the relevant SQL script must be available as a Resource.

Script tab

  • Scripts can be typed in directly.
  • Use the File extension of script field to specify the file extension that Ivanti Automation should use. This extension is used to save the script as a script file in the specified format when the Task is executed. Ivanti Automation will save this file in the temporary folder on the Agent that executes the Task.
  • Use the Open in editor button to open an external editor that is associated with the specified file extension in the File extension of script field. This makes it easier to create complex scripts and troubleshoot existing ones. After creating or editing the script, it will be copied from the external editor to the Script tab. If no associated editor can be found, the script will be opened in Notepad. For more information on how to configure file associations, see http://msdn.microsoft.com/en-us/library/windows/desktop/cc144175%28v=vs.85%29.aspx.
  • Log and error files are included in the detailed Job history.
  • You can run SQL scripts on any database that is supported by Ivanti Automation.
  • Per Task, your SQL script can perform actions on multiple databases, as long as they are located on the same database server.
  • When executing this Task, log files will be generated. Before the Agent uploads these output log, error log or grabbed log files to the Dispatcher and make them available, the Agent will first compress the file if it has a size of less than 100 MB. In the following situations, the compression of that file will not take place. As a result, the file will not be uploaded:
    • The file size cannot be determined
    • The file is empty
    • The file is larger than 100 MB
  • The Job history will report if a file could not be uploaded because the file size could not be determined, or if the file size was larger than 100 MB. Empty files will not be reported. If a file cannot be uploaded, the outcome of the Task is not affected: the Task will still complete successfully, but the log file(s) that were not uploaded are not available in the Console.
  • The Script tab has a limit of 64KB. If you use an external editor to edit a script, Ivanti Automation will disregard any characters that exceed this limit.