Capabilities

Capabilities	Supported
Connector Capabilities
Read	Yes
Backfill	No
Bidirectional Capabilities
Incident Create	No
Incident Update	No
Narrative Update	No

Bidirectional capabilities are not available for this connector.

Overview

The Data Agent runs a stored procedure in a SQL Server database with a read-only database user. The stored procedure returns closed incidents. The Data Agent then reads the units and notes for those incidents from the CAD call tables.

The Data Agent passes the stored procedure one date parameter, @dateParam. This is the start of the window. On the first run it is the first-run lookback. Later runs resume from the last closed incident. The Maximum lookback setting caps the window. The stored procedure must filter on @dateParam for the window to apply. The Data Agent reads the SQL Server clock in UTC to set the window.

No CAD data is stored on the local server running the Data Agent.

The stored procedure creates a temp table named #callIds with a CallID column. It fills the table with the calls it returns. The Data Agent joins this table to read units and notes.

SQL Server Configuration Notes

To ensure a successful and secure connection, verify the following:

Firewall Access: Ensure the server hosting the GovWorx Data Agent can reach the SQL Server over the appropriate network and port (typically TCP 1433).
Minimum Permissions: The provided database user must have read-only access to relevant tables, views, or stored procedures. Write access is not required or recommended.
Instance Visibility: If using a named instance, confirm SQL Server Browser service is running, or explicitly provide the port number.

Troubleshooting Tips

Login Failed? Verify the SQL user is enabled and not locked out.
Timeout Errors? Check network routes and firewall rules.
Invalid Database? Ensure the database field in the config matches the actual schema name.
Stored procedure error? Verify the stored procedure name matches the setting and the database user can run it.
No notes or responders? Verify the stored procedure creates the #callIds temp table and the database user can read the call, unit and personnel tables.

Network Requirements: The server running the Data Agent should be able to connect to the CAD Reporting server over port 1433. This can be tested using PowerShell.

PowerShell Command:

Test-NetConnection -ComputerName {CAD Reporting Server name or IP} -Port 1433

Standard Data Fields

These are the standard data fields the Data Agent reads. The stored procedure returns the incident fields. The Data Agent reads notes and units from the CAD call tables. Each stored procedure column name matches the GovWorx field name without spaces.

Note: Additional fields are considered enhancement requests.

Incident fields

GovWorx field	SQL Server source	Notes
Incident ID	Stored procedure	Column SysId. Required. The Data Agent keeps the first row for each SysId.
Incident Number	Stored procedure	Column Number.
Case Number	Stored procedure	Column CaseNumber.
Incident Type	Stored procedure	Column IncidentType.
Initial Incident Type	Stored procedure	Column InitialIncidentType.
Initial Location	Stored procedure	Column InitialLocation.
Caller Number	Stored procedure	Column CallerNumber.
Caller Name	Stored procedure	Column CallerName.
Agency	Stored procedure	Column Agency.
Agency Type	Stored procedure	Column AgencyType.
Incident Date	Stored procedure	Column IncidentDate.
Disposition	Stored procedure	Column Disposition.
Location	Stored procedure	Column Location.
Priority	Stored procedure	Column Priority.
Latitude	Stored procedure	Column Latitude.
Longitude	Stored procedure	Column Longitude.
Call Position	Stored procedure	Column CallPosition.
Call Taker ID	Stored procedure	Column CallTakerId.
Call Taker Name	Stored procedure	Column CallTakerName.
Call Source	Stored procedure	Column CallSource.
Call Received Time	Stored procedure	Column CallReceivedTime. The earliest CAD note timestamp setting replaces this with the earliest note time.
Call Pickup Time	Stored procedure	Column CallPickupTime.
Call Hangup Time	Stored procedure	Column CallHangupTime.
Closed At	Stored procedure	Column ClosedAt. The Data Agent uses this time to resume the next run.
Dispatch Queue At	Stored procedure	Column DispatchQueueAt.
Additional Context	Stored procedure	Column AdditionalContext.
Response Area	Stored procedure	Column ResponseArea.
Radio Channel	Stored procedure	Column RadioChannel.

Notes

GovWorx field	SQL Server source	Notes
Note ID	`call.CallNarrative.CallNarrativeID`
Note Text	`call.CallNarrative.Narrative`
Note By	`call.CallNarrative.EnteredUser`
Note Time	`call.CallNarrative.CreateDatetime`

Responders and units

GovWorx field	SQL Server source	Notes
Unit Name	`unit.unit.UnitNumber`	Matches unit.unit to call.CallUnitHistory by UnitID.
Personnel ID	`readonly.personnel.IDNumber`	The person assigned to the unit on the call.
Dispatched By	`general.users.LoginID`	Matches the user that entered the unit record.
Dispatched At	`call.CallUnitHistory.DispatchDateTime`
Enroute At	`call.CallUnitHistory.EnrouteDateTime`
Arrived At	`call.CallUnitHistory.ArriveDateTime`
Cleared At	`call.CallUnitHistory.ClearDateTime`

Data Agent Settings

To prepare for this connection, we need the following details. These are settings in the GovWorx Data Agent configuration for this connector. A toggle is an on/off switch in that configuration.

Setting	Type	Explanation
`Host`	Text	Host name or IP of the SQL Server
`Use Integrated Security`	Toggle	Toggle will enable the use of a domain service account that is configured to run the agent service as authentication to SQL server
`Username`	Text	Database user. Not needed with integrated security.
`Password`	Password	Database password. Not needed with integrated security.
`Database`	Text	Database to read from
`Stored Procedure Name`	Text	Name of the stored procedure the Data Agent runs. Default: govworx.
`First-run lookback in hours`	Number	Hours back to look on the first run, when no earlier run exists. Default: 1.
`Maximum lookback in hours`	Number	Caps how far back a stale cursor can reach so an idle or reconnected connector does not issue an oversized catch-up query. Default: 8.
`Use earliest CAD note timestamp for Received At`	Toggle	Toggle will use the earliest CAD note time as Call Received Time. Use it when the first note marks the start of the call better than the CAD pickup time. Call Pickup Time does not change. When no note has a time, Call Received Time stays as read from CAD. Default: off. This setting is on every CAD connector.

CAD Connector - SQL Server