After adding and authenticating a SQL Server Connection, you can add Form Connectors to push data to a database or pull data from a database using Data Source Connectors, which is what this article is about.
Our platform lets you quickly and easily connect Data Sources to your SQL Server tables, enabling one-way synchronization where any changes in the SQL table are downloaded into your data source regularly.
Your data source will be automatically updated in this way until you remove the connector or an error occurs, such as losing authorization to access the database.
In this article
- Adding
- Configuring
- Schema
- Table Name
- Where Clause
- Ignore Internal Columns
- Refresh Frequency
- Connector Logs
- Identify Updated and Deleted Rows in your SQL Table
Adding
Data Source Connectors can be added to pull data from other systems in any Data Source Settings area. Navigate the side menu to Connected Data > Data Sources and hover over the Settings option of the desired data source.
Or when viewing the rows of a data source.
On the Data Source's Settings, under the Connector area:
- Click the +Add Connector button to bring up a list of connectors.
- Click on the SQL Server option.
Configuring
After adding the Data Source Connector, configure the connector. Enter an optional schema, the table name to retrieve rows from, or an optional clause to return specific rows.
Schema
Add an optional SQL Server database schema where the table is stored. If kept empty, the default schema will be selected.
Table Name
The SQL Server database table from which rows will be retrieved.
Where Clause
Add an optional where clause to the query that only returns desired records. e.g.WHERE LastName = 'Good'.
Ignore Internal Columns
If enabled, this connector will ignore all columns where the column name starts with an underscore, e.g., _lastupdated. If your table has a column named _identity and internal columns are not ignored, this column will always be the first column of the data source. Other internal columns are added to the end.
Refresh Frequency
The frequency at which this connector pulls data.
When you Save your changes, the connector will be fired for the first time and run at the specified refresh frequency.
Wait a minute, then view the data source's rows populated with your SQL Server data.
Connector Logs
If rows are not downloaded, there may be an error with the connection, which can be confirmed by viewing the connector's logs. On the Data Source's Settings, under the connector area, hit the kebab menu icon to bring up options to View Logs, Run Now, and Delete, depending on your needs.
Identify Updated and Deleted Rows in your SQL Table
-
_lastupdated
This column contains the date-time value of when this row was last updated.
Please ensure it is a not null column of the DateTime data type. All values for this column must be in the UTC timezone. -
_deleted
A boolean column that represents whether or not this row has been deleted.
So when the next sync occurs on the data source and the value of this column exceeds the previous sync time, the platform will know to pull that row data across.
The _deleted column is how the platform determines whether the row was deleted on the SQL server side.
So, instead of simply deleting rows from the SQL table, you should mark these rows as deleted by setting this column to 1 (true) and also update the _lastupdated for that row to the current UTC time.
This is important because if you were to delete rows from the table, then the platform would never know about this, and the row would persist on the platform unless you have deleted and re-added the connector.
Once you are sure that these deletes have been synced, you can choose to delete these rows from the SQL Database entirely. However, we would suggest that you leave them in place.
