Microsoft SQL Server Client

overview to set up and configure this device in litmus edge, you must step 1 obtain the appropriate sql server connection details step 2 set up the microsoft sql server client device in litmus edge devicehub step 3 configure a tag with defined sql queries step 1 obtain sql server connection details you require the following connection information to connect to microsoft sql server find this informat on the sql server or from the appropriate server admin address ip/domain for the sql server port port to connect to the sql server database sql database name username and password authentication credentials to connect to sql server step 2 set up the sql server client device in litmus edge devicehub configure the following parameters when you docid\ pal6abpzbrimdu9lvgj30 with this driver update default values to the specific setup of your device parameter value type historian driver microsoft sql server client name user defined name for the device in litmus edge description (optional) description for device network address sql address you found in step 1 network port sql port you found in step 1 server instance path (optional) server instance path database name of database in sql server username enter username of sql account password enter password of sql account encryption select desired encryption mode optional doesn't require certificate mandatory verify certificate strict verify certificate and use tds8 there are additional advanced configuration that can be defined for the sql device by setting advanced to show these settings are optional parameter value app name client application id host name client workstation id request timeout request timeout in seconds default 10 seconds connection timeout connection timeout in seconds default 5 seconds keep alive interval in seconds to keep the tcp connection alive enter 0 seconds to disable packet size max packet size for sql query results step 3 configure a tag with defined sql queries when you docid\ tuy hh0gi8djvot70wroh to the connected device, see the following register table and tag parameters sections you can also docid 8oruw1cmtao49bwbbhwrn for the device see the following additional resources if necessary docid\ mu 6j6zhi0cvchdad0w8n docid\ io7xgyrdvv vmpnfxoaci register table name value types table multi row value json record single row value json, bool, bytes, float32, float64, int8, int16, int32, int64, string, uint8, uint16, uint32, uint64 tag parameters name select a register name from the drop down list the available options depend on the names in the register table value type select a data type from the drop down list the available options depend on the register name selected polling interval enter a value in seconds this determines how often the tag should query the sql server tag name enter a name for the tag description (optional) enter a description for the tag data query enter the desired sql query only publish on change select disabled or enabled select enabled for microsoft sql server change data capture (cdc) feature parse timestamp select column index , column name or none timestamp value from a specific column index or name, will be used as the timestamp value in the devicehub payload only available when name is set to record single row value timestamp index/name define the column index or name from the sql table parse value select column index , column name or none value from a specific column index or name, will be used as the value in the devicehub payload only available when name is set to record single row value value type defined above must match the sql column datatype value index/name define the column index or name from the sql table endianness select the endianness, either little or big little endian the "little end" (the least significant byte) is stored first at the lowest memory address big endian the "big end" (the most significant byte) is stored first at the lowest memory address tag formula enter a formula for the tag to process the generated data two variables are permitted value (current tag value) and timestamp (current tag unix time in milliseconds) the following math functions are available sin cos sqrt tan power power( x ) performs the operation 10^x log log( x ) is the natural logarithm (the logarithm is in base e ) exp exp( x ) performs the operation e^x only publish on change of value select the checkbox to customize nats messages to be published only when the value parameter changes to a new one change of value only applies to boolean, numeric (such as int or float), and simple string data types it does not apply to complex types, such as json or array poll once topics will not be affected by change of value settings these topics will still only see a single message meta data metadata summarizes basic information about data this feature allows you to define key value pair data for the device output payload later on it can then be used to find, use, and reuse particular instances of data note if you use special characters in meta data key names, the special characters are replaced with underscore characters in the payload this can cause two key names to be combined into one for example, configuring the key names a b and a&\&b will cause only one key name to be created (a b) enabling change data capture (cdc) cdc lets our product read only what changed in your microsoft sql server tables instead of re reading everything for more information on cd please refer to https //learn microsoft com/en us/sql/relational databases/track changes/about change data capture sql server?view=sql server ver17 in order to enable cdc, make sure only publish on change is set to enabled each cdc enabled tag uses three queries you provide data query sql query to return desired results init query returns the initial cdc position so we know where to start change query returns only the changes since the last position examples this example shows the following sql table in our microsoft sql server sql table database otdata table name metrics timestamp id metric name value area line machine 2025 06 18 08 20 00 000 1 temperature 56 a1 l1 temperature sensor 2025 06 18 08 20 00 000 2 pressure 500 a1 l3 boiler 2025 06 18 08 20 00 000 3 power 4000 a1 l4 laser cutter 2025 06 19 08 20 00 000 4 temperature 53 a1 l1 temperature sensor 2025 06 19 08 20 00 000 5 pressure 550 a1 l3 boiler 2025 06 19 08 20 00 000 6 power 4005 a1 l4 laser cutter 2025 06 20 08 20 00 000 7 temperature 51 a1 l1 temperature sensor 2025 06 20 08 20 00 000 8 pressure 600 a1 l3 boiler 2025 06 20 08 20 00 000 9 power 4010 a1 l4 laser cutter tag configuration 1 name table multi row value query select from \[otdata] \[metrics] result { "success" true, "datatype" "json", "timestamp" 1683901823577, "registerid" "dbe0c6d2 a91d 49d1 9cb7 dbf3c6d9c919", "value" { "fields" \["timestamp","id","metric name", "value", "area", "line", "machine" ], "types" \["date","int","varcar","int","varcar","varcar","varcar"], "data" \[ \["2025 06 18 08 20 00 000",1,"temperature", 56, "a1", "l1","temperature sensor"], \["2025 06 18 08 20 00 000",2,"pressure", 500, "a1", "l1","boiler"], \["2025 06 18 08 20 00 000",3,"power", 4000, "a1", "l4","laser cutter"], \["2025 06 18 08 20 00 000",9,"power", 4010, "a1", "l4","laser cutter"], ] }, "deviceid" "7c01e851 fe46 463a 90ec 98aa3f12be09", "tagname" "table all", "devicename" "mssql db", "description" "", "metadata" {} } tag configuration 2 name record single row value value type int query select from \[otdata] \[metrics] parse timestamp column index timestamp index 0 parse value column name value index value result { "success" true, "datatype" "json", "timestamp" 1750407600000, "registerid" "dbe0c6d2 a41d 46d1 2cb7 dbf3c6d9c919", "value" 4010, "deviceid" "7c01e851 fe46 463a 90ec 98aa3f12be09", "tagname" "table last entry", "devicename" "mssql db", "description" "", "metadata" {} }