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. For more information on Microsoft SQL Server on-premises see Microsoft SQL Server on-premises.

Create a Microsoft SQL Server on-premises connection

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 ServerDomain 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 Azure Active Directory domain that the MicrosoftSQL Server is installed in.

Supported Microsoft SQL Server versions

The Microsoft SQL Server on-premises integration 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 no longer supported.

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.

Create a Microsoft SQL Server on-premises data source

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

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 source to execute a stored procedure.

You can create a data source 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 source fields

When you create a Microsoft SQL Server on-premises data source, you can configure some additional fields to specify which tables, views, and stored procedures the data source should retrieve.

Field

Selections

Microsoft SQL Server on-premises - Execute a SQL stored procedure

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.

Microsoft SQL Server on-premises - Query a SQL table

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.

Microsoft SQL Server on-premises - Query a SQL view

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.

Consideration

  • '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.