MS SQL Server

MS SQL Server is a widely recognized commercial relational database management system (RDBMS) known for its advanced data management, security features, and comprehensive data analysis capabilities. This technical documentation provides a comprehensive guide to integrating MS SQL Server with Nected. You will learn how to configure the MS SQL Server integration, query the database via datasets, trigger MS SQL Server operations as rule actions, and troubleshoot common issues. By the end of this guide, you will know how to seamlessly connect MS SQL Server with Nected and leverage its capabilities for your applications.

Setting Up MS SQL Server Integration

To establish a connection between Nected and MS SQL Server for seamless data interaction, you need to configure an MS SQL Server integration. This section outlines the step-by-step process for setting up the integration effectively.

1. Adding Integration: Select "MS SQL Server" from the available integration options. This choice initiates the process of setting up an integration for MS SQL Server.

2. Configuring Connection Settings

   The configuration of connection settings is a critical step in establishing the connection between Nected and your MS SQL Server database. The following parameters need to be defined:

   • Environment Type: Choose whether you are configuring the integration for a "Staging" or "Production" environment. The selection of the environment type determines the context in which the integration will operate. By default, the environment type is set to “Staging”.

   • Integration Name: Assign a unique and meaningful name for the integration. It is essential that this name is distinct throughout the Nected platform and does not contain any spaces.

   • Host (Database IP Address): Specify the IP address or hostname of the MS SQL Server database to which you want to connect. In this case, use 13.235.33.154.

   • Port Number: Enter the port number on which Nected should communicate with the MS SQL Server database. The default port for MS SQL Server is 1433.

   • Username: Provide the username of the authorized user who has the necessary privileges to connect to the database and perform read and write operations. Use sa.

   • Password (optional, encrypted): You may enter the password for the authorized user. Passwords are encrypted to ensure maximum security for your database. For this integration, use JE5VR8N#&#gcY4.

   • Database Name: Specify the name of the MS SQL Server database from which Nected will access and manipulate data. Enter master.

   • IP Addresses for Allow-list: To enhance security, it's recommended to configure an allow-list of IP addresses that can access your MS SQL Server database. Ensure that Nected service IP addresses are included in this allow-list to secure the connection.

3. Testing the Connection

   After configuring the connection settings, it's crucial to test the connection to ensure that Nected can successfully communicate with the MS SQL Server database. This test verifies that the provided information is accurate and the connection is established without issues.

4. Publishing in Staging

   If the connection test is successful, you can proceed to publish the integration in the "Staging" environment. In this context, "Staging" refers to a development or testing environment. For a "Production" database, the option will be "Publish in Production." Publishing the integration in a specific environment enables you to set up rules and operations within that environment.

5. Integration Status

   After configuring and publishing the integration, it's essential to check the integration status. For a "Staging" environment, the status will be "staging," and for a "Production" environment, it will be "Production." Monitoring the status ensures that the integration is active and properly integrated into the selected environment.

Note: If using Nected cloud, Nected IP address (43.205.43.45) must be added to the allow-list on your database server to secure your connections. Not required in on-premise setup

You need to publish staging and production connectors in their respective environments. If a connector is published only in staging, then it cannot be used in the production environment, and any call to rule in production env will fail giving the "connector not published in production" error.

Querying MS SQL Server Database via DataSet

Nected provides a powerful feature that allows you to query and interact with your MS SQL Server database through datasets. This section will guide you through the process of adding a new dataset, writing SQL code for querying the MS SQL Server database and performing various operations, including creating rows and executing join operations.

Adding a New DataSet:

Before you can query the MS SQL Server database, you need to add a new dataset to your Nected environment. Follow these steps:

1. Navigate to Datasets: Click on "Datasets" in the left navigation panel to access the Datasets page.

2. Create a Dataset: Click the "+ Create Dataset" button on the Datasets page. Choose the database integration associated with the dataset you wish to create. Note that you can create multiple datasets for one integration.

3. Dataset Information: Complete the dataset information form, including details such as the dataset name, dataset type (staging or production), the source for dataset parameters.

4. Finally, write the specific database query to retrieve data from your connected database. For example, to connect the complete database within your dataset, write:

SELECT * FROM table_name; -- Change the table_name with your database table_name

Performing Operations:

In addition to basic querying, you can perform various operations on the data retrieved from the MS SQL Server database. Here are some common tasks you can carry out:

1. Aggregations:

You can use aggregations to summarize and analyze your data. For instance, to calculate the total deposit amount for a specific day:

SELECT
    transaction_date,
    SUM(amount) AS total_deposit
FROM
    financial_transactions
WHERE
    transaction_type = 'Deposit'
GROUP BY
    transaction_date;

This query aggregates the total deposit amount for each distinct transaction date.

2. Filtering:

Filtering is essential for retrieving specific subsets of data. To find all transactions above a certain amount, you can use:

SELECT
    transaction_date,
    amount,
    description
FROM
    financial_transactions
WHERE
    amount > 1500;

This query filters and selects transactions with amounts greater than $1500.

3. Sorting:

Sorting data can be useful for visualizing trends or identifying the most significant transactions. To sort transactions by amount in descending order:

SELECT
    transaction_date,
    amount,
    description
FROM
    financial_transactions
ORDER BY
    amount DESC;

This query returns transactions sorted by the amount in descending order.

4. Grouping and Aggregating:

Combining grouping and aggregation helps summarize data based on certain criteria. To find the total transaction amount by transaction type:

SELECT
    transaction_type,
    SUM(amount) AS total_amount
FROM
    financial_transactions
GROUP BY
    transaction_type;

This query groups transactions by type and calculates the total amount for each type.

5. Joins:

If your database has multiple related tables, joins can be used to combine data from different sources. For instance, to link transactions with account details:

SELECT
    t.transaction_id,
    t.transaction_date,
    t.amount,
    a.account_name
FROM
    financial_transactions t
JOIN
    accounts a
ON
    t.account_id = a.account_id;

This query joins the financial_transactions table with an accounts table to retrieve transactions along with their corresponding account names.

Tip: Always ensure that your SQL code is well-tested in the "Staging" environment before deploying it to the "Production" environment to prevent potential data integrity issues and unexpected consequences.

Supported Queries for Creating a Dataset

When working with MS SQL Server databases in Nected, a variety of SQL queries can be used to interact with and manipulate data. The table below lists supported queries, providing descriptions and examples for each type:

Query Type	Description	Example
Retrieve All	Fetches all records from a specified table.	SELECT * FROM dbo.MSreplication_options;
Retrieve Specific	Selects specific columns from a table.	SELECT optname, revision, value FROM dbo.MSreplication_options;
Filtering Records	Filters records based on specific conditions.	SELECT * FROM dbo.MSreplication_options WHERE value = 'true';
Aggregate Data	Performs aggregation such as SUM and AVG on data.	SELECT optname, SUM(major_version) AS total_majors FROM dbo.MSreplication_options GROUP BY optname;
Top N Records	Retrieves a limited number of records.	SELECT TOP 5 * FROM dbo.MSreplication_options;
Subqueries	Uses subqueries to perform operations on sorted subsets of data.	SELECT * FROM (SELECT Top 10 name, number FROM dbo.spt_values ORDER BY number DESC) AS subquery;
Advanced Grouping	Groups data by a column and filters using HAVING clause.	SELECT type, COUNT() AS total_count FROM dbo.spt_values WHERE status = 0 GROUP BY type HAVING COUNT() > 1;
String Operations	Concatenates columns into a single string output.	SELECT CONCAT(name, ' ', type) AS concat_string FROM dbo.spt_values;
Advanced Joins	Performs inner joins based on conditions between tables.	SELECT t1.name, t1.number, t2.name, t2.number FROM dbo.spt_values t1 INNER JOIN dbo.spt_values t2 ON t1.number > t2.number;
Full-Text Search	Utilizes full-text search capabilities to find matches in text.	SELECT * FROM dbo.spt_values WHERE CONTAINS(name, 'single user');
Window Functions	Queries involving window functions like ROW_NUMBER, RANK, etc.	SELECT name, ROW_NUMBER() OVER (ORDER BY name) AS rownum FROM dbo.MSreplication_options;

Non-Supported Queries for Creating a Dataset

Certain types of SQL queries are not supported when creating datasets within Nected due to various limitations related to data security and integrity. The table below outlines these non-supported queries along with descriptions and examples to clarify what cannot be executed:

Query Type	Description	Example
Delete Queries	Removing records from a table is not supported.	DELETE FROM products WHERE price < 10;
Truncate Table	Truncating a table to remove all records is not supported.	TRUNCATE TABLE orders;
Create, Alter, Drop Queries	Queries for creating, altering, or dropping tables and database schema are not supported.	CREATE TABLE new_table (column1 INT, column2 VARCHAR);
Indexing Queries	Creating or managing indexes on tables is not supported.	CREATE INDEX idx_customer_name ON customers (customer_name);
Stored Procedures	Creating stored procedures and functions within the database is not supported.	CREATE PROCEDURE calculate_total_sales() BEGIN SELECT SUM(sale_amount) FROM sales; END;
Grant and Revoke Permissions	Managing user privileges and permissions on database objects is not supported.	GRANT SELECT ON products TO user1;

These limitations ensure the security and integrity of your data within Nected by preventing operations that could lead to large-scale modifications or vulnerabilities. It is important to consider these restrictions when planning how to integrate and manage your database content within the platform.

Trigger MS SQL Server Operations as Rule Actions

In Nected, rule actions provide a way to automate MS SQL Server operations when specific conditions are met. While datasets are typically used for read-only queries, rule actions allow you to modify the dataset and trigger specific operations within your MS SQL Server database. These operations can be further customized using tokenized queries with token attributes. This section explores SQL queries that can be used as rule actions to write or edit data within your database, considering both static and tokenized queries.

Inserting New Records

1. Static Query: To insert new records into a table as a rule action, you can use the INSERT INTO statement with static values. For example, to log a new entry:

   INSERT INTO table_name (column1, column2, column3)
   VALUES ('value1', 'value2', 'value3');
   

   This query adds a new record to the table with specified details.

2. Using Output Data:

   To make the query dynamic and utilize data from the rule's output, you can use tokenized queries with token attributes. For example, to insert a new record using attributes from the rule's output:

   INSERT INTO table_name (column1, column2, column3)
   VALUES ('{{.outputData.column1}}', '{{.outputData.column2}}', '{{.outputData.column3}}');
   

   This query fetches the record details from the rule's output data.

Updating Existing Records

1. Static Query:

   You can use the UPDATE statement with static values as a rule action to modify existing records. For instance, to change a record’s details:

   UPDATE table_name
   SET column1 = 'new_value'
   WHERE column_id = 123;
   

   This query updates specific details of a record identified by a condition.

2. Using Output Data:

   To dynamically update records using data from the rule's output, employ tokenized queries with token attributes:

   UPDATE table_name
   SET column1 = '{{.outputData.new_value}}'
   WHERE column_id = '{{.outputData.id}}';
   

   This query modifies the record details using data from the rule's output.

Deleting Records

1. Static Query:

   Use the DELETE FROM statement to remove records based on specific conditions:

   DELETE FROM table_name
   WHERE column_id = 123;
   

   This command deletes a record from the table where the condition matches.

2. Using Output Data:

   To delete records dynamically based on the rule's output data:

   DELETE FROM table_name
   WHERE column_id = '{{.outputData.id}}';
   

   This command deletes records using conditions that match data derived from the rule's output.

Utilizing Custom Input and Dataset Data

You can also customize rule actions by using data directly from custom inputs or datasets, enabling a wide range of dynamic operations tailored to specific needs. For example, updating a table based on dataset attributes:

UPDATE table_name
SET column1 = '{{.dataSet.new_value}}'
WHERE column_id = '{{.dataSet.id}}';

These SQL operations and queries illustrate how you can use rule actions with both static and tokenized queries to perform data modification operations within the database in response to specific triggers or conditions. Rule actions offer flexibility in automating data updates, ensuring data accuracy, and enhancing the functionality of your application or database system.

Supported Queries for Writing an Action

When automating MS SQL Server operations through rule actions in Nected, certain types of SQL queries are supported to ensure efficient data manipulation based on specific conditions or triggers. The following table categorizes these queries by type, with descriptions and examples to aid in understanding their practical applications:

Query Type	Description	Example
Insert	Adds new records to a table.	INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2');
Update	Modifies existing records in a table.	UPDATE table_name SET column1 = 'new_value' WHERE condition = 'value';
Merge	Combines insert, update, and delete operations based on conditions.	MERGE INTO table_name USING source_table ON table_name.id = source_table.id WHEN MATCHED THEN UPDATE SET table_name.value = source_table.value WHEN NOT MATCHED THEN INSERT (id, value) VALUES (source_table.id, source_table.value);
Transactional Control	Executes a series of operations in a transaction, ensuring all or none of the operations are committed.	BEGIN TRANSACTION; UPDATE table1 SET column1 = 'value' WHERE column_id = 1; UPDATE table2 SET column2 = 'value' WHERE column_id = 2; COMMIT;
Dynamic Insert	Inserts data using variables from rule output.	INSERT INTO table_name (column1, column2) VALUES ('{{.outputData.column1}}', '{{.outputData.column2}}');
Dynamic Update	Updates records using variables from rule output.	UPDATE table_name SET column1 = '{{.outputData.new_value}}' WHERE column_id = '{{.outputData.id}}';
Custom Input Insert	Uses custom input for inserting data.	INSERT INTO table_name (column1, column2) VALUES ('{{.customInput.value1}}', '{{.customInput.value2}}');
Custom Input Update	Updates data based on custom input.	UPDATE table_name SET column1 = '{{.customInput.new_value}}' WHERE column_id = '{{.customInput.id}}';

These supported queries allow for a wide range of data operations that can be automated within Nected, enabling dynamic interactions with MS SQL Server databases. By utilizing these queries in rule actions, users can efficiently manage database operations in response to various internal or external triggers, ensuring data integrity and responsiveness.

Non-Supported Queries for Writing an Action

Certain types of SQL queries are not supported for rule actions in Nected when interacting with MS SQL Server. These restrictions are in place to maintain data integrity and system stability. The table below provides an overview of these non-supported queries, along with descriptions and examples:

Query Type	Description	Example
Delete Queries	Deleting records from a table is not supported.	DELETE FROM products WHERE price < 10;
Truncate Table	Truncating a table to remove all records is not supported.	TRUNCATE TABLE orders;
Create, Alter, Drop Queries	Queries for creating, altering, or dropping tables and database schema are not supported.	CREATE TABLE new_table (column1 INT, column2 VARCHAR);
Indexing Queries	Creating or managing indexes on tables is not supported.	CREATE INDEX idx_customer_name ON customers (customer_name);
Stored Procedures	Creating or invoking stored procedures and functions within the database is not supported for rule actions.	EXEC calculate_total_sales;
Grant and Revoke Permissions	Managing user privileges and permissions on database objects is not supported.	GRANT SELECT ON products TO user1;

These limitations are important to understand as they affect how users can manipulate data within Nected when connected to MS SQL Server databases. By adhering to these guidelines, users ensure they are using the platform within its operational boundaries, promoting system security and stability.

Add MS SQL Server as Workflow Node

Adding nodes in the workflow editor of Nected is essential because they represent the individual tasks, decisions, or actions that comprise the overall automated process. By adding and configuring nodes, users can tailor workflows to fit their specific business processes, data handling requirements, and decision logic, ensuring that tasks are executed efficiently and consistently.

To add MS SQL Server as a workflow node, follow the following steps:

1. Within the workflow canvas, you would typically click the '+' or 'Add Node' button to reveal the list of nodes.

2. Choose the MS SQL server from the node option.

These are the most basic steps, for a more detailed overview of how to configure it, read the DB Node page.

Supported & Non-supported Queries as Workflow Node:

1. While used as a Workflow Node, all types of SELECT and INSERT or UPDATE queries are supported.

2. The list of Non-Supported query list is just the same as 'Non-Supported Queries for Writing an Action'.

Next Steps

After you successfully publish the database connector, you can use it via Dataset to unify data & attach it as input to the Rules as well as to the Workflows to invoke any database operations as part of Rule Actions or Workflow Nodes.