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 Service used to define a connection required for associated actions and start events. Examples include Salesforce and Box. Method of integration to cloud services, business applications and content stores. . 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.
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
-
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 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
To access the data source fields, in the Add data source 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 source. |
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 source. |
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.
|
|
Query a SQL view | Connection |
The connection to use for the data source. |
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.
|
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 (note that spaces are not allowed):
-
a-z
-
A-Z
-
0-9
-
\
-
_
-
-
Stored procedures characters that are allowed (note that spaces are not allowed):
-
a-z
-
A-Z
-
0-9
-
\
-
_
-
#
-
$
-
@
-
-
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.