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.

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

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.