Scripting - SQL

Scripting - SQL

The SQL Script workflow object is used to execute SQL against the HelpMaster (or another) database.

workflow script

Workflow script

Silent workflow object This workflow object runs silently, meaning it has no user interaction.  Silent workflow object are displayed with this icon.

Use

By default, any SQL script is executed against the HelpMaster database.  To reference other databases, use the "Use" statement in your SQL, or construct your own database connection string within your SQL.

SQL Syntax should be Microsoft SQL Server only.

Use the Tags button to insert tags into your SQL script.  These tags represent HelpMaster entity IDs that are within the context of the running workflow.  If you require other information that is not accessible via the tags, consider expanding your SQL to look up this information based on the tags that you do have.  Remember that you can also call stored procedures using this workflow object. 

The following tags can be used in a SQL, or PowerShell script

  • <<JobNumber>> :  The job number that the workflow is running in
  • <<ClientPKID>> : The primary client ID of the job that the workflow is running in
  • <<SitePKID>> : The Site ID of the job that the workflow is running in
  • <<AssetPKID>> : The primary asset ID of the job that the workflow is running in
  • <<LoggedOnUserPKID>> : The User ID of the staff member that is currently logged in

Variables can also be used in the script.  To insert a pre-defined variable, start typing double angular brackets "<<", and all of the available variables will be displayed in a pick-list.  Choose the variable you want to inert its place-holder.  It's value will be substituted for the place-holder when the script is run.

Ensure that all SQL is valid, and no malicious, or ruinous script is ever run against your HelpMaster database.  This feature should only be configured by appropriate personnel who understand SQL and the HelpMaster database schema.   An incorrectly generated SQL statement can do irrepairable damage to your HelpMaster database.

All SQL/Scripting features within HelpMaster are governed by a security setting.  Only staff members that belong to an Application Security Rolethat has this setting enabled will be able to use, modify and run scripts.

See Also

Decision - Script / Data based

Execute Powershell as part of workflow

Workflow Objects

Variables - Workflow

Control Powershell use via application security roles

System Event Log

System Administration

Security Setting to enable/disable SQL