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.
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.
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.
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.
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.
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:
Navigate to Datasets: Click on "Datasets" in the left navigation panel to access the Datasets page.
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.
Dataset Information: Complete the dataset information form, including details such as the dataset name, dataset type (staging or production), the source for dataset parameters.
Finally, write the specific database query to retrieve data from your connected database. For example, to connect the complete database within your dataset, write:
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:
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:
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:
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:
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:
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. | |
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
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:This query adds a new record to the table with specified details.
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:
This query fetches the record details from the rule's output data.
Updating Existing Records
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:This query updates specific details of a record identified by a condition.
Using Output Data:
To dynamically update records using data from the rule's output, employ tokenized queries with token attributes:
This query modifies the record details using data from the rule's output.
Deleting Records
Static Query:
Use the
DELETE FROM
statement to remove records based on specific conditions:This command deletes a record from the table where the condition matches.
Using Output Data:
To delete records dynamically based on the rule's output data:
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:
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:
Within the workflow canvas, you would typically click the '+' or 'Add Node' button to reveal the list of nodes.
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:
While used as a Workflow Node, all types of
SELECT
andINSERT or UPDATE
queries are supported.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.
Last updated