SQL Scripting Commands

From QPR ProcessAnalyzer Wiki
Revision as of 09:04, 1 April 2020 by Ollvihe (talk | contribs)
Jump to navigation Jump to search

This page lists QPR ProcessAnalyzer commands that can be used in the SQL scripts. Each command precedes one or two SQL queries, which sets parameters for the command or defines the data used by the command.

Load Operations

--#CallWebService

Extracts data via Web Service. This command takes one SELECT query as parameter.

Query

Configures the command using a SELECT statement returning two columns: the first column is for a key and the second one is for a value of that key. The values in both the key column and in the value column are of type NVARCHAR. The supported keys for this command are:

'Address'
Defines the URI of the service to call. Mandatory.
'Method'
Defines the HTTP method to use for the call. Must be any of the following: GET (default), POST, PUT, DELETE. Optional.
'Body'
Defines the message body text to send to the service. Default value is empty. Optional.
'Encoding'
Defines the encoding method to use. The supported options are listed in https://msdn.microsoft.com/en-us/library/system.text.encoding%28v=vs.110%29.aspx. Default value is UTF8. Optional.
'Timeout'
Number of milliseconds to wait before the request times out. Default value is 60000. Optional.
'ExecuteInClientSide'
Defines whether the web service call is executed in the client side or in the server side when using QPR ProcessAnalyzer Pro. This parameter is used when there is no server connection available, for example. TRUE or any other Integer than "0" = the import query is executed in the client side. FALSE or "0" = the import query is executed in the server side. Supports only data table as the import destination. Default value is FALSE. Optional.
'DefaultNetworkCredentials'
Optional. Defines the possibility to use default network credentials in web service calls:
1 = use the default network credentials.
0 = don't use the default network credentials.
If CallWebService command is run in the server side (ExecuteInClientSide=False), the default network credentials can be used only if in the server configuration AllowForwardingNetworkCredentials is true (it is false by default). Otherwise, if the CallWebService command is run in the client side (ExecuteInClientSide=True), the default network credentials can always be used.
'CatchOperationExceptions'
Optional. Defines whether to stop the script execution or to continue to run the script from the next statement if an exception occurs when running the script:
1 = don't stop execution of the script, continue running the script from the next statement.
0 = stop execution of the current script and show the exception.
The following script variables will be set and are shown in the script log:
@_ExceptionOccurred If there was an exception, then this value is 1, otherwise 0. INT.
@_ExceptionType If there was an exception, shows the C# class name for the exception, NVARCHAR(MAX), otherwise NULL.
@_ExceptionMessage If there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
@_ExceptionDetails If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.
<other parameters>
All the rest of the passed parameters not listed above are added as extra headers to the request. Optional.

Result

The result of the request is passed to the script following the CallWebService operation in the following variables:

@_ResponseText The response text received from the remote server. If there was an error in processing the request, this will contain the received error message. NVARCHAR(MAX).
@_ResponseStatusCode The numeric status code received from the remote server. INT.
@_ResponseSuccess True only if the request returned status code that represents a success. BIT.

See examples at the CallWebService Script Examples page.

--#CreateFilter

Creates a new filter with the specified parameters. Returns the id of the newly created filter in SQL dynamic variable @_CreatedFilterId.

Configures the command using a SELECT statement returning two columns: the first column is for a key and the second one is for a value of that key. The values in both the key column and in the value column are of type NVARCHAR. The supported keys for this command are:

'ParentFilterId'
Id of the filter/view for which the new filter is applied. Mandatory.
'FilterType'
Type of the additional filter to apply. Supported values are:
0 = Exclude Selected
1 = Include Only Selected
2 = Exclude Selected Event Types
3 = Include Only Selected Event Types
4 = Exclude Selected Cases
5 = Include Only Selected Cases
6 = Recalculate
7 = Include Only Selected Case Attributes
8 = Exclude Selected Case Attributes
9 = Include Only Selected Event Attributes
10 = Exclude Selected Event Attributes
11 = Remove Filter Rules
'<Object selection related parameters>'
The object selection related parameters are supported by all the other filter types except Recalculate. Possible parameters and their values and value types:
SelectedActivities: Activity/event type ids separated by commas.
SelectedCases: Case ids separated by commas.
SelectedTransition: Transitions/flows separated by commas.
SelectedVariations: Variations separated by commas.
SelectedCaseAttributeTypes: Case attribute Ids separated by commas
SelectedCaseAttributeValues: Case attribute values separated by commas.
SelectedEventAttributeTypes: Event attribute Ids separated commas.
SelectedEventAttributeValues: Event attribute values separated by commas.
SelectedEventAttributeValues: End event attribute values separated by commas.
SelectedCounts: Variation event type counts separated by commas.
Event type trends selection parameters:
SelectedEventTypeTrends: Comma-separated combinations of event type id and trend period ids in the format "<Event type id>.<period id>[.<period id>]*".
SelectedEventTypeTrendStartDate: Selected start date in ISO format (e.g. 2016-01-28).
SelectedEventTypeTrendPeriodLevel: Selected period level, possible values are 0 = Day, 1 = Week, 2 = Month, 3 = Quarter, 4 = Year.
Case attribute trends selection parameters:
SelectedCaseAttributeTrends: Comma-separated combinations of case attribute value and trend period ids in the format "<Case attribute value>.<period id>[.<period id>]*".
SelectedCaseAttributeTrendStartDate: Selected start date in ISO format (e.g. 2016-01-28).
SelectedCaseAttributeTrendPeriodLevel: Selected period level, possible values are 0 = Day, 1 = Week, 2 = Month, 3 = Quarter, 4 = Year.
SelectedCaseAttributeTrendAttributeType: Case attribute id.
SelectedCaseAttributeTrendCaseTimeStampType: Case time stamp type.
SelectedCaseAttributeTrendTimeStampEventType: Event type id used for calculating case time stamps.
SelectedCaseAttributeTrendTimeStampAttribute: Case attribute used for calculating case time stamps.
Event attribute trends selection parameters:
SelectedEventAttributeTrends: Comma-separated combinations of event attribute value and trend period ids in the format "<Event attribute value>.<period id>[.<period id>]*".
SelectedEventAttributeTrendStartDate: Selected start date in ISO format (e.g. 2016-01-28).
SelectedEventAttributeTrendPeriodLevel: Selected period level, possible values are 0 = Day, 1 = Week, 2 = Month, 3 = Quarter, 4 = Year.
SelectedEventAttributeTrendAttributeType: Event attribute id.
Path selection parameters:
SelectedActivityPaths: Activity paths separated by commas.
Direction: The value is "1" if the "Predecessors" Direction was selected in the Path Analysis, otherwise "0".
AbsolutePath:
RootEventType: Root event type id.
CombinePaths:
Duration selection parameters:
DurationTimeSlots: Durations separated by commas.
Granularity: The duration time unit used in the selection. See the "Group By" entry in the Duration Analysis page for reference.
MaximumDuration: The duration limit in the selection. See the "Duration Limit" entry in the Duration Analysis page for reference.
'FilterRuleIds'
A comma-separated list of filter rule ids. Mandatory for RemoveFilterRules FilterType.
'IncludeSelection'
Boolean value. If this is TRUE, then the current selection from the parameters of the script run is copied to the parameters of the command (only if similar setting doesn't already exist in the command parameters). Default value is FALSE.
'FilterName'
Name of the new filter. By default, the name is generated automatically by QPR ProcessAnalyzer.
'PublishMode'
Publish mode for the created filter. Possible values are "Public" and "Private". Default value is "Private".
'CaseSampleSize'
The case sample size for the created filter. Optional. If missing, the sample size is inherited from the parent filter, if CaseSampleSize=0, then all cases are included into the filter, if CaseSampleSize>0, then this value is used as case sample size in the created filter.

See examples at the CreateFilter Script Examples page.

--#Exit

Stops the execution of the script and gives a message to the user. This command takes one SELECT query as its parameter.

Configures the command using a SELECT statement returning two columns: the first column is for a key and the second one is for a value of that key. The values in both the key column and in the value column are of type NVARCHAR. The supported keys for this command are:

'Exit'
Defines whether to stop the script execution:
1 = stop execution of the current script and call the script defined by the RunScriptId parameter if it is given.
0 = if a value for the RunScriptId parameter is given, pause the execution of the current script and call the given script, then resume running the current script after the given script ends. If a value for RunScriptId is not given, do not pause or stop execution of the current script.
'MessageText'
Text to be shown to the user after the script execution is finished if the script finished because of the Exit command, i.e. when Exit=1. The default value is "Script execution finished.", which is shown also when the script finished normally, i.e. when Exit=0. The text is also written to the script log.
'RunScriptId'
Optional. The Id of the script to be run. Can be empty. Note that the script can call itself, so be careful not to create a looping script.
'CatchOperationExceptions'
Optional. Defines whether to stop the script execution or to continue to run the script from the next statement if an exception occurs when running the script:
1 = don't stop execution of the script, continue running the script from the next statement.
0 = stop execution of the current script and show the exception.
The following script variables will be set and are shown in the script log:
@_ExceptionOccurred If there was an exception, then this value is 1, otherwise 0. INT
@_ExceptionType If there was an exception, shows the C# class name for the exception, NVARCHAR(MAX), otherwise NULL.
@_ExceptionMessage If there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
@_ExceptionDetails If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.

See examples at the Exit Script Examples page.

--#GetAnalysis

Creates an analysis from the data which the preceding SQL statements given as parameters provide. This command can take several queries, one for every analysis to be performed. These queries and analysis results are independent from one another. Contains information about the scripts that are running and have been run.​

Configures the command using a SELECT statement returning two columns: the first column is for a key and the second one is for a value of that key. The values in both the key column and in the value column are of type NVARCHAR. The supported keys for this command are:

'<Analysis Parameter>'
See QPR ProcessAnalyzer Analysis Parameters for a list of supported analysis parameters in QPR ProcessAnalyzer.
The --#GetAnalysis command supports the following analysis types:

0.Flowchart Analysis

Draws the flowchart of model defined in parameter​

1.Variation Analysis in the Chart Mode​

Opens Variation analysis of the model defined​

3.Path Analysis​

Opens Path analysis of model defined​

4.Event Type Analysis in the Chart Mode​

Opens Event Type analysis of model defined​

5.Case Analysis Prints the detailed list of cases and attributes of defined model​

6.Event Analysis​

Prints the detailed list of Events from defined model​

7.Event Type Analysis in the Table Mode​

Opens Event Type analysis of model defined​

8.Variation Analysis in the Table Mode​

Opens Variation analysis of the model defined​

9.Duration Analysis​

Opens Duration analysis of the model defined​

10.Profiling Analysis​

Opens Profiling analysis of the model defined​


11.User Report​

Opens a detailed list of users in server​

12.Operation Log Analysis​

Opens the operation log of ProcessAnalyzer​

13.Flow Analysis​

Opens Flow analysis of the model defined​

14.Influence Analysis​

Open Influence analysis of model defined, with defined parameters​

18.Data Table Analysis​

Reads a data table from SQL server and stores it in temporary table​

21.Model Report​

Opens a detailed list of models in server​


22.Project Report​

Opens a detailed list of projects in server​


23.Data Table Report​

Opens a detailed list of data tables in server​


24.Script Report​

Opens a detailed list of scripts in server​

25.RunScript​

Starts a script (used in UI)​


26.Event Type Analysis in the Trends Mode​

Opens Event Type analysis of model defined​

28.Profiling Analysis in the Trends Mode​

Opens Profiling analysis of the model defined​


29.Profiling Analysis in the Trends Mode​

Opens duration influence analysis of model defined​

30.Filter Report​

Opens a detailed list of filters in a model defined​

31.Filter Rule Report​ Contains information about the filter rule in filter defined​

32.Script Log Report​

33.Expression Analysis​


Note that for the analysis types Model Report, Project Report, Data Table Report and Script Report, the information related to deleted models/projects/data tables/scripts is not shown by default but can be configured with parameters to be shown. For more information, see the parameters 'GetAll', 'IncludeDeletedProjects' and 'DeletedModelsOnly' in the list of analysis parameters.
'TargetTable'
The temporary table to which the analysis is to be stored. When the TargetTable parameter is used, the "Table" result type of the ForceAnalysisResultType parameter is also automatically used. If the specified temporary table already exists in the database then its contents are deleted before storing analysis.
'Show'
Optional. If TRUE or 1, the analysis is opened after the script is run. If the Show parameter is set to TRUE or 1 and the TargetTable parameter is used in the same GetAnalysis command, the analysis result is stored in the target table in tabular format and QPR ProcessAnalyzer Excel Client shows the analysis result in the normal format.
'Title'
Optional. The title for the Excel sheet created when Show is TRUE or 1. Default value is the name of the analysis type.
'SheetName'
Optional. The name of the Excel sheet created when Show is TRUE or 1. Default value is the name of the analysis type.
'PivotSheetName'
Optional. The name of the Excel sheet to be created. The sheet will contain a pivot table that includes all columns and rows in the report. The pivot sheet doesn't support drill-down. The pivot sheet has the following additional parameters:
'PivotFilters'
A comma separated list of result sheet column names that should be used in pivot table filters dimension.
'PivotRows'
A comma separated list of row names to be used in the pivot table rows dimension. To sort the results, add the following parameters separated by commas "(SortDirection=<Ascending or Descending>)" and/or "(SortBy=<Sort Field Name>)" to the row name. For example: "Region(SortDirection=Ascending, SortBy="Average of DurationDays"), Country(SortBy="Sum of Cost")"
'PivotColumns'
A comma separated list of column names to be used in the pivot table columns dimension. To sort the results, add the following parameters separated by commas "(SortDirection=<Ascending or Descending>)" and/or "(SortBy=<Sort Field Name>)" to the column name. For example: "Region(SortDirection=Ascending, SortBy="Average of DurationDays"), Country(SortBy="Sum of Cost")"
'PivotValues'
A comma separated list of column names to be used as the pivot table values. The summarize value field can be defined by adding a summarize value parameter to the end of each value name as follows: "(SummarizeBy=<Summarize value tag)". If the parameter is not defined, the Excel default value for summarize is used: "Sum" for numbers and "Count" for other types. The supported summarize value tags are: Sum, Count, Average, Max, Min, Product, CountNumbers, StdDev, StdDevp, Var, Varp. For example: "Duration Days(SummarizeBy="Average"),Cost(SummarizeBy="Sum")"
'PivotTitle'
The title of the pivot sheet to be created. If undefined, the name defined in PivotSheetName will be used.
'CatchOperationExceptions'
Optional. Defines whether to stop the script execution or to continue to run the script from the next statement if an exception occurs when running the script:
1 = don't stop execution of the script, continue running the script from the next statement.
0 = stop execution of the current script and show the exception.
The following script variables will be set and are shown in the script log:
@_ExceptionOccurred If there was an exception, then this value is 1, otherwise 0. INT
@_ExceptionType If there was an exception, shows the C# class name for the exception, NVARCHAR(MAX), otherwise NULL.
@_ExceptionMessage If there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
@_ExceptionDetails If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.
'ScriptId'
Mandatory with the RunScript analysis type. Defines the script that should be run.
'SelectedAnalysisResult'
Used with the RunScript analysis type. Optional. Identifies the sheet name of the child analysis that should be returned to the caller of the RunScript function. If set, and a child analysis with the given sheet name exists, only that child analysis is returned to the caller. If not set or a child analysis with given sheet name does not exist, then the actual analysis object returned by RunScript is returned as the analysis result. In this case, the child analyses of the returned analysis object contain all the analyses that were generated due to the script having the ShowReport or GetAnalysis command with Show parameter having true as value.
'TrendPeriodLevel'
Used with the Event Type Analysis in the Trends mode. Optional. Defines the period level for which trends are shown. Possible values are "Day", "Week", "Month" (Default), "Quarter", and "Year".
'TrendMaximumCount'
Used with the Event Type Analysis in the Trends mode. Optional. Defines the maximum number of trend columns to show.
'ShowAllCases'
Used with Case Analysis. Optional. When this parameter is set to TRUE or 1, the current filter is applied (including sampling, filter rules etc.) but the result will contain all cases for the model. Then, if a case in the result has no events (originally or due to the applied filter, including sampling), the case start date is NULL and the case end data is NULL, and case duration is zero. The same case attributes are shown also for the cases that do not have events as those cases that have events. Default value is FALSE.
'ForceAnalysisResultType'
Can be used with all analysis types. Optional. Defines if the analysis result is to be generated in tabular format. Possible values are:
"Normal" = Uses the default result type that is best suited for holding all the information related to the analysis in the most efficient way possible. Default with analysis generation in QPR ProcessAnalyzer Excel client and Web Client.
"Table" = Forces the result type to be a tabular AnalysisRowData object. Default for analysis generation in all other uses than with QPR ProcessAnalyzer Excel client and Web Client. Forcing tabular result for Flowchart and Path analyses causes the whole result object to be converted into XML, after which it is returned in a tabular format by returning a table containing only one row and one column having the converted XML in it. The maximum supported size of the converted XML is 10 MB.
'IncludeSelection'
Can be used with all analysis types. Optional. If TRUE or 1, the current object selection related parameters are copied to the parameters of the command, if similar setting doesn't already exist in the command parameters. The object selection related parameters are:
SelectedActivities: Activity/event type ids separated by commas.
SelectedCases: Case ids separated by commas.
SelectedTransition: Transitions/flows separated by commas.
SelectedVariations: Variations separated by commas.
SelectedCaseAttributeTypes: Case attribute Ids separated by commas
SelectedCaseAttributeValues: Case attribute values separated by commas.
SelectedEventAttributeTypes: Event attribute Ids separated commas.
SelectedEventAttributeValues: Event attribute values separated by commas.
SelectedEventAttributeValues: End event attribute values separated by commas.
SelectedCounts: Variation event type counts separated by commas.
Event type trends selection parameters:
SelectedEventTypeTrends: Comma-separated combinations of event type id and trend period ids in the format "<Event type id>.<period id>[.<period id>]*".
SelectedEventTypeTrendStartDate: Selected start date in ISO format (e.g. 2016-01-28).
SelectedEventTypeTrendPeriodLevel: Selected period level, possible values are 0 = Day, 1 = Week, 2 = Month, 3 = Quarter, 4 = Year.
Case attribute trends selection parameters:
SelectedCaseAttributeTrends: Comma-separated combinations of case attribute value and trend period ids in the format "<Case attribute value>.<period id>[.<period id>]*".
SelectedCaseAttributeTrendStartDate: Selected start date in ISO format (e.g. 2016-01-28).
SelectedCaseAttributeTrendPeriodLevel: Selected period level, possible values are 0 = Day, 1 = Week, 2 = Month, 3 = Quarter, 4 = Year.
SelectedCaseAttributeTrendAttributeType: Case attribute id.
SelectedCaseAttributeTrendCaseTimeStampType: Case time stamp type.
SelectedCaseAttributeTrendTimeStampEventType: Event type id used for calculating case time stamps.
SelectedCaseAttributeTrendTimeStampAttribute: Case attribute used for calculating case time stamps.
Event attribute trends selection parameters:
SelectedEventAttributeTrends: Comma-separated combinations of event attribute value and trend period ids in the format "<Event attribute value>.<period id>[.<period id>]*".
SelectedEventAttributeTrendStartDate: Selected start date in ISO format (e.g. 2016-01-28).
SelectedEventAttributeTrendPeriodLevel: Selected period level, possible values are 0 = Day, 1 = Week, 2 = Month, 3 = Quarter, 4 = Year.
SelectedEventAttributeTrendAttributeType: Event attribute id.
Path selection parameters:
SelectedActivityPaths: Activity paths separated by commas.
Direction: The value is "1" if the "Predecessors" Direction was selected in the Path Analysis, otherwise "0".
AbsolutePath:
RootEventType: Root event type id.
CombinePaths:
Duration selection parameters:
DurationTimeSlots: Durations separated by commas.
Granularity: The duration time unit used in the selection. See the "Group By" entry in the Duration Analysis page for reference.
MaximumDuration: The duration limit in the selection. See the "Duration Limit" entry in the Duration Analysis page for reference.
'ScriptIds'
Used with Script Log Report analysis type. A comma-separated list of script ids for which the log entries are returned. Optional. If not given, logs for all scripts available to the user are included into the report.
'MinStartTime'
Used with the Script Log Report and Operation Log Analysis analysis types. A date time parameter which specifies the earliest start time in the log entries. Format is ISO8601: yyyy-mm-ddThh:mm:ss.fff. The maximum year is 2030 and the minimum year is 1970. 'fff' stands for fractions of seconds and it is given in 3ms accuracy and the last digit must be 0, 3 or 7.
  • For Script Log Report analysis type: Mandatory if ScriptIds parameter is not defined.
  • For Operation Log Analysis analysis type: Optional. Default value is no earliest start time limit.
'MaxStartTime'
Used with Script Log Report and Operation Log Analysis analysis types. A date time parameter which specifies the latest start time in the log entries. Format is ISO8601: yyyy-mm-ddThh:mm:ss.fff. The maximum year is 2030 and the minimum year is 1970. 'fff' stands for fractions of seconds and it is given in 3ms accuracy and the last digit must be 0, 3 or 7. Optional. Default value is no latest start time limit.
'IncludeCaseVariationRelations'
Used with Event Analysis analysis type. Boolean value (default value = FALSE). If set to TRUE, the result of the event analysis will include a "CaseIdToVariationIdMap" property which is an array. Every item in the array describes the mapping between a case and a variation by ids formatted as simple array of length 2 (first item being case id, the second being variation id of that case).
'IncludeEventTypeNames'
Used with Event Analysis analysis type. Boolean value (default value = FALSE). If set to TRUE, the result of the event analysis will include an "EventTypeNames" property which is an array of objects. Every item in the array maps event type id ("Key" property) into the name of that event type ("Value" property).
'UseMillisecondsSince1970DateTimeFormatting'
Used with Event Analysis analysis type. Boolean value (default value = FALSE). If set to TRUE, the start time of every event is actually an integer number of milliseconds after 1.1.1970.
'UseIdsInsteadOfNames'
Used with Event Analysis analysis type. Boolean value (default = FALSE). If set to TRUE, instead of returning case and event type names, the event analysis returns case and event type identifiers.
'IncludeCaseAttributeValues'
Used with Event Analysis analysis type. Boolean value (default = FALSE). If set to TRUE, the result of the event analysis will include a "CaseAttributeValues" array which is an array of arrays. The first item in each inner array is the id of the case. All the items following that are the attribute values of case attributes sorted by case attribute name.
'MaxTextLength'
Used with Operation Log Analysis analysis type. Integer. The maximum number of characters to return from the 'Name', 'Session Id', 'User Name', 'Model Name', 'Message', and 'Additional Data' fields. If 0 (default), all characters are returned.
'MaximumCount'
Used with Operation Log Analysis analysis type. Integer. The maximum amount of rows returned. Optional. Default value is 1000.
'ShowOpenOnly'
Used with Operation Log Analysis analysis type. Boolean. When TRUE, the Operation Log Analysis returns only operations that don't have end time. Optional. Default value is FALSE.

See examples at the GetAnalysis Script Examples page.

--#ImportCaseAttributes

Loads Case Attributes from the data which the preceding SQL statements given as parameters provide into the specified model. This command takes two SELECT queries as parameters. Models with external data source (ODBC_Datasource) not support this command.

First Query

Configures the command using a SELECT statement returning two columns: the first column is for a key and the second one is for a value of that key. The values in both the key column and in the value column are of type NVARCHAR. The supported keys for this command are:

'ProjectId' or 'ProjectName'
The id or the name of the project in which the target model exists. Defaults to the current project. If the given ProjectName doesn't exist, a new project is created.
'ModelId' or 'ModelName'
The id or the name of the existing/new target model. Defaults to the current model. If ModelId is given, neither ProjectId nor ProjectName are used. If the given ModelName doesn't exist, a new model is created.
'Append'
Defines what to do with an existing target model case attributes. TRUE or any other Integer than "0" = the existing case attributes in the target model are not deleted before import, FALSE or "0" = the existing case attributes of the target model are deleted before the import. If the target model is set to use another model as the Case Attribute Model, those case attributes are not deleted. Not used when creating a new model. Default value is TRUE.
'DataSourceType'
Defined data source type for the created model if a new model is created when using this command. Options are Database (default) and Datatable (see more about datasource types). When Datatable is used, this command imports the data to datatables.
'CatchOperationExceptions'
Optional. Defines whether to stop the script execution or to continue to run the script from the next statement if an exception occurs when running the script:
1 = don't stop execution of the script, continue running the script from the next statement.
0 = stop execution of the current script and show the exception.
The following script variables will be set and are shown in the script log:
@_ExceptionOccurred If there was an exception, then this value is 1, otherwise 0. INT
@_ExceptionType If there was an exception, shows the C# class name for the exception, NVARCHAR(MAX), otherwise NULL.
@_ExceptionMessage If there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
@_ExceptionDetails if there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.

Second Query

'<data>'
The database query whose results are to be imported. Note that the geometry, geography, hierarchyid, and image SQL data types are not supported by the ImportCaseAttributes command.

See examples at the ImportCaseAttributes Script Examples page.

--#ImportDataTable

Imports data to a Data Table. This command takes two SELECT queries as parameters.

First Query

Configures the command using a SELECT statement returning two columns: the first column is for a key and the second one is for a value of that key. The values in both the key column and in the value column are of type NVARCHAR. The supported keys for this command are:

'ProjectId' or 'ProjectName'
The id or the name of the project in which the target data table exists.
'DataTableId' or 'DataTableName'
The id or the name of the existing/new target data table.
'Append'
Defines what to do with an existing target data table contents. TRUE or any other Integer than "0" = the existing contents in the target data table are not deleted before import, FALSE or "0" = the existing contents of the target data table are deleted before the import. Not used when creating a new data table.
'CatchOperationExceptions'
Optional. Defines whether to stop the script execution or to continue to run the script from the next statement if an exception occurs when running the script:
1 = don't stop execution of the script, continue running the script from the next statement.
0 = stop execution of the current script and show the exception.
The following script variables will be set and are shown in the script log:
@_ExceptionOccurred If there was an exception, then this value is 1, otherwise 0. INT
@_ExceptionType If there was an exception, shows the C# class name for the exception, NVARCHAR(MAX), otherwise NULL.
@_ExceptionMessage If there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
@_ExceptionDetails If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.

Second Query

'<data>'
The database query whose results are to be imported. Note that if the query doesn't return any data, the data table is not created.

See examples at the ImportDataTable Script Examples page.

--#ImportEvents

Loads Events from the data which the preceding SQL statements given as parameters provide into the specified model. This command takes two SELECT queries as parameters. Models with external data source (ODBC_Datasource) not support this command.

First Query

Configures the command using a SELECT statement returning two columns: the first column is for a key and the second one is for a value of that key. The values in both the key column and in the value column are of type NVARCHAR. The supported keys for this command are:

'ProjectId' or 'ProjectName'
The id or the name of the project in which the target model exists. Defaults to the current project. If the given ProjectName doesn't exist, a new project is created.
'ModelId' or 'ModelName'
The id or the name of the existing/new target model. Defaults to the current model. If ModelId is given, neither ProjectId nor ProjectName are used. If the given ModelName doesn't exist, a new model is created.
'Append'
Defines what to do with the existing target model events. TRUE or any other Integer than "0" = the existing events in the target model are not deleted before import, FALSE or "0" = the existing events of the target model are deleted before the import. Not used when creating a new model. Default value is TRUE.
'DataSourceType'
Defined data source type for the created model if a new model is created when using this command. Options are Database (default) and Datatable (see more about datasource types). When Datatable is used, this command imports the data to datatables.
'CatchOperationExceptions'
Optional. Defines whether to stop the script execution or to continue to run the script from the next statement if an exception occurs when running the script:
1 = don't stop execution of the script, continue running the script from the next statement.
0 = stop execution of the current script and show the exception.
The following script variables will be set and are shown in the script log:
@_ExceptionOccurred If there was an exception, then this value is 1, otherwise 0. INT
@_ExceptionType If there was an exception, shows the C# class name for the exception, NVARCHAR(MAX), otherwise NULL.
@_ExceptionMessage If there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
@_ExceptionDetails If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.

Second Query

'<data>'
The database query whose results are to be imported. Note that the geometry, geography, hierarchyid, and image SQL data types are not supported by the ImportEvents command.

See examples at the ImportEvents Script Examples page.

--#ImportOdbcQuery

Extracts data from an ODBC data source and imports it to QPR ProcessAnalyzer datatable or temporary table. Column names from the query result as used. If a column name contains illegal characters for table names, the illegal characters are converted to be underscore characters. Columns are extracted as text data. To use ImportOdbcQuery, define a SELECT statement returning two columns: the first column is for a key and the second one is for a value of that key. The values in both the key column and in the value column are of type NVARCHAR. The supported keys for this command are:

TargetTable
The temporary table to which the data is to be imported. If not used, define the target using the ProjectId/ProjectName, DataTableId/DataTableName, and Append parameters described below.
ProjectId / ProjectName
The id or the name of the project in which the target data table exists.
DataTableId / DataTableName
The id or the name of the existing/new target data table.
Append
Defines what to do with an existing target data table and its contents. TRUE or any other Integer than "0" = the target data table and its existing contents are not deleted before import. If a user imports into a data table with 'Append' = FALSE or "0", the contents of the data table are deleted before the import. If a user imports into a temporary table (i.e. TargetTable) with 'Append' = FALSE or "0", then the whole temporary table is deleted before the import. Not used when creating a new data table.
CatchOperationExceptions
Optional. Defines whether to stop the script execution or to continue to run the script from the next statement if an exception occurs when running the script:
1 = don't stop execution of the script, continue running the script from the next statement.
0 = stop execution of the current script and show the exception.
The following script variables will be set and are shown in the script log:
@_ExceptionOccurred If there was an exception, then this value is 1, otherwise 0. INT
@_ExceptionType If there was an exception, shows the C# class name for the exception, NVARCHAR(MAX), otherwise NULL.
@_ExceptionMessage If there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
@_ExceptionDetails If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.
OdbcConnectionString
The ODBC driver connection string that includes the settings needed to establish the initial connection. Mandatory. See OdbcConnection.ConnectionString Property in Microsoft Development Network for more information on the possible connection strings.
OdbcConnectionStringKey
Secure string key for the connection string. Alternative for the OdbcConnectionString property.
OdbcQueryString
The SQL query string. Mandatory. Note that if the query doesn't return any data, the target data table or temporary table is not created.
ExecuteInClientSide
Defines whether the command is executed in the client side or in the server side when using QPR ProcessAnalyzer Pro. This parameter is used when there is no server connection available, for example. TRUE or any other Integer than "0" = the import query is executed in the client side. FALSE or "0" = the import query is executed in the server side. Supports only data table as the import destination. If 'TargetTable' has been defined as the import destination and the value of this parameter is given as TRUE or any other Integer than "0", you will receive an error message. Optional. Default value is FALSE.

See examples in the ImportOdbcQuery Script Examples page.

--#ImportOleDbQuery

Extracts data from an OLE DB data source and imports it to QPR ProcessAnalyzer datatable or a temporary table. Column names from the query result are used. It is possible to both create new datatables as well as modify existing datatables with this command. To use the ImportOleDbQuery, define a SELECT statement returning two columns: the first column is for a key and the second one is for a value of that key. The values in both the key column and in the value column are of type NVARCHAR. The supported keys for this command are:

TargetTable
The temporary table to which the data is to be imported. If not used, define the target using the ProjectId/ProjectName, DataTableId/DataTableName, and Append parameters described below.
ProjectId / ProjectName
The id or the name of the project in which the target data table exists.
DataTableId / DataTableName
The id or the name of the existing/new target data table.
Append
Defines what to do with an existing target data table and its contents. TRUE or any other Integer than "0" = the target data table and its existing contents are not deleted before import. If a user imports into a data table with 'Append' = FALSE or "0", the contents of the data table are deleted before the import. If a user imports into a temporary table(i.e. TargetTable) with 'Append' = FALSE or "0", then the whole temporary table is deleted before the import. Not used when creating a new data table.
CatchOperationExceptions
Optional. Defines whether to stop the script execution or to continue to run the script from the next statement if an exception occurs when running the script:
1 = don't stop execution of the script, continue running the script from the next statement.
0 = stop execution of the current script and show the exception.
The following script variables will be set and are shown in the script log:
@_ExceptionOccurred If there was an exception, then this value is 1, otherwise 0. INT
@_ExceptionType If there was an exception, shows the C# class name for the exception, NVARCHAR(MAX), otherwise NULL.
@_ExceptionMessage If there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
@_ExceptionDetails If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.
OleDbConnectionString
The OLE DB connection string that includes the settings needed to establish the initial connection. Mandatory. See OleDbConnection.ConnectionString Property in Microsoft Development Network for more information on the possible connection strings.
OleDbConnectionStringKey
Secure string key for the OLE DB connection string. Alternative for the OleDbConnectionString property.
OleDbQueryString
The SQL query string. Mandatory. Note that if the query doesn't return any data, the target data table or temporary table is not created.
ExecuteInClientSide
Defines whether the command is executed in the client side or in the server side when using QPR ProcessAnalyzer Pro. This parameter is used when there is no server connection available, for example. TRUE or any other Integer than "0" = the import query is executed in the client side. FALSE or "0" = the import query is executed in the server side. Supports only data table as the import destination. If 'TargetTable' has been defined as the import destination and the value of this parameter is given as TRUE or any other Integer than "0", you will receive an error message. Optional. Default value is FALSE.

See examples at the ImportOleDbQuery Script Examples page.

--#ImportSalesforceQuery

Extracts data from the Salesforce cloud and imports it into a data table as NVARCHAR(MAX) or SQL_VARIANT type data. This command takes one SELECT query as its parameter.

Query

Configures the command using a SELECT statement returning two columns: the first column is for a key and the second one is for a value of that key. The values in both the key column and in the value column are of type NVARCHAR. The supported keys for this command are:

TargetTable
The temporary table to which the data is to be imported. If not used, define the target using the ProjectId/ProjectName, DataTableId/DataTableName, and Append parameters described below.
ProjectId / ProjectName
The id or the name of the project in which the target data table exists.
'DataTableId' or 'DataTableName'
The id or the name of the existing/new target data table.
Append
Defines what to do with an existing target data table contents. TRUE or any other Integer than "0" = the existing contents in the target data table are not deleted before import, FALSE or "0" = the existing contents of the target data table are deleted before the import. Not used when creating a new data table.
CatchOperationExceptions
Optional. Defines whether to stop the script execution or to continue to run the script from the next statement if an exception occurs when running the script:
1 = don't stop execution of the script, continue running the script from the next statement.
0 = stop execution of the current script and show the exception.
The following script variables will be set and are shown in the script log:
@_ExceptionOccurred If there was an exception, then this value is 1, otherwise 0. INT
@_ExceptionType If there was an exception, shows the C# class name for the exception, NVARCHAR(MAX), otherwise NULL.
@_ExceptionMessage If there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
@_ExceptionDetails If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.

Salesforce Query Parameters

SalesforceUser
Username for the Salesforce cloud.
SalesforcePW
Password for the Salesforce cloud.
SalesforcePWKey
Secure string key for the stored Salesforce password. Alternative for the SalesforcePW property.
SalesforceUrl
Optional. Salesforce service Url for the Salesforce cloud. Default value for QPR ProcessAnalyzer 2016.1 version is 'https://login.salesforce.com/services/Soap/u/27.0'. In order to connect to Salesforce's test environment the URL should be like: 'https://test.salesforce.com/services/Soap/u/27.0'.
SalesforceQueryMode
Optional. The Salesforce query function to be used. 1 (default) = queryall(), 2 = query(), 3 = describeSObject().
SalesforceQuery
The query to be run in the Salesforce cloud. Note that "*" cannot be used in the query. See Salesforce API and SOQL Reference for more information. Note that if the query doesn't return any data, the target data table or temporary table is not created.
SalesforceQueryRetries
Optional. Number of retries to attempt if the Salesforce query doesn't succeed. Default value is 3.
SalesforceQueryRetryWait
Optional. Number of milliseconds to wait between query retries. Default is 3000 ms.
SalesforceBatchSize
Optional. The number of rows of data the query returns in one batch. Minimum = 200, Maximum = 2000, Default = 500. See Salesforce API for more information.

Notes

If you get error in --#ImportSalesforceQuery PA Operation saying "INVALID_TYPE sObject type 'Objectname' is not supported":

  • Check that the object in question exists or that the object name is correct.
  • Verify that the Salesforce user has rights to the object.
  • The Salesforce user may need extra lisence to access the object. Special 3rd party custom objects may need separate license. Contact your Salesforce application administrator.

See examples at the ImportSalesforceQuery Script Examples page.

--#ImportSapQuery

Extracts data from an SAP system and imports it to QPR ProcessAnalyzer datatable or a temporary table. Column names from the query result are used. If a column name contains illegal characters for table names, the illegal characters are converted to be underscore characters (e.g. "sap:Owner" -> "sap_Owner"). Columns are extracted as text data. Note that using this command requires some dlls not provided by QPR Software.

To use the ImportSapQuery command, define a SELECT statement returning two columns: the first column is for a key and the second one is for a value of that key. The values in both the key column and in the value column are of type NVARCHAR. The supported keys for this command are:

TargetTable
If this parameter is given, store the results into a temporary SQL table in the ETL sandbox. If the TargetTable parameter is not given, use either the ProjectId or ProjectName parameters.
ProjectId / ProjectName
The id or the name of the project in which the target data table exists.
DataTableId / DataTableName
The id or the name of the existing/new target data table.
Append
Defines what to do with an existing target data table and its contents. TRUE or any other Integer than "0" = the target data table and its existing contents are not deleted before import. If a user imports into a data table with 'Append' = FALSE or "0", the contents of the data table are deleted before the import. If a user imports into a temporary table (i.e. TargetTable) with 'Append' = FALSE or "0", then the whole temporary table is deleted before the import. Not used when creating a new data table.
CatchOperationExceptions
Optional. Defines whether to stop the script execution or to continue to run the script from the next statement if an exception occurs when running the script:
1 = don't stop execution of the script, continue running the script from the next statement.
0 = stop execution of the current script and show the exception.
The following script variables will be set and are shown in the script log:
@_ExceptionOccurred If there was an exception, then this value is 1, otherwise 0. INT
@_ExceptionType If there was an exception, shows the C# class name for the exception, NVARCHAR(MAX), otherwise NULL.
@_ExceptionMessage If there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
@_ExceptionDetails If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.
SapUser
SAP username used to connect to SAP. Mandatory. Corresponds to the "USER" constant on SAP side. See the SAP .NET Connector documentation for more info.
SapPW
Password of the SAP user used to connect to SAP. Mandatory. Corresponds to the "PASSWD" constant on SAP side. See the SAP .NET Connector documentation for more info.
SapPWKey
Secure string key for the stored SAP password. Alternative for the SapPW property.
SapClient
The SAP backend client. Mandatory. Corresponds to the "CLIENT" constant on SAP side. See the SAP .NET Connector documentation for more info.
SapAppServerHost
The hostname or IP of the specific SAP application server, to which all connections shall be opened. Mandatory if SapMessageServerHost is not defined. Corresponds to the "ASHOST" constant on SAP side. See the SAP .NET Connector documentation for more info.
SapMessageServerHost
The hostname or IP of the SAP system’s message server (central instance). Mandatory if SapAppServerHost is not defined. Corresponds to the "MSHOST" constant on SAP side. See the SAP .NET Connector documentation for more info.
SapSystemNumber
The SAP system’s system number. Mandatory if SapSystemID is not defined. Corresponds to the "SYSNR" constant on SAP side. See the SAP .NET Connector documentation for more info.
SapSystemID
The SAP system’s three-letter system ID. Mandatory if SapSystemNumber is not defined. Corresponds to the "SYSID" constant on SAP side. See the SAP .NET Connector documentation for more info.
ExecuteInClientSide
Defines whether the command is executed in the client side or in the server side when using QPR ProcessAnalyzer Pro. This parameter is used when there is no server connection available, for example. TRUE or any other Integer than "0" = the import query is executed in the client side. FALSE or "0" = the import query is executed in the server side. Supports only data table as the import destination. If 'TargetTable' has been defined as the import destination and the value of this parameter is given as TRUE or any other Integer than "0", you will receive an error message. Optional. Default value is FALSE.
SapLanguage
SAP language used. Default value is "EN". Optional. Corresponds to the "LANG" constant on SAP side. See the SAP .NET Connector documentation for more info.
SapPoolSize
The maximum number of RFC connections that this destination will keep in its pool. Default value is "5". Optional. Corresponds to the "POOL_SIZE" constant on SAP side. See the SAP .NET Connector documentation for more info.
SapPeakConnectionsLimit
In order to prevent an unlimited number of connections to be opened, you can use this parameter. Default value is "10". Optional. Corresponds to the "MAX_POOL_SIZE" constant on SAP side. See the SAP .NET Connector documentation for more info.
SapConnectionIdleTimeout
If a connection has been idle for more than SapIdleTimeout seconds, it will be closed and removed from the connection pool upon checking for idle connections or pools. Default value is "600". Optional. Corresponds to the "IDLE_TIMEOUT" constant on SAP side. See the SAP .NET Connector documentation for more info.
SapRouter
A list of host names and service names / port numbers for the SAPRouter in the following format: /H/hostname/S/portnumber. Optional. Corresponds to the "SAPROUTER" constant on SAP side. See the SAP .NET Connector documentation for more info.
SapLogonGroup
The logon group from which the message server shall select an application server. Optional. Corresponds to the "GROUP" constant on SAP side. See the SAP .NET Connector documentation for more info.
SapQueryMode
If this number is set to "1", then the query result will have the SAP Table field names as data table column names and actual data rows as rows. If this is set to "3", the query result will get the field descriptions from the SAP query using NO_DATA parameter, i.e. the returned columns are the following (in this order): Field, Type, Description, Length, Offset. Default value is "1". Optional. See the SAP .NET Connector documentation for more info.
SapQueryTable
Name of the SAP table to be extracted. Specifies the value for the parameter QUERY_TABLE in tab: 'Import' or function module 'rfc_read_table' in SAP. Mandatory. See the SAP .NET Connector documentation for more info. Note that if the query doesn't return any data, the target data table or temporary table is not created.
SapRowcount
The maximum amount of rows to fetch. Specifies the value for parameter ROWCOUNT in tab: 'Import' or function module 'rfc_read_table' in SAP. Optional. See the SAP .NET Connector documentation for more info.
SapRowskips
The number of rows to skip. Specifies the value for parameter ROWSKIPS in tab: 'Import' or function module 'rfc_read_table'. in SAP. Optional. See the SAP .NET Connector documentation for more info.
SapWhereClause
A comma separated list of WHERE clause elements passed for the SapQueryTable. Can be used with or without the SapWhereClauseSelect parameter. If used together with the SapWhereClauseSelect parameter, use the SapWhereClause parameter first. NOTE: The default maximum length for the Where Clause string is 72 characters in SAP, so the recommended maximum length of the SapWhereClause value is also 72 characters. In effect, specifies the value for parameter OPTIONS in tab: 'Import' or function module 'rfc_read_table' in SAP. Optional. See the SAP .NET Connector documentation for more info.
SapWhereClauseSelect
The SELECT query to be executed in QPR ProcessAnalyzer sandbox. Used with or without the SapWhereClause parameter to pass WHERE clauses to SapQueryTable. If used together with the SapWhereClause parameter, use the SapWhereClause parameter first. The query is expected to return a table with at least one column, as the contents from the rows in the first column of the table are concatenated together to form the WHERE clause in SAP RFC_ReadTable. Therefore, it's recommended to first create the table with the WHERE clauses into a temporary table. In addition, it's recommended to have an order number column in the table and use that in the SELECT query to make sure the WHERE clause elements are concatenated in the correct order. The default maximum length for Where Clause string is 72 characters in SAP, so the recommended maximum length for the WHERE clause string in each row of the table is also 72. In effect, specifies the value for parameter OPTIONS in tab: 'Import' or function module 'rfc_read_table' in SAP. Optional. The contents up to the first 10 rows in the first column of the SELECT query are shown in the QPR ProcessAnalyzer Script Log. See the SAP .NET Connector documentation for more info.
See also Troubleshooting for other SAP related limitations.
SapFieldNames
A comma separated list of field names for columns to be imported. Default value is empty, resulting in all columns being imported. Specifies the value for parameter FIELDNAME in tab: 'Tables' for table 'FIELDS' for function module 'rfc_read_table' in SAP. Optional. See the SAP .NET Connector documentation for more info.
SapFunction
If you define a value for this parameter, then the new value specifies the SAP function that is called inside the #ImportSapQuery command. Optional. The default value is RFC_READ_TABLE. Another possible value is BBP_RFC_READ_TABLE. See the SAP .NET Connector documentation for more info.

See examples at the ImportSapQuery Script Examples page.

--#ImportSqlQuery

Extracts data from an ADO.Net source (which usually is an SQL Server database) and imports it to QPR ProcessAnalyzer datatable or a temporary table. Column names from the query result are used. It is possible to both create new Data Tables as well as modify existing datatables with this command. To use the ImportSqlQuery command, a SELECT statement returning two columns: the first column is for a key and the second one is for a value of that key. The values in both the key column and in the value column are of type NVARCHAR. The supported keys for this command are:

TargetTable
The temporary table to which the data is to be imported. If not used, define the target using the ProjectId/ProjectName, DataTableId/DataTableName, and Append parameters described below.
ProjectId / ProjectName
The id or the name of the project in which the target data table exists.
DataTableId / DataTableName
The id or the name of the existing/new target data table.
Append
Defines what to do with an existing target data table and its contents. TRUE or any other Integer than "0" = the target data table and its existing contents are not deleted before import. If a user imports into a data table with 'Append' = FALSE or "0", the contents of the data table are deleted before the import. If a user imports into a temporary table (i.e. TargetTable) with 'Append' = FALSE or "0", then the whole temporary table is deleted before the import. Not used when creating a new data table.
CatchOperationExceptions
Optional. Defines whether to stop the script execution or to continue to run the script from the next statement if an exception occurs when running the script:
1 = don't stop execution of the script, continue running the script from the next statement.
0 = stop execution of the current script and show the exception.
The following script variables will be set and are shown in the script log:
@_ExceptionOccurred If there was an exception, then this value is 1, otherwise 0. INT
@_ExceptionType If there was an exception, shows the C# class name for the exception, NVARCHAR(MAX), otherwise NULL.
@_ExceptionMessage If there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
@_ExceptionDetails If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.
SqlConnectionString
The SQL connection string that includes the settings needed to establish the initial connection. Mandatory. See SqlConnection.ConnectionString Property in Microsoft Development Network for more information on the connection parameters.
SqlConnectionStringKey
Secure string key for the connection string. Alternative for the SqlConnectionString property.
SqlQueryString
The SQL query string. Mandatory. Note that if the query doesn't return any data, the target data table or temporary table is not created.
ExecuteInClientSide
Defines whether the command is executed in the client side or in the server side when using QPR ProcessAnalyzer Pro. This parameter is used when there is no server connection available, for example. TRUE or any other Integer than "0" = the import query is executed in the client side. FALSE or "0" = the import query is executed in the server side. Supports only data table as the import destination. If 'TargetTable' has been defined as the import destination and the value of this parameter is given as TRUE or any other Integer than "0", you will receive an error message. Optional. Default value is FALSE.

See examples at the ImportSqlQuery Script Examples page.

--#RemoveCases

Removes all or specified cases in the target model. This command takes two SELECT queries as parameters. Models with Datatable, ODBC or Loading Script Datasources don't support this command.

First Query

Configures the command using a SELECT statement returning two columns: the first column is for a key and the second one is for a value of that key. The values in both the key column and in the value column are of type NVARCHAR. The supported keys for this command are:

'ProjectId' or 'ProjectName'
The id or the name of the project in which the target model exists. Defaults to the current project.
'ModelId' or 'ModelName'
The id or the name of the target model. Defaults to the current model. If ModelId is given, neither ProjectId nor ProjectName are used.
'CatchOperationExceptions'
Optional. Defines whether to stop the script execution or to continue to run the script from the next statement if an exception occurs when running the script:
1 = don't stop execution of the script, continue running the script from the next statement.
0 = stop execution of the current script and show the exception.
The following script variables will be set and are shown in the script log:
@_ExceptionOccurred If there was an exception, then this value is 1, otherwise 0. INT
@_ExceptionType If there was an exception, shows the C# class name for the exception, NVARCHAR(MAX), otherwise NULL.
@_ExceptionMessage If there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
@_ExceptionDetails If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.

Second Query

The optional database query that returns the cases to be removed. Note that if there are several columns in the query, the cases have to be in the first column of the query. If the second query is not given, all cases are removed from the model. If the result of the second query is empty, no cases are removed. Removing a case also removes all events and case attributes associated with the case.

See examples at the RemoveCases Script Examples page.

--#RemoveEvents

Removes all or specified events in the target model, but retains Cases, Event Types, and Variations. This command takes two SELECT queries as parameters. Models with Datatable, ODBC or Loading Script Datasources don't support this command.

First Query

Configures the command using a SELECT statement returning two columns: the first column is for a key and the second one is for a value of that key. The values in both the key column and in the value column are of type NVARCHAR. The supported keys for this command are:

'ProjectId' or 'ProjectName'
The id or the name of the project in which the target model exists. Defaults to the current project.
'ModelId' or 'ModelName'
The id or the name of the target model. Defaults to the current model. If ModelId is given, neither ProjectId nor ProjectName are used.
'CatchOperationExceptions'
Optional. Defines whether to stop the script execution or to continue to run the script from the next statement if an exception occurs when running the script:
1 = don't stop execution of the script, continue running the script from the next statement.
0 = stop execution of the current script and show the exception.
The following script variables will be set and are shown in the script log:
@_ExceptionOccurred If there was an exception, then this value is 1, otherwise 0. INT
@_ExceptionType If there was an exception, shows the C# class name for the exception, NVARCHAR(MAX), otherwise NULL.
@_ExceptionMessage If there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
@_ExceptionDetails If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.

Second Query

The optional database query that returns the event Id's to be removed. Note that if there are several columns in the query, the event Id's have to be in the first column of the query.

See examples at the RemoveEvents Script Examples page.

--#Run

Runs another script with specified parameters. This command can take multiple SELECT queries as parameters.

First Query

Configures the command using a SELECT statement returning two columns: the first column is for a key and the second one is for a value of that key. The values in both the key column and in the value column are of type NVARCHAR. The supported keys for this command are:

'ScriptId'
Mandatory. The Id of the called script.
'CatchOperationExceptions'
Optional. Defines whether to stop the script execution or to continue to run the script from the next statement if an exception occurs when running the script:
1 = don't stop execution of the script, continue running the script from the next statement.
0 = stop execution of the current script and show the exception.
The following script variables will be set and are shown in the script log:
@_ExceptionOccurred If there was an exception, then this value is 1, otherwise 0. INT
@_ExceptionType If there was an exception, shows the C# class name for the exception, NVARCHAR(MAX), otherwise NULL.
@_ExceptionMessage If there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
@_ExceptionDetails If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.

Following Queries

The following queries are optional and used for initializing the arguments which are passed to the script to be run. The maximum number of arguments is 10. Each argument is created as a temporary table with names #_Arg1, ... #_Arg10. In the created temporary tables, all columns are of the type SQL Variant. If the column names have not been specified, then "Value_0", "Value_1", etc. are used as column names. The possible arguments are as follows:

  • @_Argv - type INT: the number of provided parameters (from 0 to 10)
  • #_Arg1, ... #_Arg10: arguments passed to that script

Each argument exists in the called script until the next --#Run command is executed in that script. After the called script has finished, the main script continues its execution.

See examples at the Run Script Examples page.

--#SendEmail

Sends an e-mail and writes a message to script log whether sending the email was successful or not. Script execution continues even when the sending isn't successful.

Configures the command using a SELECT statement returning two columns: the first column is for a key and the second one is for a value of that key. The values in both the key column and in the value column are of type NVARCHAR. The supported keys for this command are:

'CatchOperationExceptions'
Optional. Defines whether to stop the script execution or to continue to run the script from the next statement if an exception occurs when running the script:
1 = don't stop execution of the script, continue running the script from the next statement.
0 = stop execution of the current script and show the exception.
The following script variables will be set and are shown in the script log:
@_ExceptionOccurred If there was an exception, then this value is 1, otherwise 0. INT
@_ExceptionType If there was an exception, shows the C# class name for the exception, NVARCHAR(MAX), otherwise NULL.
@_ExceptionMessage If there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
@_ExceptionDetails If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.

E-mail Parameters

'EmailFrom'
Defines the from address for this e-mail message. Mandatory.
'EmailTo'
Defines the recipient(s) for this e-mail message given in a list separated by comma. Mandatory.
'EmailSubject'
Defines the subject of the email. Default value is empty. Optional.
'EmailBody'
Defines the message body. Default value is empty. Optional.
'EmailCc'
Defines the carbon copy recipient(s) for this e-mail message given in a list separated by comma. Optional.
'EmailBcc'
Defines the blind carbon copy recipient(s) for this e-mail message given in a list separated by comma. Optional.
'EmailIsBodyHtml'
Defines whether the e-mail message body is in HTML. TRUE or any other Integer than "0" = body is in HTML, FALSE or "0" = body is not in HTML. Default value is FALSE. Optional.
'EmailSender'
Defines the sender's address for this e-mail message. Default value is empty. Optional.
'EmailReplyTo'
Defines the ReplyTo address(es) for the mail message given in a list separated by comma. Optional.
'EmailPriority',
Defines the priority of this e-mail message. Possible values are "High", "Normal", and "Low". Default value is "Normal". Optional.
'EmailDeliveryNotification'
Defines the delivery notifications for this e-mail message. Possible values are "Delay", "Never", "None", "OnFailure", and "OnSuccess". Default value is "None". Optional.
'EmailBodyEncoding'
Defines the encoding used to encode the message body. Supported encodings are listed in the "Remarks" section at http://msdn.microsoft.com/en-us/library/System.Text.Encoding.aspx. Optional.
'EmailSubjectEncoding'
Defines the encoding used for the subject content for this e-mail message. Supported encodings are listed in the "Remarks" section at http://msdn.microsoft.com/en-us/library/System.Text.Encoding.aspx. Optional.
'EmailAttachmentQuery'
Defines a query to fetch the parameters for adding attachments to the email. Each row (except the header row) in the query result corresponds to one attachment. The result must contain the following columns in this order: Name of the attachment, Content for the attachment (Sent as-is without any modifications. Supports binary values.), Media type (supported types are text/plain, text/html, text/xml, and image/jpeg), and Creation time (SQL datetime). Names of the columns do not matter. If the result doesn't contain some of the columns, an error is written into the Progress log, and the email is not sent. Optional.

SMTP Server Parameters

'SmtpServer'
Defines the hostname or the IP address of the server. Mandatory for the first occurrence of the SendEmail command during script execution.
'SmtpPort'
Defines the port of the SMTP server. Default value is "25". Optional.
'SmtpAuthenticationUsername'
Defines the user name for the SMTP server. Note that the user name is in plain text and visible to all users who have access to the script. Optional.
'SmtpAuthenticationPassword'
Defines the password for the SMTP server. Note that the password is in plain text and visible to all users who have access to the script. Optional.
'SmtpEnableSSL'
Defines whether SSL should be enabled for the SMTP connection. TRUE or any other Integer than "0" = SSL is enabled, FALSE or "0" = SSL is not enabled. Default value is "FALSE". Optional.

See examples at the SendEmail Script Examples page.

See also How to Define the SMTP Server Connection in an On-Site Deployment.

--#ShowReport

Creates a new Excel sheet containing a table that contains the results of the user specified SQL query. The result column names are the field names of the SQL query and the rows are the actual data rows of the SQL query. The report can be used to see, for example, the events that would be loaded into QPR ProcessAnalyzer before actually loading them. If the events have problems that cause errors when loaded it is useful to be able to see the row data in a report. Note: Excel cannot handle more than 1 million rows to be shown so if the result set contains more rows than that, the data will be truncated to 1 million rows.

This command takes two SELECT queries as parameters.

First Query

A user specified SQL query to be shown in the configured Excel sheet.

'<data>'
Mandatory. The database query whose results are to be returned.

Second Query

Configures the command using a SELECT statement returning two columns: the first column is for a key and the second one is for a value of that key. The values in both the key column and in the value column are of type NVARCHAR. The supported keys for this command are:

'<Analysis Parameter>'
Optional. The QPR ProcessAnalyzer Analysis Parameters given for the operation. Some suggested parameters to be used:
'Title'
The title of the created report. If not given, "Report" will be used as a default.
'SheetName'
The name of the Excel sheet to be created.
'MaximumCount'
The maximum number of rows to show (0 = all, default = 1000).
'PivotSheetName'
Optional. The name of the Excel sheet to be created. The sheet will contain a pivot table that includes all columns and rows in the report. The pivot sheet doesn't support drill-down. The pivot sheet has the following additional parameters:
'PivotFilters'
A comma separated list of result sheet column names that should be used in pivot table filters dimension.
'PivotRows'
A comma separated list of row names to be used in the pivot table rows dimension. To sort the results, add the following parameters separated by commas "(SortDirection=<Ascending or Descending>)" and/or "(SortBy=<Sort Field Name>)" to the row name. For example: "Region(SortDirection=Ascending, SortBy="Average of DurationDays"), Country(SortBy="Sum of Cost")"
'PivotColumns'
A comma separated list of column names to be used in the pivot table columns dimension. To sort the results, add the following parameters separated by commas "(SortDirection=<Ascending or Descending>)" and/or "(SortBy=<Sort Field Name>)" to the column name. For example: "Region(SortDirection=Ascending, SortBy="Average of DurationDays"), Country(SortBy="Sum of Cost")"
'PivotValues'
A comma separated list of column names to be used as the pivot table values. The summarize value field can be defined by adding a summarize value parameter to the end of each value name as follows: "(SummarizeBy=<Summarize value tag)". If the parameter is not defined, the Excel default value for summarize is used: "Sum" for numbers and "Count" for other types. The supported summarize value tags are: Sum, Count, Average, Max, Min, Product, CountNumbers, StdDev, StdDevp, Var, Varp. For example: "Duration Days(SummarizeBy="Average"),Cost(SummarizeBy="Sum")"
'PivotTitle'
The title of the pivot sheet to be created. If undefined, the name defined in PivotSheetName will be used.
'CatchOperationExceptions'
Optional. Defines whether to stop the script execution or to continue to run the script from the next statement if an exception occurs when running the script:
1 = don't stop execution of the script, continue running the script from the next statement.
0 = stop execution of the current script and show the exception.
The following script variables will be set and are shown in the script log:
@_ExceptionOccurred If there was an exception, then this value is 1, otherwise 0. INT
@_ExceptionType If there was an exception, shows the C# class name for the exception, NVARCHAR(MAX), otherwise NULL.
@_ExceptionMessage If there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
@_ExceptionDetails If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.

See examples at the ShowReport Script Examples page.

--#StartBackground

Makes the script run in background starting from this command, so that using QPR ProcessAnalyzer Excel client is possible while the script is running. The script results are returned to the Excel Client user interface (e.g. when ShowReport is reached in the script) before the StartBackground command is reached. Operations (e.g. ShowReport) resulting into adding new analysis results into the script run results after the StartBackground command is reached are executed, but the results are ignored, and in such cases an error is written into the Script Log.

Any exceptions causing the script run to fail while running the script in the background are written into the Script Log and the QPR ProcessAnalyzer Operation Log. It's possible to terminate scripts that run in the background via the Operation Log. If an import operation specified in the script requires execution in the client side, the client side execution is ignored and a warning is written into the Script Log.

Takes one SELECT query as a parameter. Following parameter is supported:

'Enabled'
Boolean value defining whether the script is run in background starting from this command. TRUE = run in background, FALSE = don't run in background. Default value is TRUE.

See examples at the StartBackground Script Examples page.

--#WriteLog

Adds the first column values from the preceding SQL statements to the log that is shown after the whole script execution is completed. In addition to the WriteLog command, you can also use the Print SQL statement to generate log entries into the script execution log. The difference to the WriteLog command is that the Print statement can use also variables.

See examples at the WriteLog Script Examples page.

Retrieved from "https://wiki.onqpr.com/pa/index.php?title=SQL_Scripting_Commands&oldid=15736"