SQLServer Adapter

This adapter allows you to query SQL Server from REST commands, implement Change Data Capture (CDC) with SQL Server databases and import data into SQL Server.

Want to try it in Azure? Click here to learn more or click here to create a new Enzo Server RTM 3.1 Azure VM now.

Want to try it in AWS? Click here to learn more or click here to create a new Enzo Server RTM 3.1 AWS VM now.

Commands Summary

The following is a summary of available commands. Please run the EXEC Help command in Enzo to obtain further details.

E Can run a EXEC command

I Can run a INSERT command

S Can run a SELECT command

U Can run a UPDATE command

D Can run a DELETE command

POST Supports HTTP POST

GET Supports HTTP GET

DEL Supports HTTP DELETE

PUT Supports HTTP PUT

@ Supports Table Variables

Proc Name	Table Name	SQL Options	HTTP Methods	Description
DisablePumpJob		E	POST	Disables a SQLServer Pump Job
EnablePumpJob		E	POST	Enables a SQLServer Pump Job
GetChangeTrackingSetting	ChangeTrackingSetting	E S	GET	Returns SQL Server Change Tracking database settings if enabled (returns nothing if not configured).
GetChangeTrackingTables	ChangeTrackingTables	E S	GET	Returns list of tables that have a Primary Key (so that Change Tracking can be enabled)
GetFields	Fields	E S	GET	Returns list of fields for a given table
GetTable	table @	E S	GET	Executes a SELECT command on a table as specified by the table parameter
ListChangeTracking	ChangeTracking	E S	GET	Lists the tables that currently have ChangeTracking enabled.
ListPumpSchedule	PumpSchedule	E S	POST	Lists the scheduled Pump operations that were created with EXEC SQLServer.PumpBulk__async commands.
ListTableChanges	TableChanges	E S	GET	Gets available changes on a specific table since the last sync id.
ListTableChangesWithData	TableChangesWithData	E S	GET	Gets available changes on a specific table since the last sync id including current record data.
pingTest		E	GET	Ping test
PumpBulk		E	POST	Imports the content of a data source into a SQL Server table (BulkImport) using a loopback connection
Run		E	GET	Execute an SQL statement against the database.
RunAsJson		E		Execute an SQL statement against the database and returns a JSON document.
SetChangeTrackingSetting	ChangeTrackingSetting	E U	POST	Enable/Disable the SQL Server Change Tracking feature at the database level.
StartChangeTracking	ChangeTracking	E I	POST	Starts Change Tracking on a specific SQL Server table.
StopChangeTracking	ChangeTracking	E D	POST	Stops Change Tracking on a specific SQL Server table.
Sync		E	POST	Imports the content of a data source into a destination system using a loopback connection

Configuration Settings

Name	Description	Default
Boolean _isDefault	Make this the default configuration. Defaults to true if configName is already the default.
String connectionString	Connection string to the destination database
String defaultDB	Default database to connect to
String appName	The application name
Boolean ssl	True if the connection should be encrypted
Int32 retries	Max connection and execution retries
Int32 defaultTimeout	Default execution timeout for SQL commands	30

Handlers

DisablePumpJob

Disables a SQLServer Pump Job details

Arguments

Name	Description	Default
jobKey

SQL Call

EXEC SQLServer.DisablePumpJob 'jobkey'

Http Call

/bsc/sqlserver/disablepumpjob

Output Columns

This call does not return data.

EnablePumpJob

Enables a SQLServer Pump Job details

Arguments

Name	Description	Default
jobKey

SQL Call

EXEC SQLServer.EnablePumpJob 'jobkey'

Http Call

/bsc/sqlserver/enablepumpjob

Output Columns

This call does not return data.

GetChangeTrackingSetting
ChangeTrackingSetting

Returns SQL Server Change Tracking database settings if enabled (returns nothing if not configured). details

Arguments

SQL Call

EXEC SQLServer.GetChangeTracking

Http Call

/bsc/sqlserver/getchangetrackingsetting

Output Columns

Int32 database_id
Int32 is_auto_cleanup_on
Int32 retention_period
Int32 retention_period_units
String retention_period_units_desc
Int32 max_cleanup_version

GetChangeTrackingTables
ChangeTrackingTables

Returns list of tables that have a Primary Key (so that Change Tracking can be enabled) details

Arguments

SQL Call

SELECT * FROM SQLServer.ChangeTrackingTables

Http Call

/bsc/sqlserver/getchangetrackingtables

Output Columns

String name
String trackingSet

GetFields
Fields

Returns list of fields for a given table details

Arguments

Name	Description	Default
tableName R

SQL Call

SELECT * FROM SQLServer.Fields WHERE table = 'table1'

Http Call

/bsc/sqlserver/getfields

Output Columns

String tableName
String name
String datatype

GetTable
table

Executes a SELECT command on a table as specified by the table parameter details

Arguments

Name	Description	Default
tableName
_top
_where
_cols		*
_configName
_execTimeout		30

SQL Call

SELECT * FROM SQLServer.table@myTable1 WHERE ID=1

Http Call

/bsc/sqlserver/gettable

Output Columns

This call does not return data.

ListChangeTracking
ChangeTracking

Lists the tables that currently have ChangeTracking enabled. details

Arguments

SQL Call

EXEC SQLServer.startChangeTracking 'table1'

Http Call

/bsc/sqlserver/listchangetracking

Output Columns

String tableName
String object_id
String is_track_columns_updated_on
String min_valid_version
String begin_version
String cleanup_version

ListPumpSchedule
PumpSchedule

Lists the scheduled Pump operations that were created with EXEC SQLServer.PumpBulk__async commands. details

Arguments

SQL Call

SELECT * FROM SQLServer.PumpSchedule

Http Call

/bsc/sqlserver/listpumpschedule

Output Columns

String schedule	The CRON schedule for the job
Int32 lastReads	Number of records last read
Int32 lastWrites	Number of records last written
DateTime createdOn	Date the job was created
DateTime lastExecutedOn	Date the job was last executed
DateTime nextFireTime	Date when the job will be fired next
Int32 executionCount	Total number of executions for the job
Int32 errorCount	Total number of execution errors
String lastErrorMsg	Last error message
String destinationObject	Destination object for the job
String sourceSQL	SQL Command executed against the source system
String sourceConnectionName	Source system connection name
String targetConnectionName	Target system connection name
Int32 batchCount	Batch count for inserting records into the target table
Int64 totalReads	Number of records last read
Int64 totalWrites	Number of records last written
String upsertColumns	List of columns used for upsert operations
Int64 lastElapsedSec	Execution time of last job (in seconds)
String JobKey	The _reqUID for the job (JobKey)
Boolean disabled	When true the job is disabled

ListTableChanges
TableChanges

Gets available changes on a specific table since the last sync id. details

Arguments

Name	Description	Default
tableName R
lastSyncId		0
fireEvents		0
failOnMissedEvents		0

SQL Call

SELECT * FROM SQLServer.TableChanges WHERE tableName = 'mytable' AND sinceId = 1

Http Call

/bsc/sqlserver/listtablechanges

Output Columns

This call does not return data.

ListTableChangesWithData
TableChangesWithData

Gets available changes on a specific table since the last sync id including current record data. details

Arguments

Name	Description	Default
tableName R
lastSyncId		0
changeType		all
fireEvents		0
failOnMissedEvents		0

SQL Call

SELECT * FROM SQLServer.TableChangesWithData WHERE tableName = 'mytable' AND sinceId = 1

Http Call

/bsc/sqlserver/listtablechangeswithdata

Output Columns

This call does not return data.

PumpBulk

Imports the content of a data source into a SQL Server table (BulkImport) using a loopback connection details

Arguments

Name	Description	Default
sourceConnection R
targetConnection
sqlread R
tableName R
upsertColumns
batchCount		100
targetTableCreate		0
propagateDelete		False
sourceTimeout		3000
targetTimeout		3000

SQL Call

exec SQLServer.PumpBulk 'enzoloopback', 'localhost', 'SELECT * FROM COVID19.Summary', '[dbo].[schema].[summary]'
exec SQLServer.PumpBulk 'enzoloopback', 'localhost', 'SELECT TOP 1000 * FROM Twitter.Timeline WHERE filter=''nasdaq''', '[dbo].[schema].[tweets]', 'id'
exec SQLServer.PumpBulk 'enzoloopback', 'localhost', 'SELECT * FROM SharePoint.list@mylist', '[dbo].[schema].[splist]', 'ID', 100, 0, 1

Http Call

/bsc/sqlserver/pumpbulk

Output Columns

Int64 read	Number of rows read.
Int64 written	Number of rows written.

Run

Execute an SQL statement against the database. details

Arguments

Name	Description	Default
sql R

SQL Call

EXEC SQLServer.Run 'SELECT * FROM mytable'

Http Call

/bsc/sqlserver/run

Output Columns

This call does not return data.

RunAsJson

Execute an SQL statement against the database and returns a JSON document. details

Arguments

Name	Description	Default
sql R

SQL Call

EXEC SQLServer.RunAsJson 'SELECT * FROM mytable'

Http Call

/bsc/sqlserver/runasjson

Output Columns

String result

The JSON document

SetChangeTrackingSetting
ChangeTrackingSetting

Enable/Disable the SQL Server Change Tracking feature at the database level. details

Arguments

Name	Description	Default
setting R
retentionDays		7
autoCleanup		1

SQL Call

EXEC SQLServer.SetChangeTracking 1
EXEC SQLServer.SetChangeTracking 0

Http Call

/bsc/sqlserver/setchangetrackingsetting

Output Columns

This call does not return data.

StartChangeTracking
ChangeTracking

Starts Change Tracking on a specific SQL Server table. details

Arguments

Name	Description	Default
tableName R

SQL Call

EXEC SQLServer.startChangeTracking 'table1'

Http Call

/bsc/sqlserver/startchangetracking

Output Columns

This call does not return data.

StopChangeTracking
ChangeTracking

Stops Change Tracking on a specific SQL Server table. details

Arguments

Name	Description	Default
tableName R

SQL Call

EXEC SQLServer.stopChangeTracking 'table1'

Http Call

/bsc/sqlserver/stopchangetracking

Output Columns

This call does not return data.

BLOGS AND VIDEOS

IN THE NEWS

OTHER

SQLServer Adapter

Commands Summary

Configuration Settings

Handlers

Arguments

SQL Call

Http Call

Output Columns

Arguments

SQL Call

Http Call

Output Columns

Arguments

SQL Call

Http Call

Output Columns

Arguments

SQL Call

Http Call

Output Columns

Arguments

SQL Call

Http Call

Output Columns

Arguments

SQL Call

Http Call

Output Columns

Arguments

SQL Call

Http Call

Output Columns

Arguments

SQL Call

Http Call

Output Columns

Arguments

SQL Call

Http Call

Output Columns

Arguments

SQL Call

Http Call

Output Columns

Arguments

SQL Call

Http Call

Output Columns

Arguments

SQL Call

Http Call

Output Columns

Arguments

SQL Call

Http Call

Output Columns

Arguments

SQL Call

Http Call

Output Columns

Arguments

SQL Call

Http Call

Output Columns

Arguments

SQL Call

Http Call

Output Columns

PLATFORM

SOLUTIONS

RESOURCES

COMPANY