Introduction

Enzo Server
  Installation
  Quick Start
  Core Features
    Async Calls
    ConnectionStrings
    Edge Cache
    HTTP Access
    Scheduling
    Views
  Advanced Capabilities
    Change Data Capture
  Administration
    Configuration Settings
    Logins & ACL
    Auditing
    Current Executions
    Linked Server
    SSL

  User Guides
     Sharding
     Sharding Overview

     SharePoint

   Adapters
    All Adapters
    ActiveCampaign
    Azure Bus
    Azure IoT Hub
    Azure Key Vault
    Azure Storage
    Bing Search
    ConstantContact
    Couchbase
    CSV
    DB (RDBMS)
    Dropbox
    Files
    Flight Aware
    FreshBooks
    FTP
    Geonames
    GoogleDrive
    GoogleSearch
    GoogleSheet
    GreenMile
    GpsInsight
    HttpEndpoint (REST)
    Intacct
    Insteon
    Loggly
    MailChimp
    Messaging (SMS/SMTP)
    MongoDB
    Motus
    MSAD
    MSMQ
    OneDrive
    OneDrive365 (Business)
    Pipeline
    Pressero
    RabbitMQ
    SalesForce
    Shard
    SharePoint
    Slack
    SQL Server
    SQLX
    Sudoku
    Teams
    Threading
    Twilio
    Twitter
    VellemanDMX
    Whiparound
    WMI
    Xls (Excel)
    ZIP


  SDK
    Overview
    Installation
    DevHost
    Create A Simple Adapter
    Handler Columns

    Advanced SDK Concepts
      Handler Decorators
      Dynamic Columns
      Table & Table Enumerators
      Virtual Tables

/adapters/sqlserver




SQLServer


The SQLServer adapter allows you to access the Change Tracking capabilities of SQL Server databases and import data into SQL Server.


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 scheduleThe CRON schedule for the job
Int32 lastReadsNumber of records last read
Int32 lastWritesNumber of records last written
DateTime createdOnDate the job was created
DateTime lastExecutedOnDate the job was last executed
DateTime nextFireTimeDate when the job will be fired next
Int32 executionCountTotal number of executions for the job
Int32 errorCountTotal number of execution errors
String lastErrorMsgLast error message
String destinationObjectDestination object for the job
String sourceSQLSQL Command executed against the source system
String sourceConnectionNameSource system connection name
String targetConnectionNameTarget system connection name
Int32 batchCountBatch count for inserting records into the target table
Int64 totalReadsNumber of records last read
Int64 totalWritesNumber of records last written
String upsertColumnsList of columns used for upsert operations
Int64 lastElapsedSecExecution time of last job (in seconds)
String JobKeyThe _reqUID for the job (JobKey)
Boolean disabledWhen 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 readNumber of rows read.
Int64 writtenNumber 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 resultThe 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.