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 Workflow, 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 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

The Microsoft SQL Server on-premises action in Nintex Gateway opens a pool of SQL connections for each configured connection string. By default this pool supports up to 100 connections for standard SQL actions and up to 150 connections for data-source operations. If you need to limit the number of concurrent SQL connections opened by the Gateway, you can manually configure the pool size in the Gateway appsettings.json file.

Prerequisites:

  • Nintex Gateway 5.2.1 or later installed on the on-premises server.

  • Administrator access to the server where Nintex Gateway is installed.

Configure the connection pool size

  1. On the server running Nintex Gateway, locate the installation folder. The default location is:

    C:\Program Files (x86)\Nintex\Nintex Gateway\Service

  2. Open appsettings.json in a text editor as administrator.

  3. Add or update the Nintex.Gateway.Action.SQL section with the required pool size:

    "Nintex.Gateway.Action.SQL": {

    "DefaultMaxPoolSize": 10,

    "DefaultDataSourceMaxPoolSize": 10

    }

In the above:

  • Replace 10 with the maximum number of pooled connections you want to allow.

  • DefaultMaxPoolSize: Maximum pooledSQL connections for standard SQL actions. The default is 100.

  • DefaultDataSourceMaxPoolSize: Maximum pooled SQL connections for data-source operations. The default is 150.

  • CommandTimeout / ConnectTimeout: Existing related settings, in seconds, are also available in this section if you need to tune them at the same time. The default values are 30 seconds for each setting.

  1. Save appsettings.json.

  2. Restart the Nintex Gateway Windows service for the change to take effect.

    • Restart-Service -Name "NintexGateway"

Important: 

  • The minimum pool size is 1. Setting DefaultMaxPoolSize or DefaultDataSourceMaxPoolSize to 0 causes SQL actions to fail immediately with the following error:

    System.ArgumentException: Invalid value for key 'Max Pool Size'.

  • The pool size acts as a queue limit rather than a hard failure limit. If more workflow instances request connections than the configured pool size allows, additional requests wait for a connection to become available. Requests can fail if they wait longer than the SQL connection timeout, resulting in an error such as:

    Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool.

  • The effective SQL Server connection count is typically the configured pool size × 2, because the Gateway Windows Service runs as two processes, with each process maintaining its own SQL connection pool. For example, setting DefaultMaxPoolSize to 3 can result in approximately 6 active connections to SQL Server.

  • Lowering the pool size reduces concurrent connection capacity, but does not necessarily reduce total throughput. Long-running queries or a high volume of concurrent workflow runs may cause requests to queue and, if queries take too long, may result in connection timeout errors.

  • The setting applies per Gateway installation. If you have multiple Gateway servers, update the appsettings.json file and restart the Nintex Gateway Windows Service on each server where you want the setting to apply.

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.