SQL Server is a relational database management system by Microsoft. It supports transactional processes, business intelligence and analytics applications for enterprises.
All versions of SQL Server are supported. However, some triggers/actions are restricted to newer versions. Refer to individual trigger/action documentation to find out.
How to connect to SQL Server on Workato
The SQL Server connector uses basic authentication to authenticate with SQL Server.
|Connection name||Give this SQL Server connection a unique name that identifies which SQL Server instance it is connected to.|
|On-prem secure agent||Choose an on-premise agent if your database is running in a network that does not allow direct connection. Before attempting to connect, make sure you have an active on-premise agent. Refer to the On-premise agent guide for more information.|
|Username||Username to connect to SQL Server.|
|Password||Password to connect to SQL Server.|
|Host||URL of your hosted server.|
|Port||Port number that your server is running on, typically 3306.|
|Database||Name of the SQL Server database you wish to connect to.|
|Azure SQL||Choose 'Yes' if connecting to an Azure SQL instance.|
Permissions required to connect
At minimum, the database user account must be granted
SELECT permission to the database specified in the connection.
If we are trying to connect to a named database (
HR_PROD) in a SQL Server instance, using a new database user
workato, the following example queries can be used.
First, create a new login and user dedicated to integration use cases with Workato.
CREATE LOGIN workato WITH PASSWORD = password; USE HR_PROD; CREATE USER workato FOR LOGIN workato;
This allows the user to have login access to the SQL Server instance. However, this user will not have access to any tables.
The next step is to grant permission to the necessary tables. There are a few ways to do this. One of the simplest ways is to grant access based on a ROLE.
ALTER ROLE db_datareader ADD MEMBER workato;
Alternatively, we can grant access to all tables defined by a SCHEMA,
GRANT SELECT,INSERT ON SCHEMA :: HR TO workato;
Finally, check that this user has the necessary permissions. Run a query to see all permissions.
SELECT pr.name, pr.type_desc, perm.permission_name, perm.class_desc, object_name(perm.major_id) AS "object", schema_name(perm.major_id) AS "schema" FROM sys.database_principals pr LEFT JOIN sys.database_permissions perm ON perm.grantee_principal_id = pr.principal_id WHERE p.name = 'workato';
This should return the following minimum permission to create a SQL Server connection on Workato.
+---------+-----------+-----------------+------------+--------+-------------+ | name | type_desc | permission_name | class_desc | object | schema | +---------+-----------+-----------------+------------+--------+-------------+ | workato | SQL_USER | CONNECT | DATABASE | NULL | NULL | | workato | SQL_USER | INSERT | SCHEMA | NULL | workatodemo | | workato | SQL_USER | SELECT | SCHEMA | NULL | workatodemo | +---------+-----------+-----------------+------------+--------+-------------+ 3 rows in set (0.20 sec)
Working with the SQL Server connector
Table, view and stored procedure
The SQL Server connector works with all tables, views and stored procedures. These are available in pick lists in each trigger/action or you can provide the exact name.
Select a table/view from pick list
Provide exact table/view name in a text field
Case sensitivity of the name of a table/view depends on your database implementation. A default SQL Server is case insensitive. Databases or database objects with
CS in the COLLATION indicates that it is case sensitive.
Single row vs batch of rows
SQL Server connector can read or write to your database either as a single row or in batches. When using batch triggers/actions, you have to provide the batch size you wish to work with. The batch size can be any number between 1 and 100, with 100 being the maximum batch size.
Batch trigger inputs
Besides the difference in input fields, there is also a difference between the outputs of these 2 types of operations. A trigger that processes rows one at a time will have an output datatree that allows you to map data from that single row.
Single row output
However, a trigger that processes rows in batches will output them as an array of rows. The Rows datapill indicates that the output is a list containing data for each row in that batch.
Batch trigger output
As a result, the output of batch triggers/actions needs to be handled differently. This recipe uses a batch trigger for new rows in the
users table. The output of the trigger is used in a Salesforce bulk upsert action that requires mapping the Rows datapill into the source list.
Using batch trigger output
This input field is used to filter and identify rows to perform an action on. It is used in multiple triggers and actions in the following ways:
- filter rows to be picked up in triggers
- filter rows in Select rows action
- filter rows to be deleted in Delete rows action
This clause will be used as a
WHERE statement in each request. This should follow basic SQL syntax. Refer to this SQL Server documentation for a comprehensive list of rules for constructing
Greater than or equal to
Less than or equal to
|IN(...)||List of values||
|LIKE||Pattern matching with wildcard characters (
|BETWEEN||Retrieve values with a range||
IS NOT NULL
NULL values check
Non-NULL values check
String values must be enclosed in single quotes (
'') and columns used must exist in the table/view.
WHERE condition to filter rows based on values in a single column looks like this.
currency = 'USD'
If used in a Select rows action, this
WHERE condition will return all rows that has the value 'USD' in the
currency column. Just remember to wrap datapills with single quotes in your inputs.
Using datapills in
Column names with spaces must be enclosed in double quotes (
"") or square brackets (
). For example, currency code must to enclosed in brackets to be used as an identifier.
[currency code] = 'USD'
WHERE condition with enclosed identifier
WHERE condition can also contain subqueries. The following query can be used on the
id in (select compensation_id from users where active = 0)
When used in a Delete rows action, this will delete all rows in the
compensation table related to users who are no longer active (
active = 0).
Using subquery in WHERE condition
SQL Server connector has triggers for both new and updated rows. For the trigger to work, both Unique key and Sort column must be configured.
A table must satisfy some constraints to be used in a trigger. The following sections contain more information about specific constraints.
In all triggers and some actions, this is a required input. Values from this selected column are used to uniquely identify rows in the selected table.
As such, the values in the selected column must be unique. Typically, this column is the primary key of the table (e.g.
When used in a trigger, this column must be incremental. This constraint is required because the trigger uses values from this column to look for new rows. In each poll, the trigger queries for rows with a unique key value greater than the previous greatest value.
Let's use a simple example to illustrate this behavior. We have a New row trigger that processed rows from a table. The unique key configured for this trigger is
ID. The last row processed has
100 as it's
ID value. In the next poll, the trigger will use
ID >= 101 as the condition to look for new rows.
Performance of a trigger can be improved if the column selected to be used as the unique key is indexed.
This is required for New/updated row triggers. Values in this selected column are used to identify updated rows.
When a row is updated, the Unique key value remains the same. However, it should have it's Sort column updated to reflect the last updated time. Following this logic, Workato keeps track of values in this column together with values in the selected Unique key column. When a change in the Sort column value is observed, an updated row event will be recorded and processed by the trigger.
Let's use a simple example to illustrate this behavior. We have a New/updated row trigger that processed rows from a table. The Unique key and Sort column configured for this trigger is
UPDATED_AT respectively. The last row processed by the trigger has
ID value of
UPDATED_AT value of
2018-05-09 16:00:00.000000. In the next poll, the trigger will query for new rows that satisfy either of the 2 conditions:
UPDATED_AT > '2018-05-09 16:00:00.000000'
ID > 100 AND UPDATED_AT = '2018-05-09 16:00:00.000000'
For SQL Server, only datetime2 and datetime column types can be used.