MSSQL

MSSQL description

You can use the collector to retrieve data or to make changes to the MSSQL database. Various authorization methods are supported, including standard username and password, NTLM, and Azure.

Array of values

The MSSQL collector returns an array of rows from the SQL query. Each element in the array is an object that describes a row from the table. For example, for a table

The following array will be created:

[{
    Num: 1
    Name: "Alexander"
    Age: 44
}, {
    Num: 2
    Name: Marina
    Age: 33
}, {
    Num: 3
    Name: Bullfinch
    Age: 1
}]

When receiving an array of values, ALEPIZ does the following:

• The array of values is converted to JSON text and saved to the history database
• If the counter has dependent counters, then ALEPIZ will call them as many times as there were values in the array and each time pass them as a variable %:PARENT_VALUE:% a new JSON row. You can work with the resulting value using the getValueFromJSONStr() function For example, the first time, for our example, the getValueFromJSONStr(%: PARENT_VALUE:%, "Name") function will return "Alexander", the second time - "Marina", the third time - "Bullfinch"

Parameters

• MSSQL driver (run ODBC Data Source (64-bit), tab "Drivers") - driver for access to MSSQL. To see the list of installed drivers, you need to launch the "ODBC Data Source (64-bit)" application (for example, via Ctrl+Q and type "ODBC"), go to the Drivers tab and select one of the MSSQL drivers that are presented there.
• MSSQL server - Host name, IP address, instance name (myServerName\theInstanceName) or network path (.\ SQLExpress) to the MSSQL server. More details can be found in the documentation for the used MSSQL driver
• Port to connect - TCP port of MSSQL server
• Use Windows integrated (trusted) authentication - Enable this option to connect using Integrated Windows Authentication, or provide a user and password to connect using SQL Server Authentication.
• User name (for SQL Server authentication) - username for authorization if using SQL Server Authentication
• Password (SQL Server authentication) - password for authorization if using SQL Server Authentication To ensure security, it is better to use authorization methods that do not require a password.
• Database to connect - The name of the database to which the query will be made
• MSSQL Query - SQL query. If there is no query, a connection to the MSSQL server is checked. In case of successful connection, 1 is returned, in case of unsuccessful returned 0. If a SQL query is specified, but the connection to the server failed, JSON is returned {"unableToConnect": "<error message>"}
• Connection timeout (sec) - connection timeout to MSSQL, default 2 seconds
• Query timeout (sec) - SQL query execution timeout, default 2 sec