Microsoft SQL Server on-premises

Microsoft SQL Server on-premises is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications - which may run either on the same computer or on another computer across a network. In the context of Nintex Automation Cloud, Microsoft SQL Server on-premises is a connector A software component that allows seamless integration with third-party services, business applications, and content stores. Examples include Salesforce, Box, and Microsoft SharePoint. Connectors are used to create connections required for workflow actions and start events. . Use the Microsoft SQL Server on-premises actions to execute SQL stored procedures and to create, delete and update SQL records.

For more information on Microsoft SQL Server on-premises, see Microsoft SQL Server on-premises.

Important:

The maximum pool size limits for SQL connections to a specific database in SQL Server:

The maximum pool size for Microsoft SQL Server on-premises actions is 100.
The maximum pool Size for Microsoft SQL Server on-premises data lookup is 150.
The total maximum pool size for Microsoft SQL Server on-premises actions and data lookup is 250.

Jump to:

Create a Microsoft SQL Server on-premises connection
Create a Microsoft SQL Server on-premises data lookup
Considerations
Troubleshooting

Create a Microsoft SQL Server on-premises connection

Create a connection from the connections page or from the action configuration panel when configuring the action. For information about creating connections and assigning permissions, see Manage connections.

Microsoft SQL Server on-premises account requirements

The account you use to create a connection for Microsoft SQL Server on-premises is the account that has access to the Microsoft SQL Server on-premises actions.
The MicrosoftSQL Server Domain User used to create the connection needs db_datareader rights in SQL. To use the Create, Update, and run Stored Procedures actions they also need db_datawriter rights in SQL.
Use Integrated Authentication. SQL Authentication is not supported.
You need the Domain\Username and Password of the user, the MicrosoftSQL Server host, and the Database name.
You must install and configure Nintex Gateway on the Active Directory domain that the MicrosoftSQL Server is installed in.

Supported Microsoft SQL Server versions

The Microsoft SQL Server on-premises integration has been tested on and is supported for:

Any edition of SQL Server 2014
Any edition of SQL Server 2016

Any edition of SQL Server 2019

Note:

SQL Server 2012 is not supported.
SQL Server 2017 and 2022 may work with the Microsoft SQL Server on-premises integration, but have not been tested with it.

Create a Microsoft SQL Server on-premises data lookup

Use the Microsoft SQL Server on-premises data lookup to retrieve data from executing a stored procedure, and querying a SQL table or view. For more information on creating a data lookup and assigning permissions to it, see Data lookups.

For example, to allow users to see the results of a SQL stored procedure .The system would use the Microsoft SQL Server on-premises - Execute a SQL stored procedure data lookup to execute a stored procedure.

You can create a data lookup to:

Execute a SQL stored procedure: list the results of running a stored procedure in your Microsoft SQL Server on-premises account.
Query a SQL table: list the results of running a SQL query on a table in your Microsoft SQL Server on-premises account.
Query a SQL view: list the results of running a SQL query on a view in your Microsoft SQL Server on-premises account.

Microsoft SQL Server on-premises data lookup fields

To access the data lookup fields, in the Add data lookup section, select Microsoft SQL Server on-premises in the Connector field and then select the Operation.

Operation	Filed	Description
Execute a SQL stored procedure	Connection	The connection to use for the data lookup.
	Stored Procedure	The stored procedure of SQL statements that you want to run.
	Parameters	Click Add field to define the values of any required input parameters of the stored procedure.
	Item Limit	The number of records to return.
Query a SQL table	Connection	The connection to use for the data lookup.
	Table	The SQL table you want to retrieve data from.
	Item Limit	The number of records to return.
	Sort by	The column name to use for sorting the retrieved records.
	Sort order	Sort the retrieved records in ascending or descending order.
	Add condition	Click Add condition to define which records to retrieve. This builds a SQL WHERE statement defining what records to retrieve. When: The column selected for the condition. Operator: The operator type of the conditional case statement, such as equals, does not equal, is empty, is not empty, contains, begins with, or ends with. Value: The specific value searched for. Summary: A short description of your condition. For example, WHEN LastName equals Smith would retrieve all the records in that table where Smith is the value of the LastName column.
Query a SQL view	Connection	The connection to use for the data lookup.
	View	The SQL view you want to retrieve data from.
	Item Limit	The number of records to return.
	Sort by	The column name to use for sorting the retrieved records.
	Sort order	Sort the retrieved records in ascending or descending order.
	Add condition	Click Add condition to define which records to retrieve. This builds a SQL WHERE statement defining what records to retrieve. When: The column selected for the condition. Operator: The operator type of the conditional case statement, such as equals, does not equal, is empty, is not empty, contains, begins with, or ends with. Value: The specific value searched for. Summary: A short description of your condition. For example, WHEN LastName equals Smith would retrieve all the records in that table where Smith is the value of the LastName column.

Considerations

'EXECUTE' cannot be used within a stored procedure as this is not supported by the Microsoft SQL Server on-premises connector. See EXECUTE (Transact-SQL) for additional information.
See the following topic on how to add Null values : Variables with null value.
Table and View characters that are allowed:
1. a-z
2. A-Z
3. 0-9
4. \
5. _
6. $
7. (space)
Stored procedures characters that are allowed:
1. a-z
2. A-Z
3. 0-9
4. \
5. _
6. $
7. (space)
Depending on the column type selected, this can cause the UI to remove trailing spaces eventhough they are still present as HTML ignores trailing spaces.

Troubleshooting

If you get the following error message, try these troubleshooting tips:

Error Message

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

Cause

The number of active connections has reached the maximum pool size limit for a specific database. You can run the following query on the SQL Server to check the number of active connections:

Copy

SELECT 
    program_name,
    host_process_id,
    COUNT(*) AS session_count
FROM 
    sys.dm_exec_sessions
WHERE 
    is_user_process = 1
    AND database_id = <database_id>
GROUP BY 
    program_name,
    host_process_id;

If the result shows that the number of active connections for program_name Core Microsoft SqlClient Data Provider is nearing or exceeding the configured maximum pool size, restart Nintex Gateway or reach out to the Nintex Support team.