SQL Scripting Commands: Difference between revisions

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search
 
(269 intermediate revisions by 5 users not shown)
Line 1: Line 1:
This page lists all the QPR ProcessAnalyzer commands that are supported in scripts. Each command consists of queries, which are explained in the following subsections.<br/>
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.


=<br/> --#CallWebService =
<div style="display: flex;flex-wrap: wrap;">
<div style="flex: 1 0 230px;border:1px solid #dfdfdf;padding:0 1em 1em 1.5em;background-color:#F7FAFC;margin:10px 0px 0px 10px;">
=== Data Extraction ===
* [[#--.23CallWebService|CallWebService]]
* [[#--.23ImportOdbcQuery|ImportOdbcQuery]]
* [[#--.23ImportOleDbQuery|ImportOleDbQuery]]
* [[#--.23ImportSalesforceQuery|ImportSalesforceQuery]]
* [[#--.23ImportSapQuery|ImportSapQuery]]
* [[#--.23ImportSqlQuery|ImportSqlQuery]] (ADO.Net)
</div>
 
<div style="flex: 1 0 230px;border:1px solid #dfdfdf;padding:0 1em 1em 1.5em;background-color:#F7FAFC;margin:10px 0px 0px 10px;">
 
=== Data Output ===
* [[#--.23ImportDataTable|ImportDataTable]]
* [[#--.23SendEmail|SendEmail]]
* [[#--.23ShowReport|ShowReport]]
* [[#--.23WriteLog|WriteLog]]
</div>
 
<div style="flex: 1 0 230px;border:1px solid #dfdfdf;padding:0 1em 1em 1.5em;background-color:#F7FAFC;margin:10px 0px 0px 10px;">
 
=== Script Flow ===
* [[#--.23RunQuery|RunQuery]] ([[RunQuery Script Examples|examples]])
* [[#--.23Commit|Commit]]
* [[#--.23Exit|Exit]]
* [[#--.23GetAnalysis|GetAnalysis]]
* [[#--.23Run|Run]]
* [[#--.23StartBackground|StartBackground]]
</div>
 
</div>
 
= --#CallWebService =
Extracts data via Web Service. This command takes one SELECT query as parameter.
Extracts data via Web Service. This command takes one SELECT query as parameter.


== Query ==
== 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:
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'
; Address
: Defines the URI of the service to call. Mandatory.
: Defines the URI of the service to call. Mandatory.
; 'Method'
; Method
: Defines the HTTP method to use for the call. Must be any of the following: GET (default), POST, PUT, DELETE. Optional.
: Defines the HTTP method to use for the call. Must be any of the following: GET (default), POST, PUT, DELETE. Optional.
; 'Body'
; Body
: Defines the message body text to send to the service. Default value is empty. Optional.
: Defines the message body text to send to the service. Default value is empty. Optional.
; 'Encoding'
; 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 https://msdn.microsoft.com/en-us/library/system.text.encoding%28v=vs.110%29.aspx]. Default value is UTF8. Optional.
: 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 https://msdn.microsoft.com/en-us/library/system.text.encoding%28v=vs.110%29.aspx]. Default value is UTF8. Optional.
; 'Timeout'
; Timeout
: Number of milliseconds to wait before the request times out. Default value is 60000. Optional.
: Number of milliseconds to wait before the request times out. Default value is 60000. Optional.
; 'ExecuteInClientSide'
; 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. Not used with QPR ProcessAnalyzer Xpress or QPR ProcessAnalyzer Database as they always execute in the client side. Supports only data table as the import destination. Default value is FALSE. Optional.
: Defines whether the web service call is made from the QPR ScriptLauncher or from the server. TRUE or 1, the call is executed in the ScriptLauncher. FALSE or 0, the call is executed in the server. Default value is FALSE. Optional.
; 'CatchOperationExceptions'
; 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:
: 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.
: 1 = don't stop execution of the script, continue running the script from the next statement.
Line 28: Line 66:
: <code>@_ExceptionDetails</code> If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.
: <code>@_ExceptionDetails</code> If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.
;<nowiki><other parameters></nowiki>
;<nowiki><other parameters></nowiki>
: All the rest of the passed parameters not listed above are added as extra headers to the request. Optional.
: All the rest of the passed parameters not listed above are added as extra headers to the request. For example, ''Content-Type'' and ''Accept'' HTTP headers can be added. Optional.


== Result ==
== Result ==
The result of the request is passed to the script following the CallWebService operation in the following variables:
The result of the request is passed to the script following the CallWebService operation in the following variables:
: <code>@_ResponseText</code> 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).  
: <code>@_ResponseText</code> 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).  
Line 37: Line 74:
: <code>@_ResponseSuccess</code> True only if the request returned status code that represents a success. BIT.
: <code>@_ResponseSuccess</code> True only if the request returned status code that represents a success. BIT.


== Example ==
See examples at the [[CallWebService Script Examples]] page.
<pre>
 
(SELECT 'Method', 'GET') UNION ALL
= --#Commit =
(SELECT 'Address', 'http://google.com') UNION ALL
[https://docs.microsoft.com/en-us/sql/t-sql/language-elements/commit-transaction-transact-sql?view=sql-server-ver15 Commits] the currently open SQL transaction in the sandbox database and starts a new transaction. The commit command can be executed at any point in the script. Note that the command does not have any parameters, i.e. there is no preceding SELECT statement before the --#Commit statement.
(SELECT 'ContentType', 'application/json') UNION ALL
 
(SELECT 'Accept', '*/*')
If the commit command is not used, the database transaction in the sandbox database is committed when the script is completed. On the other hand, if the script execution encounters an error, the SQL transaction is rolled back.
--#CallWebService
PRINT SUBSTRING(@_ResponseText, 1, 50);
</pre>


== Script Log Results ==
The commit command is useful in following circumstances:
When the script is run, entries similar to the following will be shown in the script log:
* If the sandbox database is configured to allow storing permanent objects, commit can be used to preserve changes even if the script execution encounters an error.
* When the scripting is handling large amount of data, it's better to make commits during the script run, so that the database transaction log doesn't grow too large.
* Committing changes makes them visible for other users in the database.


Example:
<pre>
<pre>
Execution duration: 0,753 seconds
--#Commit
Execution Log:
2015-09-14T13:59:49.2838661+03:00 Notification 85 Script operation: "--#CallWebService" started
2015-09-14T13:59:49.3468813+03:00 Notification 85 Address: http://google.com
Method: GET
ContentType: application/json
Encoding: Unicode (UTF-8)
Body content length: 0
Timeout: 60000
ExecuteInClientSide: 0
Additional headers: Accept(3)
2015-09-14T13:59:49.6579900+03:00 Notification 85 Script operation: "--#CallWebService" completed: Result: OK, text length: 53019, status code: 200
<nowiki>
2015-09-14T13:59:49.7230130+03:00 Notification 85 <!doctype html><html itemscope="" itemtype="http:/
</nowiki>
</pre>
</pre>


=<br/> --#Exit =
= --#Exit =
Stops the execution of the script and gives a message to the user. This command takes one SELECT query as its parameter.
Stops the execution of the script and gives a message to the user. 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:<br/>
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:<br/>
; 'Exit'
; Exit
: Defines whether to stop the script execution:
: 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.
: 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.
: 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'
; 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.
: 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'
; 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.
: 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'
; 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:
: 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.
: 1 = don't stop execution of the script, continue running the script from the next statement.
Line 91: Line 113:
: <code>@_ExceptionDetails</code> If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.
: <code>@_ExceptionDetails</code> If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.


== Examples ==
See examples at the [[Exit Script Examples]] page.
The Exit command can in effect be used to "call" a script, i.e. run a different script and then return to continue the current script.
<pre>
(SELECT 'Exit', '0') UNION ALL
(SELECT 'RunScriptId', '12') 
--#Exit
</pre>


The Exit command can also be used to "goto" a script, i.e. stop the execution of the current script and run a different script.
= --#GetAnalysis =
<pre>
(SELECT 'Exit', '1') UNION ALL 
(SELECT 'RunScriptId', '12') 
--#Exit
</pre>


The following example stops the script execution, gives a message, and runs a script with Id 12.
<div style="border:1px solid #dfdfdf;padding:0.5em 1em 0.5em 1em;background-color:#E7EAEC;margin:10px 0px 0px 10px;">
<pre>
--#GetAnalysis command is deprecated and it will be removed in a future release. Use the more flexible [[SQL_Scripting_Commands#--.23RunQuery|--#RunQuery]] command instead.
(SELECT 'Exit', '1') UNION ALL
</div>
(SELECT 'MessageText', 'Data from SAP not valid. Script execution will be terminated. Check source data. Running script Id 12.') UNION ALL
(SELECT 'RunScriptId', '12')
--#Exit
</pre>
 
The following example script fragment checks if the previous ProcessAnalyzer command had any exceptions, and if it did, will goto script with Id 2. If the previous command didn't have any exceptions, the script execution is stopped.
<pre>
DECLARE @ScriptToRun VARCHAR(10)
 
IF @_ExceptionOccurred = 1
SET @ScriptToRun = '2'
ELSE SET @ScriptToRun  = <nowiki>''</nowiki>
 
(SELECT 'Exit', '1') UNION ALL 
(SELECT 'RunScriptId', @ScriptToRun) 
--#Exit
</pre>


=<br/> --#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.​
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.


== 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:<br/>
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:<br/>
; '<Analysis Parameter>'
; <Analysis Parameter>
: See [[Analysis Parameters]] for a list of supported analysis parameters in QPR ProcessAnalyzer.
: The --#GetAnalysis command supports the following analysis types:
: The --#GetAnalysis command supports the following analysis types:
: Flowchart Analysis (0)
* DataTableAnalysis=18: Reads a data table from SQL server and stores it in temporary table​
: Variation Analysis in the Chart Mode (1)
* Etl=19
: Path Analysis (3)
* EtlReport=20
: Event Type Analysis in the Chart Mode (4)
* RunScript=25
: Case Analysis (5)
* ExpressionAnalysis=33
: Event Analysis (6)
; TargetTable
: Event Type Analysis in the Table Mode (7)
: 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.
: Variation Analysis in the Table Mode (8)
; Show
: Duration Analysis (9)
: 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.
: Profiling Analysis (10)
; Title
: User Report (11)
: Optional. Name of the CSV file created when Show is TRUE or 1. Default value is the name of the analysis type.
: Operation Log Analysis (12)
; CatchOperationExceptions
: Flow Analysis (13)
: Influence Analysis (14)
: Data Table Analysis (18)
: Model Report (21)
: Project Report (22)
: Data Table Report (23)
: Script Report (24)
: 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 [[Analysis Parameters|the list of analysis parameters]].
; 'TargetTable'
: The temporary table to which the analysis is to be stored. Note that only table format analyses can be stored to a temporary table. If the specified temporary table already exists in the database then its contents are deleted before storing analysis.
: You can define the 'TargetTable' when using the following analysis types:
: - Case Analysis
: - Event Analysis
: - Event Type Analysis
: - Variation Analysis
: - User Permissions
: - Operation Log
: - Flow Analysis
: - Influence Analysis
: - Integration Table
: - Model Report
: - Project Report
: - Data Table Report
: - Script Report
; 'Show'
: Optional. If TRUE or 1, the analysis is opened after the script is run.
; '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.
; '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:
: 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.
: 1 = don't stop execution of the script, continue running the script from the next statement.
Line 186: Line 146:
:<code>@_ExceptionMessage</code> If there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
:<code>@_ExceptionMessage</code> If there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
:<code>@_ExceptionDetails</code> If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.
:<code>@_ExceptionDetails</code> If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.
; MaximumCount
: Used with Operation Log Analysis analysis type. Integer. The maximum amount of rows returned. Optional. Default value is 1000.


== Examples ==
See examples at the [[GetAnalysis Script Examples]] page.
Below are listed examples of each supported analysis type using the GetAnalysis command. <br/>
The following example will get a Case analysis and open that analysis with "Case Analysis From Script" as the title on a sheet named "Case Analysis Sheet".
<pre>
(SELECT 'AnalysisType', '5') UNION ALL
(SELECT 'ProjectName', '<ProjectName>') UNION ALL
(SELECT 'ModelName', '<ModelName>') UNION ALL
(SELECT 'MaximumCount', '10') UNION ALL
(SELECT 'Title', 'Case Analysis From Script') UNION ALL
(SELECT 'SheetName', 'Case Analysis Sheet') UNION ALL
(SELECT 'Show', 'True')
--#GetAnalysis
</pre>
 
The following example will get an Event analysis, open that analysis with "Analysis Title" as the title on a sheet named "Example Sheet Name", and store the Event analysis results to the "#ExampleTable" data table.
<pre>
(SELECT 'AnalysisType', '6') UNION ALL
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'FilterId', '3') UNION ALL
(SELECT 'SelectedEventAttributes', '*') UNION ALL
(SELECT 'Show', '1') UNION ALL
(SELECT 'Title', 'Analysis Title') UNION ALL
(SELECT 'SheetName', 'Excel Sheet Name') UNION ALL
(SELECT 'TargetTable', '#ExampleTable')
--#GetAnalysis
</pre>
The following example will get a Duration analysis and open that analysis with "Duration Analysis From Script" as the title on a sheet named "Duration Analysis Sheet".
<pre>
SELECT 'AnalysisType', '9') UNION ALL
(SELECT 'ProjectName', '<ProjectName>') UNION ALL
(SELECT 'ModelName', '<ModelName>') UNION ALL
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'Granularity', '1') UNION ALL
(SELECT 'Title', 'Duration Analysis From Script') UNION ALL
(SELECT 'SheetName', 'Duration Analysis Sheet') UNION ALL
(SELECT 'Show', 'True')
--#GetAnalysis
</pre>
The following example will load data from the "ExampleTable" data table in the "ExampleProject" project and put that data into the "CSV1" table.
<pre>
(SELECT 'AnalysisType', '18') UNION ALL
(SELECT 'ProjectName', 'ExampleProject') UNION ALL
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'DataTableName', 'ExampleTable') UNION ALL
(SELECT 'TargetTable', '#CSV1')
--#GetAnalysis
</pre>
 
The following example will get a Model Report analysis, store the analysis results to a temporary table called "#ModelResult" and show the results on an Excel sheet. This table contains the same information as is visible in the Project Workspace. For explanations of the columns, see [[Workspace#Models|Models]].
<pre>
(SELECT 'AnalysisType', '21') UNION ALL
(SELECT 'Show', '1') UNION ALL
(SELECT 'TargetTable', '#ModelResult')
--#GetAnalysis
</pre>
 
The following example will get a Project Report analysis, store the analysis results to a temporary table called "#ProjectResult" and show the results on an Excel sheet. This table contains the same information as is visible in the Project Workspace. For explanations of the columns, see [[Workspace#Models|Models]].
<pre>
(SELECT 'AnalysisType', '22') UNION ALL
(SELECT 'Show', '1') UNION ALL
(SELECT 'TargetTable', '#ProjectResult')
--#GetAnalysis
</pre>
The following example will get a Data Table Report analysis, store the analysis results to a temporary table called "#DataTableResult" and show the results on an Excel sheet. This table contains the same information as is visible in the Project Workspace. For explanations of the columns, see [[Workspace#Data_Tables|Data Tables]].
<pre>
(SELECT 'AnalysisType', '23') UNION ALL
(SELECT 'Show', '1') UNION ALL
(SELECT 'TargetTable', '#DataTableResult')
--#GetAnalysis
</pre>
 
The following example will get a Script Report analysis, store the analysis results to a temporary table called "#ScriptResult" and show the results on an Excel sheet. This table contains the same information as is visible in the Manage Scripts dialog. For explanations of the columns, see [[Script_Management#Script_Manager|Manage Scripts]].
<pre>
(SELECT 'AnalysisType', '24') UNION ALL
(SELECT 'Show', '1') UNION ALL
(SELECT 'TargetTable', '#ScriptResult')
--#GetAnalysis
</pre>
 
=<br/> --#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.
 
== 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:<br/>
; '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 [[Model Properties|Case Attribute Model]], those case attributes are not deleted. Not used when creating a new model. Default value is TRUE.
; '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:
: <code>@_ExceptionOccurred</code> If there was an exception, then this value is 1, otherwise 0. INT
: <code>@_ExceptionType</code> If there was an exception, shows the C# class name for the exception, NVARCHAR(MAX), otherwise NULL.
: <code>@_ExceptionMessage</code> If there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
: <code>@_ExceptionDetails</code> if there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.


== Second Query ==
= --#ImportDataTable =
; <nowiki>'<data>'</nowiki>
Imports data from an SQL query to a datatable. This command takes two SELECT queries as parameters.
: 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.


=<br/> --#ImportDataTable =
Imports data to a Data Table. This command takes two SELECT queries as parameters.
== First Query ==
== 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:<br/>
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:<br/>
; 'ProjectId' or 'ProjectName'
; ProjectId or ProjectName
: The id or the name of the project in which the target data table exists.
: The id or the name of the project in which the target data table exists.
; 'DataTableId' or 'DataTableName'
; DataTableId or DataTableName
: The id or the name of the existing/new target data table.
: The id or the name of the existing/new target data table.
; 'Append'
; 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.
: Defines what to do with an existing contents of the target datatable. When value is 1, existing rows in the target datatable are not deleted (also new columns in the imported data are created to the datatable). When value is 0, existing rows in the target datatable are deleted before the import (columns are still preserved). Not used when creating a new data table.
; 'CatchOperationExceptions'
; 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:
: 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.
: 1 = don't stop execution of the script, continue running the script from the next statement.
Line 312: Line 173:


== Second Query ==
== Second Query ==
; <nowiki>'<data>'</nowiki>
; <nowiki><data></nowiki>
: 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.
: The database query whose results are to be imported. Note that if the query doesn't return any data, the datatable is not created.


== Examples ==
See examples at the [[ImportDataTable Script Examples]] page.
The following example will load data from a model and put it into the "AnalysisResult" table and then add that data to the "ExampleTable" data table.
<pre>
SELECT 'START'
--#WriteLog
 
(SELECT 'AnalysisType', '6') UNION ALL
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'FilterId', '3') UNION ALL
(SELECT 'SelectedEventAttributes', '*') UNION ALL
(SELECT 'TargetTable', '#AnalysisResult')
--#GetAnalysis
 
SELECT 'DataLoaded'
--#WriteLog
 
SELECT count(*) from [#AnalysisResult]
--#WriteLog
 
(SELECT 'ProjectId', '1') UNION ALL
(SELECT 'DataTableName', 'ExampleTable') UNION ALL
(SELECT 'Append', '1')
(SELECT * FROM [#AnalysisResult])
--#ImportDataTable
</pre>
 
The following example will load data from the "ExampleTable" data table in the "ExampleProject" project, put that data into the "CSV1" table and then show the contents of the "CSV" table. In effect, it shows the contents of the "ExampleTable" data table.
<pre>
(SELECT 'AnalysisType', '18') UNION ALL
(SELECT 'ProjectName', 'ExampleProject') UNION ALL
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'DataTableName', 'ExampleTable') UNION ALL
(SELECT 'TargetTable', '#CSV1')
--#GetAnalysis
 
(SELECT * FROM #CSV1)
(SELECT 'Title', 'CSV Table') UNION ALL
(SELECT 'MaximumCount', '0')
--#ShowReport
</pre>
 
=<br/> --#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.
== 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:<br/>
; '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.
; '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:
: <code>@_ExceptionOccurred</code> If there was an exception, then this value is 1, otherwise 0. INT
: <code>@_ExceptionType</code> If there was an exception, shows the C# class name for the exception, NVARCHAR(MAX), otherwise NULL.
: <code>@_ExceptionMessage</code> If there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
: <code>@_ExceptionDetails</code> If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.


== Second Query ==
= --#ImportOdbcQuery =
; <nowiki>'<data>'</nowiki>
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:
: 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.


=<br/> --#ImportOdbcQuery =
; 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.  
Extracts data directly from the ODBC data source and imports it to QPR ProcessAnalyzer Data Table or QPR ProcessAnalyzer temporary table.  Column names are parsed from the query result. 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.
; ProjectId / ProjectName
== 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:<br/>
; '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' or 'ProjectName'
: The id or the name of the project in which the target data table exists.
: The id or the name of the project in which the target data table exists.
; 'DataTableId' or 'DataTableName'
; DataTableId / DataTableName
: The id or the name of the existing/new target data table.
: The id or the name of the existing/new target data table.
; 'Append'
; 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.
: 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'
; 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:
: 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.
: 1 = don't stop execution of the script, continue running the script from the next statement.
Line 400: Line 197:
:<code>@_ExceptionMessage</code> If there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
:<code>@_ExceptionMessage</code> If there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
:<code>@_ExceptionDetails</code> If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.
:<code>@_ExceptionDetails</code> If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.
 
; OdbcConnectionString
'''ODBC specific parameters'''
: The ODBC driver connection string that includes the settings needed to establish the initial connection. Mandatory. See [http://msdn.microsoft.com/en-us/library/system.data.odbc.odbcconnection.connectionstring%28v=vs.110%29.aspx?cs-save-lang=1&cs-lang=csharp#code-snippet-1 OdbcConnection.ConnectionString Property in Microsoft Development Network] for more information on the possible connection strings.
; 'OdbcConnectionString'
; OdbcConnectionStringKey
: The ODBC driver connection string that includes the settings needed to establish the initial connection. Mandatory. See [http://msdn.microsoft.com/en-us/library/system.data.odbc.odbcconnection.connectionstring%28v=vs.110%29.aspx?cs-save-lang=1&cs-lang=csharp#code-snippet-1 OdbcConnection.ConnectionString Property in Microsoft Development Network] for more information on the possible connection strings. You can also configure a data source name for connecting to QPR ProcessAnalyzer, for instructions see [[How_to_Configure_an_ODBC_Data_Source_Name_for_Connecting_to_QPR_ProcessAnalyzer|How to Configure an ODBC Data Source Name for Connecting to QPR ProcessAnalyzer]].
: [[Storing_Secrets_for_Scripts|Secret name]] for the connection string. Alternative for the OdbcConnectionString property.
; 'OdbcQueryString'
; 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.
: 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'
; QueryExecutionTimeout
: 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. Not used with QPR ProcessAnalyzer Xpress or QPR ProcessAnalyzer Database as they always execute the command in the client 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.
: Defines timeout in seconds for the ODBC command execution. If not specified, default value is 600 seconds.
; ExecuteInClientSide
: Defines whether the command is executed from the QPR ScriptLauncher or from the server. This parameter is used when there is no server connection available, for example. TRUE or 1, the query is executed in the QPR ScriptLauncher. FALSE or 0, the query is executed in the server. 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 1, you will receive an error message. Optional. Default value is FALSE.


== Example ==
See examples in the [[ImportOdbcQuery Script Examples]] page.
The following script extracts data from an ODBC using a data source name configured as described in the link above and selects all columns from the table PA_MODEL.
<pre>
(SELECT 'OdbcConnectionString', 'DSN=PA_EXPRESS_40') UNION ALL
(SELECT 'OdbcQueryString', 'SELECT * FROM PA_MODEL') UNION ALL
(SELECT 'TargetTable', '#ImportOdbcTable') UNION ALL
(SELECT 'Append', '1')
--#ImportOdbcQuery
</pre>


=<br/> --#ImportOleDbQuery =
= --#ImportOleDbQuery =
Extracts data from an OLE DB source and imports it to QPR ProcessAnalyzer Data Table or QPR ProcessAnalyzer temporary table. Column names are parsed from the query result. It is possible to both create new Data Tables as well as modify existing Data Tables with this command.
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:


== Query ==
; TargetTable
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:<br/>
; '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.
: 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' or 'ProjectName'
; ProjectId / ProjectName
: The id or the name of the project in which the target data table exists.
: The id or the name of the project in which the target data table exists.
; 'DataTableId' or 'DataTableName'
; DataTableId / DataTableName
: The id or the name of the existing/new target data table.
: The id or the name of the existing/new target data table.
; 'Append'
; 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.
: 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'
; 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:
: 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.
: 1 = don't stop execution of the script, continue running the script from the next statement.
Line 441: Line 230:
:<code>@_ExceptionMessage</code> If there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
:<code>@_ExceptionMessage</code> If there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
:<code>@_ExceptionDetails</code> If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.
:<code>@_ExceptionDetails</code> If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.
'''OLE DB Query Parameters'''
; OleDbConnectionString
; 'OleDbConnectionString'
: The OLE DB connection string that includes the settings needed to establish the initial connection. Mandatory. See [http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbconnection.connectionstring%28v=vs.110%29.aspx OleDbConnection.ConnectionString Property in Microsoft Development Network] for more information on the possible connection strings.
: The OLE DB connection string that includes the settings needed to establish the initial connection. Mandatory. See [http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbconnection.connectionstring%28v=vs.110%29.aspx OleDbConnection.ConnectionString Property in Microsoft Development Network] for more information on the possible connection strings.
; 'OleDbQueryString'
; 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.
: 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'
; QueryExecutionTimeout
: 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. Not used with QPR ProcessAnalyzer Xpress or QPR ProcessAnalyzer Database as they always execute the command in the client 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.
: Defines timeout in seconds for the OLE DB command execution. If not specified, default value is 600 seconds.
; ExecuteInClientSide
: Defines whether the command is executed from the QPR ScriptLauncher or from the server. This parameter is used when there is no server connection available, for example. TRUE or 1, the query is executed in the QPR ScriptLauncher. FALSE or 0, the query is executed in the server. 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.


== Examples ==
See examples at the [[ImportOleDbQuery Script Examples]] page.
The following example will load data from an OLE DB source (a sample database called DB1) and selects all columns from the table EXAMPLE. It will then put that data into the "#TABLE" temporary table and then show the contents of that table.
<pre>
(SELECT 'OleDbConnectionString', 'Provider=SQLOLEDB;Data Source=(local);Initial Catalog=DB1;Integrated Security=SSPI;') UNION ALL
(SELECT 'OleDbQueryString', 'SELECT * FROM EXAMPLE') UNION ALL
(SELECT 'TargetTable', '#TABLE') UNION ALL
(SELECT 'Append', '1')
--#ImportOleDbQuery


(SELECT * FROM #TABLE) UNION ALL
= --#ImportSalesforceQuery =
(SELECT 'MaximumCount', '0')
Extracts data from the Salesforce cloud using its REST API and imports the data to a datatable. The command takes one SELECT query as its parameter. If the query doesn't return any data, the target data table or temporary table is not created.
--#ShowReport
</pre>


The following example will load all the columns from "EXAMPLE" table from an OLE DB source and will put that data into the "ExampleDataTable" in the "ExampleProject" project. It will then get the Data Table analysis from the "ExampleDataTable", put that into the "#TABLE" temporary table and then show the contents of that table.  
More information about the Salesforce REST API: https://developer.salesforce.com/docs/atlas.en-us.api_rest.meta/api_rest/intro_rest.htm.
<pre>
(SELECT 'ProjectName', 'ExampleProject') UNION ALL
(SELECT 'DataTableName', 'ExampleDataTable') UNION ALL
(SELECT 'Append', '0') UNION ALL
(SELECT 'OleDbConnectionString', 'Provider=SQLOLEDB;Data Source=(local);Initial Catalog=DB1;Integrated Security=SSPI;') UNION ALL
(SELECT 'OleDbQueryString', 'SELECT * FROM EXAMPLE')
--#ImportOleDbQuery


(SELECT 'AnalysisType', '18') UNION ALL
(SELECT 'ProjectName', 'ExampleProject') UNION ALL
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'DataTableName', 'ExampleDataTable') UNION ALL
(SELECT 'TargetTable', '#TABLE')
--#GetAnalysis
(SELECT * FROM #TABLE)
(SELECT 'MaximumCount', '0')
--#ShowReport
</pre>
=<br/> --#ImportSalesforceQuery =
Extracts data from Salesforce cloud and imports it into a data table as NVARCHAR(MAX) or SQL_VARIANT type data. Note that this command requires the Salesforce username and password to be visible in the script!
This command takes one SELECT query as its parameter.
== Query ==
== 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:<br/>
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:<br/>
; 'TargetTable'
; 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.
: Temporary table to which the data is imported. If not used, define the target using the ProjectId/ProjectName, DataTableId/DataTableName, and Append parameters described below.
; 'ProjectId' or 'ProjectName'
; ProjectId / ProjectName
: The id or the name of the project in which the target data table exists.
: Id or the name of the project in which the target datatable is located.
; 'DataTableId' or 'DataTableName'
; DataTableId / DataTableName
: The id or the name of the existing/new target data table.
: Id or the name of the target data table. If DataTableName is used, the ProjectId or ProjectName can also be used to define the project where the datatable is located.
; 'Append'
; 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.
: Defines what to do with an existing target data table contents. TRUE or 1, existing contents of the target datatable is not deleted in the import. When FALSE or 0, existing contents of the target datatable are deleted before importing new data. Not used when creating a new data table.
; 'CatchOperationExceptions'
; 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:
: 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.
: 1 = don't stop execution of the script, continue running the script from the next statement.
Line 507: Line 265:
:<code>@_ExceptionMessage</code> If there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
:<code>@_ExceptionMessage</code> If there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
:<code>@_ExceptionDetails</code> If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.
:<code>@_ExceptionDetails</code> 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
; 'SalesforceUser'
: Username for the Salesforce cloud.
: Username for the Salesforce cloud.
; 'SalesforcePW'
; SalesforcePW
: Password for the Salesforce cloud.
: Password for the Salesforce cloud.
; 'SalesforceUrl'
; SalesforcePWKey
: Optional. Salesforce service Url for the Salesforce cloud. Default value for QPR ProcessAnalyzer 2016.1 version is <nowiki>'https://login.salesforce.com/services/Soap/u/27.0'</nowiki>. In order to connect to Salesforce's test environment the URL should be like: <nowiki>'https://test.salesforce.com/services/Soap/u/27.0'</nowiki>.
: [[Storing_Secrets_for_Scripts|Secret name]] for the stored Salesforce password. Alternative for the SalesforcePW property.
; 'SalesforceQueryMode'
; SalesforceUrl
: Optional. The Salesforce query function to be used. 1 (default) = [http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_queryall.htm queryall()], 2 = [http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_query.htm query()], 3 = [http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_describesobject.htm describeSObject()].
: Optional. Salesforce web service url.
; 'SalesforceQuery'
; SalesforceQueryMode
: The query to be run in the Salesforce cloud. Note that "*" cannot be used in the query. See [http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_query.htm Salesforce API] and [http://www.salesforce.com/us/developer/docs/soql_sosl/index.htm 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.
: Optional. Determines which Salesforce query function to use. One of the following values (1, 2 or 3) can be used:
; 'SalesforceQueryRetries'
: 1: '''QueryAll''' (default): Executes specified SOQL query, except unlike ''Query'', ''QueryAll'' returns records that are deleted because of a merge or delete. More information: https://developer.salesforce.com/docs/atlas.en-us.api_rest.meta/api_rest/resources_queryall.htm.
: 2: '''Query''': Executes the specified SOQL query. More information: https://developer.salesforce.com/docs/atlas.en-us.api_rest.meta/api_rest/resources_query.htm)
: 3: '''sObject Describe''': Completely describes the individual metadata at all levels for the specified object. For example, this can be used to retrieve the fields, URLs, and child relationships for the Account object. More information: https://developer.salesforce.com/docs/atlas.en-us.api_rest.meta/api_rest/resources_sobject_describe.htm).
; SalesforceQuery
: Query to run in the Salesforce cloud to fetch the data, defined as SOQL (Salesforce Object Query Language). More information: https://developer.salesforce.com/docs/atlas.en-us.236.0.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_sosl_intro.htm.
; SalesforceQueryRetries
: Optional. Number of retries to attempt if the Salesforce query doesn't succeed. Default value is 3.
: Optional. Number of retries to attempt if the Salesforce query doesn't succeed. Default value is 3.
; 'SalesforceQueryRetryWait'
; SalesforceQueryRetryWait
: Optional. Number of milliseconds to wait between query retries. Default is 3000 ms.
: Optional. Number of milliseconds to wait between query retries. Default is 3000 ms.
; 'SalesforceBatchSize'
; SalesforceBatchSize
: Optional. The number of rows of data the query returns in one batch. Minimum = 200, Maximum = 2000, Default = 500. See [http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_query.htm Salesforce API] for more information.
: Optional. Data is queried from Salesforce in batches, and this setting determines the batch size. The value can be between 200 and 2000, and the default value is 500.
 
== Example ==
The following example will load date data from the "Contact" table in the Salesforce cloud and put that data into the "ExampleDataTable" data table in the "ExampleProject" project. It will then get the Data Table analysis from the "ExampleDataTable", put that into the "#TABLE" temporary table and then show the contents of that table.
<pre>
(SELECT 'ProjectName', 'ExampleProject') UNION ALL
(SELECT 'DataTableName', 'ExampleDataTable') UNION ALL
(SELECT 'Append', '0') UNION ALL
(SELECT 'SalesforceUser', 'example.user@qpr.com')  UNION ALL
(SELECT 'SalesforcePW', 'examplepassword')  UNION ALL
(SELECT 'SalesforceQuery', 'SELECT CreatedDate FROM Contact')
--#ImportSalesforceQuery


(SELECT 'AnalysisType', '18') UNION ALL
== Notes ==
(SELECT 'ProjectName', 'ExampleProject') UNION ALL
If you get error ''INVALID_TYPE sObject type 'Objectname' is not supported'':
(SELECT 'MaximumCount', '0') UNION ALL
* Check that the object in question exists or that the object name is correct.
(SELECT 'DataTableName', 'ExampleDataTable') UNION ALL
* Verify that the Salesforce user has rights to the object.
(SELECT 'TargetTable', '#TABLE')  
** You have to give access to the new custom objects and VisualForce pages from the user's profile, and you have to check the "Customize Application" checkbox under the same profile (https://developer.salesforce.com/forums/?id=906F00000008qG6IAI). Contact your Salesforce administrator.
--#GetAnalysis
* The Salesforce user may need extra license to access the object. Special 3rd party custom objects may need separate license. Contact your Salesforce application administrator.


(SELECT * FROM #TABLE)
See examples at the [[ImportSalesforceQuery Script Examples]] page.
(SELECT 'MaximumCount', '0')
--#ShowReport
</pre>


=<br/> --#ImportSapQuery =
= --#ImportSapQuery =
Extracts data from SAP and imports it to QPR ProcessAnalyzer Data Table or QPR ProcessAnalyzer temporary table. Column names are parsed from the query result. 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 [[Installing_QPR_ProcessAnalyzer#Installing_SAP_Connector_for_Microsoft_.NET_Version_3.0_Dll_Files|some dlls]] not provided by QPR Software.
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 [[QPR_ProcessAnalyzer_ScriptLauncher#Installing_SAP_NetWeaver_RFC_Library|installing SAP NetWeaver RFC Library]].


== Query ==
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:
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:<br/>
; TargetTable
; '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.
: If this parameter is given, store the results into a temporary SQL table in ETL sandbox.
; ProjectId / ProjectName
If the TargetTable parameter is not given, use the following destination parameters:
; 'ProjectId' or 'ProjectName'
: The id or the name of the project in which the target data table exists.
: The id or the name of the project in which the target data table exists.
; 'DataTableId' or 'DataTableName'
; DataTableId / DataTableName
: The id or the name of the existing/new target data table.
: The id or the name of the existing/new target data table.
; 'Append'
; 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.
: 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'
; ConvertDataTypes
: List of SAP data types that are converted into respective data types supported by SQL Server instead of using NVARCHAR. Defined by listing the data type identifier characters in any order. Available data type identifying characters are '''IFPCDTNX'''. If not defined, all data is converted to NVARCHAR. Example: ''IFP'' (convert only numeric data types: Integer, Float, Packed number) ([[Importing_Data_from_SAP|more information]]).
; 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:
: 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.
: 1 = don't stop execution of the script, continue running the script from the next statement.
Line 571: Line 319:
:<code>@_ExceptionMessage</code> If there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
:<code>@_ExceptionMessage</code> If there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
:<code>@_ExceptionDetails</code> If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.
:<code>@_ExceptionDetails</code> If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.
 
; SapUser
'''SAP Connection Parameters:'''
; 'SapUser'
: SAP username used to connect to SAP. Mandatory. Corresponds to the "USER" constant on SAP side. See the [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
: SAP username used to connect to SAP. Mandatory. Corresponds to the "USER" constant on SAP side. See the [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
; 'SapPW'
; SapPW
: Password of the SAP user used to connect to SAP. Mandatory. Corresponds to the "PASSWD" constant on SAP side. See the [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
: Password of the SAP user used to connect to SAP. Mandatory. Corresponds to the "PASSWD" constant on SAP side. See the [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
; 'SapClient'
; SapPWKey
: [[Storing_Secrets_for_Scripts|Secret name]] 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 [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
: The SAP backend client. Mandatory. Corresponds to the "CLIENT" constant on SAP side. See the [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
; 'SapAppServerHost'
; 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 [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
: 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 [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
; 'SapMessageServerHost'
; 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 [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
: 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 [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
; 'SapSystemNumber'
; SapSystemNumber
: The SAP system’s system number. Mandatory if SapSystemID is not defined. Corresponds to the "SYSNR" constant on SAP side. See the [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
: The SAP system’s system number. Mandatory if SapSystemID is not defined. Corresponds to the "SYSNR" constant on SAP side. See the [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
; 'SapSystemID'
; 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 [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
: The SAP system’s three-letter system ID. Mandatory if SapSystemNumber is not defined. Corresponds to the "SYSID" constant on SAP side. See the [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
; 'ExecuteInClientSide'
; 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. Not used with QPR ProcessAnalyzer Xpress or QPR ProcessAnalyzer Database as they always execute the command in the client 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.
: Defines whether the command is executed from the QPR ScriptLauncher or from the server. This parameter is used when there is no server connection available, for example. TRUE or 1, the query is executed in the QPR ScriptLauncher. FALSE or 0, the query is executed in the server. 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
'''Other SAP Parameters:'''
; 'SapLanguage'
: SAP language used. Default value is "EN". Optional. Corresponds to the "LANG" constant on SAP side. See the [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
: SAP language used. Default value is "EN". Optional. Corresponds to the "LANG" constant on SAP side. See the [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
; 'SapPoolSize'
; 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 [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
: 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 [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
; 'SapPeakConnectionsLimit'
; SapMaxPoolSize
: 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 [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
: 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 [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
; 'SapConnectionIdleTimeout'
; 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 [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
: 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 [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
; 'SapRouter'
; 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 [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
: 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 [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
; 'SapLogonGroup'
; 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 [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
: The logon group from which the message server shall select an application server. Optional. Corresponds to the "GROUP" constant on SAP side. See the [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
; 'SapQueryMode'
; 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 [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
: 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 [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
; 'SapQueryTable'
; 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 [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm 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.
: 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 [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm 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'
; 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 [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
: 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 [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
; 'SapRowskips'
; 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 [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
: 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 [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
; 'SapWhereClause'
; 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 [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
: 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 [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
; 'SapWhereClauseSelect'
; 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 [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.<br/>
: 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 [[QPR_ProcessAnalyzer_Logs#Script_Log|Script Log]]. See the [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.<br/>
: See also [[Data_Extraction,_Transformation,_and_Loading#Client_Exception_When_Using_.23ImportSapQuery_Command|Troubleshooting]] for other SAP related limitations.
; SapFieldNames
; '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 [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
: 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 [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
; 'SapFunction'
; 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 [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
: 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 [http://help.sap.com/saphelp_nw04/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm SAP .NET Connector documentation] for more info.
; UseAnyAsColumnType
: Determines datatable column data types for the created columns. When ''true'', "Any" type of columns are created (resulting into SQL_variant columns in SQL server), and when ''false'', data types depend on the ConvertDataTypes parameter. Default value is true when running the import in SQL script, and otherwise default value is false.
;AliasUser
:
;AppServerService
:
;CharacterFaultIndicatorToken
:
;Codepage
:
;GatewayHost
:
;GatewayService
:
;IdleCheckTime
:
;LogonCheck
:
;MaxPoolWaitTime
:
;MessageServerService
:
;Name
:
;NoCompression
:
;OnCharacterConversionError
:
;PartnerCharSize
:
;PasswordChangeEnforced
:
;ProgramId
:
;R3Name
:
;RegistrationCount
:
;RepositoryDestination
:
;RepositoryPassword
:
;RepositorySncMyName
:
;RepositoryUser
:
;RepositoryX509Certificate
:
;SapSso2Ticket
:
;SncLibraryPath
:Full path including file name of the [[Importing_Data_from_SAP#SNC_encrypted_connection|SNC]] shared library to be used.
;SncMode
: Determines whether connections will be secured with [[Importing_Data_from_SAP#SNC_encrypted_connection|SNC]]. Value '''0''' doesn't use SNC (default) and value '''1''' uses SNC.
;SncMyName
:Token/identifier representing the external RFC program. In most cases this can be omitted. The installed [[Importing_Data_from_SAP#SNC_encrypted_connection|SNC]] solution usually knows its own SNC name. Only for solutions supporting “multiple identities”, you may Varies depending on the installed SNC solution (Secude, Kerberos, NTLM, etc). Example for Secude: p/secude:CN=ALEREMOT SAP Online Help 09.09.2014 SAP .NET Connector 3.0 41 need to specify the identity to be used for this particular destination/server. E, O=Mustermann-AG, C=DE
;SncPartnerName
:The backend's [[Importing_Data_from_SAP#SNC_encrypted_connection|SNC]]name.
;SncPartnerNames
:
;SncQop
:Quality of service to be used for SNC communication of this particular destination/server. One of the following values:
* 1: Digital signature
* 2: Digital signature and encryption
* 3: Digital signature, encryption, and user authentication
* 8: Default value defined by back-end system
* 9: Maximum value that the current security product supports
;SystemIds
:
;UseSapGui
:
;X509Certificate
:


== Examples ==
See examples at the [[ImportSapQuery Script Examples]] page.
The following script will get the "VBELN", "ERDAT", "ERZET", "ERNAM", "NETWR", and "WAERK" columns from the "VBAK" table in a SAP system and put them into a data table named "SapQueryTableExample".
<pre>
(SELECT 'ProjectName', 'ImportSapQueryExample') UNION ALL
(SELECT 'DataTableName', 'SapQueryTableExample') UNION ALL
(SELECT 'Append', 'TRUE') UNION ALL
(SELECT 'SapUser', 'exampleuser') UNION ALL
(SELECT 'SapPW', 'examplepassword') UNION ALL
(SELECT 'SapClient', '200') UNION ALL
(SELECT 'SapAppServerHost', '127.0.0.1') UNION ALL
(SELECT 'SapSystemNumber', '10') UNION ALL
(SELECT 'SapLanguage', 'EN') UNION ALL
(SELECT 'SapPoolSize', '5') UNION ALL
(SELECT 'SapPeakConnectionsLimit', '10') UNION ALL
(SELECT 'SapConnectionIdleTimeout', '600') UNION ALL
(SELECT 'SapRouter', '/H/127.0.0.1/A/1234/H/') UNION ALL
(SELECT 'SapLogonGroup', 'GROUPXNAME') UNION ALL
(SELECT 'SapQueryMode', '1') UNION ALL
(SELECT 'SapQueryTable', 'VBAK') UNION ALL
(SELECT 'SapDelimiter', '|') UNION ALL
(SELECT 'SapRowcount', '0') UNION ALL
(SELECT 'SapRowskips', '0') UNION ALL
(SELECT 'SapWhereClause', 'VBELN EQ `0060000039`, OR VBELN EQ `0060000040`') UNION ALL
(SELECT 'SapFieldNames', 'VBELN,ERDAT,ERZET,ERNAM,NETWR,WAERK')
--#ImportSapQuery
</pre>
 
The following script will extract values for the VBELN field from the VBAK table where the value of the VBELN field is between 0060000039` and `0060000041. It will also catch possible exceptions when getting the data and print out them on a separate sheet. The extracted data is also shown on its own sheet:
<pre>
(SELECT 'CatchOperationExceptions', '1') UNION ALL
(SELECT 'SapAppServerHost', '127.0.0.1') UNION ALL
(SELECT 'SapSystemNumber', '10') UNION ALL
(SELECT 'SapUser', 'qpr') UNION ALL
(SELECT 'SapPW', 'demo') UNION ALL
(SELECT 'SapRouter', '') UNION ALL
(SELECT 'SapClient', '200') UNION ALL
(SELECT 'SapLanguage', 'EN') UNION ALL
(SELECT 'SapPoolSize', '5') UNION ALL
(SELECT 'SapPeakConnectionsLimit', '10') UNION ALL
(SELECT 'SapConnectionIdleTimeout', '600') UNION ALL
(SELECT 'ExecuteInClientSide', '1') UNION ALL
(SELECT 'TargetTable', '#SAPmode1') UNION ALL
(SELECT 'Append', '0') UNION ALL
(SELECT 'SapWhereClause', 'VBELN BETWEEN `0060000039` AND `0060000041`') UNION ALL
(SELECT 'SapQueryTable', 'VBAK')
--#ImportSapQuery
 
DECLARE @_SuccessOrNot as NVARCHAR(MAX);
SET @_SuccessOrNot = CASE @_ExceptionOccurred
WHEN 1 THEN
'Exception(s) occurred!'
ELSE
'SAP import OK.'
END
 
SELECT
@_SuccessOrNot as Result,
@_ExceptionOccurred as ExceptionOccurred,
@_ExceptionType as ExceptionType,
@_ExceptionMessage as ExceptionMessage,
@_ExceptionDetails as ExceptionDetails
(SELECT 'SheetName' , 'ExceptionData')
--#ShowReport
 
(SELECT * FROM #SAPmode1)
(SELECT 'SheetName' , 'SAPmode1')
--#ShowReport
</pre>
 
The following script will return the values for the VBELN, ERDAT, ERZET, ERNAM, NETWR, and WAERK fields from the VBAK table where the value of the VBELN field is between 0060000039 and 0060000041:
<pre>
/* First, create the temporary table that holds the WHERE clause. */
CREATE TABLE #SapWhereClauseTable (sap_select_string varchar(255), order_number int)
INSERT INTO #SapWhereClauseTable SELECT <nowiki>'VBELN BETWEEN ''0060000039'''</nowiki>, 1
INSERT INTO #SapWhereClauseTable SELECT <nowiki>'AND ''0060000041'''</nowiki>, 2
 
/* Specify the target for the data that the script extracts from SAP */
(SELECT 'TargetTable', '#SAPmode1') UNION ALL
(SELECT 'Append', '0') UNION ALL
 
/* Define the SAP connection parameters */
(SELECT 'SapAppServerHost', '127.0.0.1') UNION ALL
(SELECT 'SapSystemNumber', '10') UNION ALL
(SELECT 'SapUser', 'exampleuser') UNION ALL
(SELECT 'SapPW', 'examplepassword') UNION ALL
(SELECT 'SapClient', '200') UNION ALL
(SELECT 'SapLanguage', 'EN') UNION ALL
(SELECT 'SapPoolSize', '5') UNION ALL
(SELECT 'SapPeakConnectionsLimit', '10') UNION ALL
(SELECT 'SapConnectionIdleTimeout', '600') UNION ALL
(SELECT 'SapFieldNames', 'VBELN,ERDAT,ERZET,ERNAM,NETWR,WAERK') UNION ALL
 
/* Use the WHERE clause defined in the temporary table */
(SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number') UNION ALL
(SELECT 'SapQueryTable', 'VBAK')
--#ImportSapQuery
 
/* Show the results */
(SELECT * FROM #SAPmode1)
(SELECT 'SheetName' , 'SAPmode1')
--#ShowReport
</pre>
 
The following script will get the "VBELN", "ERDAT", "ERZET", "ERNAM", "NETWR", and "WAERK" columns from the "VBAK" table where the value of the VBELN field is between 0060000039 and 0060000041 and put them into a data table named "SapQueryTableExample". The query is made on the client side.
<pre>
/* First, create the temporary table that holds the WHERE clause.*/
CREATE TABLE #SapWhereClauseTable (sap_select_string varchar(255), order_number int)
INSERT INTO #SapWhereClauseTable SELECT 'VBELN BETWEEN ''0060000039''', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND ''0060000042''', 2
 
/* Define that the command is executed in the client side.*/
(SELECT 'ExecuteInClientSide', 'True') UNION ALL
 
/* Specify the data table where the data is imported into.*/
(SELECT 'DataTableName', 'SapQueryTableExample') UNION ALL
(SELECT 'Append', '0') UNION ALL
 
/* Define the SAP connection parameters.*/
(SELECT 'SapAppServerHost', '127.0.0.1') UNION ALL
(SELECT 'SapSystemNumber', '10') UNION ALL
(SELECT 'SapUser', 'exampleuser') UNION ALL
(SELECT 'SapPW', 'examplepassword') UNION ALL
(SELECT 'SapRouter', '') UNION ALL
(SELECT 'SapClient', '200') UNION ALL
(SELECT 'SapLanguage', 'EN') UNION ALL
(SELECT 'SapPoolSize', '5') UNION ALL
(SELECT 'SapPeakConnectionsLimit', '10') UNION ALL
(SELECT 'SapConnectionIdleTimeout', '600') UNION ALL
(SELECT 'SapFieldNames', 'VBELN,ERDAT,ERZET,ERNAM,NETWR,WAERK') UNION ALL
 
/* Use the WHERE clause defined in the temporary table.*/
(SELECT 'SapWhereClauseSelect', 'SELECT sap_select_string from #SapWhereClauseTable ORDER BY order_number') UNION ALL
(SELECT 'SapQueryTable', 'VBAK')
--#ImportSapQuery
 
/* Create an analysis.*/
(SELECT 'AnalysisType', '18') UNION ALL
(SELECT 'DataTableName', 'SapQueryTableExample') UNION ALL
(SELECT 'TargetTable', '#Result')
--#GetAnalysis


/* Show the results.*/
= --#ImportSqlQuery =
SELECT * FROM #Result
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:
--#ShowReport
</pre>


=<br/> --#ImportSqlQuery =
; TargetTable
Extracts data from an ADO.NET source (which in this case is the SQL Server database) and imports it to QPR ProcessAnalyzer Data Table or QPR ProcessAnalyzer temporary table. Column names are parsed from the query result. It is possible to both create new Data Tables as well as modify existing Data Tables with this command.
== 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:<br/>
; '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.
: 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' or 'ProjectName'
; ProjectId / ProjectName
: The id or the name of the project in which the target data table exists.
: The id or the name of the project in which the target data table exists.
; 'DataTableId' or 'DataTableName'
; DataTableId / DataTableName
: The id or the name of the existing/new target data table.
: The id or the name of the existing/new target data table.
; 'Append'
; 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.
: 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'
; 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:
: 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.
: 1 = don't stop execution of the script, continue running the script from the next statement.
Line 788: Line 461:
:<code>@_ExceptionMessage</code> If there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
:<code>@_ExceptionMessage</code> If there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
:<code>@_ExceptionDetails</code> If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.
:<code>@_ExceptionDetails</code> If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.
'''SQL Query Parameters'''
; SqlConnectionString
; 'SqlConnectionString'
: The SQL connection string that includes the settings needed to establish the initial connection. Mandatory. See [http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring%28v=vs.110%29.aspx SqlConnection.ConnectionString Property in Microsoft Development Network] for more information on the connection parameters.
: The SQL connection string that includes the settings needed to establish the initial connection. Mandatory. See [http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring%28v=vs.110%29.aspx SqlConnection.ConnectionString Property in Microsoft Development Network] for more information on the connection parameters.
; 'SqlQueryString'
; 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.
: 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'
; QueryExecutionTimeout
: 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. Not used with QPR ProcessAnalyzer Xpress or QPR ProcessAnalyzer Database as they always execute the command in the client 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.
: Defines timeout in seconds for the SQL command execution. If not specified, default value is 600 seconds.
; ExecuteInClientSide
: Defines whether the command is executed from the QPR ScriptLauncher or from the server. This parameter is used when there is no server connection available, for example. TRUE or 1, the query is executed in the client side. FALSE or 0, the 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.


== Examples ==
See examples at the [[ImportSqlQuery Script Examples]] page.
The following example will load data from an ADO.NET source (a sample database called DB1 on Microsoft SQL Server) and select all columns from the table EXAMPLE. It will then put that data into the "#TABLE" temporary table, and then show the contents of that table.
<pre>
(SELECT 'SqlConnectionString', 'Data Source=(local);Initial Catalog=DB1;Integrated Security=true') UNION ALL
(SELECT 'SqlQueryString', 'SELECT * FROM EXAMPLE') UNION ALL
(SELECT 'TargetTable', '#TABLE') UNION ALL
(SELECT 'Append', '1')
--#ImportSqlQuery
 
(SELECT * FROM #TABLE)
(SELECT 'MaximumCount', '0')
--#ShowReport
</pre>
 
The following example will load all the columns from "EXAMPLE" table in SQL Server database and will put that data into the "ExampleDataTable" in the "ExampleProject" project. It will then get the Data Table analysis from the "ExampleDataTable", put that into the "#TABLE" temporary table and then show the contents of that table.  
<pre>
(SELECT 'ProjectName', 'ExampleProject') UNION ALL
(SELECT 'DataTableName', 'ExampleDataTable') UNION ALL
(SELECT 'Append', '0') UNION ALL
(SELECT 'SqlConnectionString', 'Data Source=(local);Initial Catalog=DB1;Integrated Security=true') UNION ALL
(SELECT 'SqlQueryString', 'SELECT * FROM EXAMPLE')
--#ImportSqlQuery


(SELECT 'AnalysisType', '18') UNION ALL
= --#Run =
(SELECT 'ProjectName', 'ExampleProject') UNION ALL
Runs another script with specified parameters. This command can take multiple SELECT queries which are passed as parameters to the called script. The first SELECT configures the script call by defining the script id to be called.
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'DataTableName', 'ExampleDataTable') UNION ALL
(SELECT 'TargetTable', '#TABLE')
--#GetAnalysis


(SELECT * FROM #TABLE)
(SELECT 'MaximumCount', '0')
--#ShowReport
</pre>
=<br/> --#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.
== First Query ==
== 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:<br/>
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:<br/>
; 'ProjectId' or 'ProjectName'
; ScriptId
: The id or the name of the project in which the target model exists. Defaults to the current project.
: Mandatory. The Id of the called script.
; 'ModelId' or 'ModelName'
; CatchOperationExceptions
: 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:
: 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.
: 1 = don't stop execution of the script, continue running the script from the next statement.
Line 849: Line 489:
: <code>@_ExceptionDetails</code> If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.
: <code>@_ExceptionDetails</code> 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.
== Examples ==
The following example will remove all events in the model with Id "22931" in the project with Id "234".
<pre>
(SELECT 'ProjectId', '234') UNION ALL
(SELECT 'ModelId', '22931')
--#RemoveEvents
</pre>
The following example will remove 10 first events from the model by using Event Id's from --#GetAnalysis command.
<pre>
(SELECT 'AnalysisType', '6') UNION ALL
(SELECT 'MaximumCount', '10') UNION ALL
(SELECT 'ModelId', '<ModelId>') UNION ALL
(SELECT 'IncludeEventIds', 'True') UNION ALL
(SELECT 'TargetTable', '#Events')
--#GetAnalysis
(SELECT 'ModelId', '<ModelId>')
(SELECT [Event Id] from [#Events])
--#RemoveEvents
</pre>
== Script Log Results ==
When the script is run, entries similar to the following will be shown in the script log, if event Id's to be removed had been specified in the query:
<pre>
Remove events for model id=41 started
Number of events in model (before): 641
Number of events to be removed: 100
Number of events removed: 10
Number of events not found: 90
Number of events in model (after): 631
</pre>
In the log "Number of events to be removed" refers to the number of values (in this case 100) fetched from the first column of the query so that these values can be converted into numeric format.
In case there are no event Id's specified in the query, the script log will show the following entries:
<pre>
Remove events for model id=41 started
Number of events in model (before): 621
Remove all events
Number of events in model (after): 0
</pre>
=<br/> --#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:<br/>
; '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:
: <code>@_ExceptionOccurred</code> If there was an exception, then this value is 1, otherwise 0. INT
: <code>@_ExceptionType</code> If there was an exception, shows the C# class name for the exception, NVARCHAR(MAX), otherwise NULL.
: <code>@_ExceptionMessage</code> If there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
: <code>@_ExceptionDetails</code> If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.
== Following Queries ==
== 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.
Subsequent queries are optional and they are used for passing parameters to the called script. Maximum number of arguments is 10.  Each argument is created as a temporary table with names '''#_Arg1''', '''#_Arg2''', '''#_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.
The possible arguments are as follows:
The possible arguments are as follows:
:* @_Argv - type INT: the number of provided parameters (from 0 to 10)
* '''@_Argv''': Number of provided parameters (between 0 to 10) (type iNT)
:* #_Arg1, ... #_Arg10: arguments passed to that script
* '''#_Arg1''', '''#_Arg2''', ... '''#_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.
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.


== Examples ==
See examples at the [[Run Script Examples]] page.
In the following example, the script gets data from two data tables and passes that data to the script with Id equal to <ScriptId>.  
 
<pre>
(SELECT 'AnalysisType', '18') UNION ALL
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'DataTableId', '<DataTableId_1>') UNION ALL
(SELECT 'TargetTable', '#DataTable1')
--#GetAnalysis
 
(SELECT 'AnalysisType', '18') UNION ALL
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'DataTableId', '<DataTableId_2>') UNION ALL
(SELECT 'TargetTable', '#DataTable2')
--#GetAnalysis


(SELECT 'ScriptId', '<ScriptId>')
= --#RunQuery =
SELECT * FROM #DataTable1;
Runs an [[Web_API:_Expression/query|expression language query]], and stores results to a [[QPR_ProcessAnalyzer_Project_Workspace#Datatables|datatable]] or to a temporary table in the scripting database. Following parameters can be used in the command:
SELECT * FROM #DataTable2;
* '''Configuration''': Expression language query to run, written in JSON as specified in [[Web_API:_Expression/query|Web API: Expression/query]]. Queries can be created by using a [[QPR_ProcessAnalyzer_Chart|chart]] where to open the '''Query''' (in the '''Advanced''' tab). It will show the query made by chart that's compatible with what can be specified in the ''Configuration'' parameter.
--#Run
* '''TargetTable''': When specified, results are stored to a temporary table with that name in the scripting sandbox. The temporary table can be read using the subsequent commands. When the script ends, temporary tables are automatically removed.
</pre>
* '''DatatableId''': When specified, data is stored to the defined existing datatable. When using datatable id, ProjectName or ProjectId parameter don't need to be defined.
* '''DataTableName''': When specified, data is stored to the datatable with that name, located in the same project as the script. If you want to use different project, specify either the ProjectName or ProjectId parameter.
* '''ProjectName''': Specifies a project by name where the results datatable is stored. Used together with the DataTableName parameter.
* '''ProjectId''': Specifies a project by id where the results datatable is stored. Used together with the DataTableName parameter.


Then it runs that script with the following parameters: the number of arguments is 2 (that is, @_Argv=2). #_Arg1 takes data from the <DataTableId_1> data table and #_Arg2 from the <DataTableId_2> data table.
See [[RunQuery Script Examples]].
<pre>
print 'Number of arguments: ' + cast(@_Argv as varchar(100));
 
SELECT * from #_Arg1;
(SELECT 'MaximumCount', '0')
--#ShowReport
 
SELECT * from #_Arg2;
(SELECT 'MaximumCount', '0')
--#ShowReport
</pre>


= --#SendEmail =
= --#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.  
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.  


== 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:<br/>
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:<br/>
; 'CatchOperationExceptions'
; 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:
: 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.
: 1 = don't stop execution of the script, continue running the script from the next statement.
Line 968: Line 524:
:<code>@_ExceptionDetails</code> If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.
:<code>@_ExceptionDetails</code> 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'''
'''E-mail Parameters'''
; 'EmailFrom'
; EmailFrom
: Defines the from address for this e-mail message. Mandatory.
: Defines the from address for this e-mail message. Mandatory.
; 'EmailTo'
; EmailTo
: Defines the recipient(s) for this e-mail message given in a list separated by comma. Mandatory.
: Defines the recipient(s) for this e-mail message given in a list separated by comma. Mandatory.
; 'EmailSubject'
; EmailSubject
: Defines the subject of the email. Default value is empty. Optional.
: Defines the subject of the email. Default value is empty. Optional.
; 'EmailBody'
; EmailBody
: Defines the message body. Default value is empty. Optional.
: Defines the message body. Default value is empty. Optional.
; 'EmailCc'
; EmailCc
: Defines the carbon copy recipient(s) for this e-mail message given in a list separated by comma. Optional.
: Defines the carbon copy recipient(s) for this e-mail message given in a list separated by comma. Optional.
; 'EmailBcc'
; EmailBcc
: Defines the blind carbon copy recipient(s) for this e-mail message given in a list separated by comma. Optional.
: Defines the blind carbon copy recipient(s) for this e-mail message given in a list separated by comma. Optional.
; 'EmailIsBodyHtml'
; 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.
: 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'
; EmailSender
: Defines the sender's address for this e-mail message. Default value is empty. Optional.
: Defines the sender's address for this e-mail message. Default value is empty. Optional.
; 'EmailReplyTo'
; EmailReplyTo
: Defines the ReplyTo address(es) for the mail message given in a list separated by comma. Optional.
: Defines the ReplyTo address(es) for the mail message given in a list separated by comma. Optional.
; 'EmailPriority',
; EmailPriority
: Defines the priority of this e-mail message. Possible values are "High", "Normal", and "Low". Default value is "Normal". Optional.
: Defines the priority of this e-mail message. Possible values are "High", "Normal", and "Low". Default value is "Normal". Optional.
; 'EmailDeliveryNotification'
; EmailDeliveryNotification
: Defines the delivery notifications for this e-mail message. Possible values are "Delay", "Never", "None", "OnFailure", and "OnSuccess". Default value is "None". Optional.
: Defines the delivery notifications for this e-mail message. Possible values are "Delay", "Never", "None", "OnFailure", and "OnSuccess". Default value is "None". Optional.
; 'EmailBodyEncoding'
; 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.
: 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. UTF8 is used by default. Optional.
; 'EmailSubjectEncoding'
; 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.
: 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. UTF8 is used by default. 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'''
'''SMTP Server Parameters'''
; 'SmtpServer'
; SmtpServer
: Defines the hostname or the IP address of the server. Mandatory for the first occurrence of the SendEmail command during script execution.
: Defines the hostname or the IP address of the server. Mandatory for the first occurrence of the SendEmail command during script execution.
; 'SmtpPort'
; SmtpPort
: Defines the port of the SMTP server. Default value is "25". Optional.
: Defines the port of the SMTP server. Default value is "25". Optional.
; 'SmtpAuthenticationUsername'
; 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.
: 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'
; 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.
: 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'
; 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.
: 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.


== Example ==
See examples at the [[SendEmail Script Examples]] page.
The following example will send an e-mail message to multiple recipients.
<pre>
(SELECT 'EmailFrom', 'example.from@address.com') UNION ALL
(SELECT 'EmailTo', 'recipient.one@address.com,recipient.two@address.com,recipient.three@address.com') UNION ALL
(SELECT 'EmailSubject', 'Example E-mail') UNION ALL
(SELECT 'EmailBody', 'QPR ProcessAnalyzer example script started running.') UNION ALL
(SELECT 'SmtpServer', 'localhost')
--#SendEmail
</pre>
 
See also [[How to Define the SMTP Server Connection in an On-Site Deployment]].
 
=<br/> --#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.
= --#ShowReport =
Outputs result of an SQL query to a CSV file when running script from [[QPR_ProcessAnalyzer_ScriptLauncher|QPR ProcessAnalyzer ScriptLauncher]]. This command takes two SELECT queries as parameters.
== First Query ==
== First Query ==
A user specified SQL query to be shown in the configured Excel sheet.  
SQL query which results are shown.  
; <nowiki>'<data>'</nowiki>
; <nowiki><data></nowiki>
: Mandatory. The database query whose results are to be returned.
: Mandatory. The database query whose results are to be returned.


== Second Query ==
== 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:<br/>
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:<br/>
; '<Analysis Parameter>'
; <Analysis Parameter>
: Optional. The [[Analysis Parameters]] given for the operation. Some suggested parameters to be used:
: Optional. The analysis parameters given for the operation. Some suggested parameters to be used:
:; 'Title'
:; Title
:: The title of the created report. If not given, "Report" will be used as a default.
:: The name of the created CSV file.
:; 'SheetName'
:; MaximumCount
:: The name of the Excel sheet to be created.
:; 'MaximumCount'
:: The maximum number of rows to show (0 = all, default = 1000).
:: The maximum number of rows to show (0 = all, default = 1000).
; '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:
 
; 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.
: 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.
: 0 = stop execution of the current script and show the exception.
Line 1,049: Line 592:
:<code>@_ExceptionDetails</code> If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.
:<code>@_ExceptionDetails</code> If there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.


== Example ==
See examples at the [[ShowReport Script Examples]] page.
The following example opens the data table identified by data table name "SqlTable" and project name "Test" as a report.
 
<pre>
= --#StartBackground =
(SELECT 'AnalysisType', '18') UNION ALL
Continues the script run in background, i.e. the parent script execution completes and the rest of the script execution continues. When running a script in the background, it cannot output any results using the ShowReport command or GetAnalysis with the Show parameter. It's possible to terminate scripts that run in the background via the [[QPR_ProcessAnalyzer_Logs#Task_Log|Task log]]. No also that a script running in the background cannot execute in the client side mode.
(SELECT 'ProjectName', 'Test') UNION ALL
 
(SELECT 'DataTableName', 'SqlTable') UNION ALL
Takes one SELECT query as a parameter. Following parameter is supported:
(SELECT 'TargetTable', '#AnalysisResult')
 
--#GetAnalysis
; 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.


SELECT * FROM #AnalysisResult;
See examples at the [[StartBackground Script Examples]] page.
(SELECT 'Title', 'Report1') UNION ALL
(SELECT 'SheetName', 'Sheet1') UNION ALL
(SELECT 'MaximumCount', '0')
--#ShowReport
</pre>


=<br/> --#WriteLog =
= --#WriteLog =
Adds the first column values from the preceding SQL statements to the log that is shown after the whole script execution is completed.
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 [https://docs.microsoft.com/en-us/sql/t-sql/language-elements/print-transact-sql?view=sql-server-ver15 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.


In addition to the WriteLog command, you can also use the [http://msdn.microsoft.com/en-us/library/ms176047.aspx 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.


== Examples ==
__NOTOC__
The following example will write "Script started", "Example", "Print Example" into the log. Note that the WriteLog command and Print SQL statement represent two different ways of generating log entries, and you can use them also separately.
[[Category: QPR ProcessAnalyzer]]
<pre>
SELECT 'Script started'
SELECT 'Example'
--#WriteLog
PRINT 'Print Example'
</pre>

Latest revision as of 14:20, 24 January 2024

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.

--#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 made from the QPR ScriptLauncher or from the server. TRUE or 1, the call is executed in the ScriptLauncher. FALSE or 0, the call is executed in the server. 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. For example, Content-Type and Accept HTTP headers can be added. 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.

--#Commit

Commits the currently open SQL transaction in the sandbox database and starts a new transaction. The commit command can be executed at any point in the script. Note that the command does not have any parameters, i.e. there is no preceding SELECT statement before the --#Commit statement.

If the commit command is not used, the database transaction in the sandbox database is committed when the script is completed. On the other hand, if the script execution encounters an error, the SQL transaction is rolled back.

The commit command is useful in following circumstances:

  • If the sandbox database is configured to allow storing permanent objects, commit can be used to preserve changes even if the script execution encounters an error.
  • When the scripting is handling large amount of data, it's better to make commits during the script run, so that the database transaction log doesn't grow too large.
  • Committing changes makes them visible for other users in the database.

Example:

--#Commit

--#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

--#GetAnalysis command is deprecated and it will be removed in a future release. Use the more flexible --#RunQuery command instead.

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>
The --#GetAnalysis command supports the following analysis types:
  • DataTableAnalysis=18: Reads a data table from SQL server and stores it in temporary table​
  • Etl=19
  • EtlReport=20
  • RunScript=25
  • ExpressionAnalysis=33
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.
Title
Optional. Name of the CSV file created when Show is TRUE or 1. Default value is the name of the analysis type.
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.
MaximumCount
Used with Operation Log Analysis analysis type. Integer. The maximum amount of rows returned. Optional. Default value is 1000.

See examples at the GetAnalysis Script Examples page.

--#ImportDataTable

Imports data from an SQL query to a datatable. 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 contents of the target datatable. When value is 1, existing rows in the target datatable are not deleted (also new columns in the imported data are created to the datatable). When value is 0, existing rows in the target datatable are deleted before the import (columns are still preserved). 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 datatable is not created.

See examples at the ImportDataTable 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
Secret name 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.
QueryExecutionTimeout
Defines timeout in seconds for the ODBC command execution. If not specified, default value is 600 seconds.
ExecuteInClientSide
Defines whether the command is executed from the QPR ScriptLauncher or from the server. This parameter is used when there is no server connection available, for example. TRUE or 1, the query is executed in the QPR ScriptLauncher. FALSE or 0, the query is executed in the server. 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 1, 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.
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.
QueryExecutionTimeout
Defines timeout in seconds for the OLE DB command execution. If not specified, default value is 600 seconds.
ExecuteInClientSide
Defines whether the command is executed from the QPR ScriptLauncher or from the server. This parameter is used when there is no server connection available, for example. TRUE or 1, the query is executed in the QPR ScriptLauncher. FALSE or 0, the query is executed in the server. 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 using its REST API and imports the data to a datatable. The command takes one SELECT query as its parameter. If the query doesn't return any data, the target data table or temporary table is not created.

More information about the Salesforce REST API: https://developer.salesforce.com/docs/atlas.en-us.api_rest.meta/api_rest/intro_rest.htm.

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
Temporary table to which the data is imported. If not used, define the target using the ProjectId/ProjectName, DataTableId/DataTableName, and Append parameters described below.
ProjectId / ProjectName
Id or the name of the project in which the target datatable is located.
DataTableId / DataTableName
Id or the name of the target data table. If DataTableName is used, the ProjectId or ProjectName can also be used to define the project where the datatable is located.
Append
Defines what to do with an existing target data table contents. TRUE or 1, existing contents of the target datatable is not deleted in the import. When FALSE or 0, existing contents of the target datatable are deleted before importing new data. 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.
SalesforceUser
Username for the Salesforce cloud.
SalesforcePW
Password for the Salesforce cloud.
SalesforcePWKey
Secret name for the stored Salesforce password. Alternative for the SalesforcePW property.
SalesforceUrl
Optional. Salesforce web service url.
SalesforceQueryMode
Optional. Determines which Salesforce query function to use. One of the following values (1, 2 or 3) can be used:
1: QueryAll (default): Executes specified SOQL query, except unlike Query, QueryAll returns records that are deleted because of a merge or delete. More information: https://developer.salesforce.com/docs/atlas.en-us.api_rest.meta/api_rest/resources_queryall.htm.
2: Query: Executes the specified SOQL query. More information: https://developer.salesforce.com/docs/atlas.en-us.api_rest.meta/api_rest/resources_query.htm)
3: sObject Describe: Completely describes the individual metadata at all levels for the specified object. For example, this can be used to retrieve the fields, URLs, and child relationships for the Account object. More information: https://developer.salesforce.com/docs/atlas.en-us.api_rest.meta/api_rest/resources_sobject_describe.htm).
SalesforceQuery
Query to run in the Salesforce cloud to fetch the data, defined as SOQL (Salesforce Object Query Language). More information: https://developer.salesforce.com/docs/atlas.en-us.236.0.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_sosl_intro.htm.
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. Data is queried from Salesforce in batches, and this setting determines the batch size. The value can be between 200 and 2000, and the default value is 500.

Notes

If you get error 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 license 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 installing SAP NetWeaver RFC Library.

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.
ConvertDataTypes
List of SAP data types that are converted into respective data types supported by SQL Server instead of using NVARCHAR. Defined by listing the data type identifier characters in any order. Available data type identifying characters are IFPCDTNX. If not defined, all data is converted to NVARCHAR. Example: IFP (convert only numeric data types: Integer, Float, Packed number) (more information).
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
Secret name 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 from the QPR ScriptLauncher or from the server. This parameter is used when there is no server connection available, for example. TRUE or 1, the query is executed in the QPR ScriptLauncher. FALSE or 0, the query is executed in the server. 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.
SapMaxPoolSize
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
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.
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.
UseAnyAsColumnType
Determines datatable column data types for the created columns. When true, "Any" type of columns are created (resulting into SQL_variant columns in SQL server), and when false, data types depend on the ConvertDataTypes parameter. Default value is true when running the import in SQL script, and otherwise default value is false.
AliasUser
AppServerService
CharacterFaultIndicatorToken
Codepage
GatewayHost
GatewayService
IdleCheckTime
LogonCheck
MaxPoolWaitTime
MessageServerService
Name
NoCompression
OnCharacterConversionError
PartnerCharSize
PasswordChangeEnforced
ProgramId
R3Name
RegistrationCount
RepositoryDestination
RepositoryPassword
RepositorySncMyName
RepositoryUser
RepositoryX509Certificate
SapSso2Ticket
SncLibraryPath
Full path including file name of the SNC shared library to be used.
SncMode
Determines whether connections will be secured with SNC. Value 0 doesn't use SNC (default) and value 1 uses SNC.
SncMyName
Token/identifier representing the external RFC program. In most cases this can be omitted. The installed SNC solution usually knows its own SNC name. Only for solutions supporting “multiple identities”, you may Varies depending on the installed SNC solution (Secude, Kerberos, NTLM, etc). Example for Secude: p/secude:CN=ALEREMOT SAP Online Help 09.09.2014 SAP .NET Connector 3.0 41 need to specify the identity to be used for this particular destination/server. E, O=Mustermann-AG, C=DE
SncPartnerName
The backend's SNCname.
SncPartnerNames
SncQop
Quality of service to be used for SNC communication of this particular destination/server. One of the following values:
  • 1: Digital signature
  • 2: Digital signature and encryption
  • 3: Digital signature, encryption, and user authentication
  • 8: Default value defined by back-end system
  • 9: Maximum value that the current security product supports
SystemIds
UseSapGui
X509Certificate

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.
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.
QueryExecutionTimeout
Defines timeout in seconds for the SQL command execution. If not specified, default value is 600 seconds.
ExecuteInClientSide
Defines whether the command is executed from the QPR ScriptLauncher or from the server. This parameter is used when there is no server connection available, for example. TRUE or 1, the query is executed in the client side. FALSE or 0, the 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.

--#Run

Runs another script with specified parameters. This command can take multiple SELECT queries which are passed as parameters to the called script. The first SELECT configures the script call by defining the script id to be called.

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

Subsequent queries are optional and they are used for passing parameters to the called script. Maximum number of arguments is 10. Each argument is created as a temporary table with names #_Arg1, #_Arg2, #_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. The possible arguments are as follows:

  • @_Argv: Number of provided parameters (between 0 to 10) (type iNT)
  • #_Arg1, #_Arg2, ... #_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.

--#RunQuery

Runs an expression language query, and stores results to a datatable or to a temporary table in the scripting database. Following parameters can be used in the command:

  • Configuration: Expression language query to run, written in JSON as specified in Web API: Expression/query. Queries can be created by using a chart where to open the Query (in the Advanced tab). It will show the query made by chart that's compatible with what can be specified in the Configuration parameter.
  • TargetTable: When specified, results are stored to a temporary table with that name in the scripting sandbox. The temporary table can be read using the subsequent commands. When the script ends, temporary tables are automatically removed.
  • DatatableId: When specified, data is stored to the defined existing datatable. When using datatable id, ProjectName or ProjectId parameter don't need to be defined.
  • DataTableName: When specified, data is stored to the datatable with that name, located in the same project as the script. If you want to use different project, specify either the ProjectName or ProjectId parameter.
  • ProjectName: Specifies a project by name where the results datatable is stored. Used together with the DataTableName parameter.
  • ProjectId: Specifies a project by id where the results datatable is stored. Used together with the DataTableName parameter.

See RunQuery Script Examples.

--#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. UTF8 is used by default. 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. UTF8 is used by default. 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.

--#ShowReport

Outputs result of an SQL query to a CSV file when running script from QPR ProcessAnalyzer ScriptLauncher. This command takes two SELECT queries as parameters.

First Query

SQL query which results are shown.

<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 analysis parameters given for the operation. Some suggested parameters to be used:
Title
The name of the created CSV file.
MaximumCount
The maximum number of rows to show (0 = all, default = 1000).
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

Continues the script run in background, i.e. the parent script execution completes and the rest of the script execution continues. When running a script in the background, it cannot output any results using the ShowReport command or GetAnalysis with the Show parameter. It's possible to terminate scripts that run in the background via the Task log. No also that a script running in the background cannot execute in the client side mode.

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.