The MS SQL Connector connects SIA Connect to a Microsoft SQL Server database through ODBC. You can use read items to run queries and return database values, and mappings to send complete SQL statements to the database.

This guide explains how to configure an MS SQL instance, create read items, execute stored procedures, write data, interpret returned values, and troubleshoot connection or query errors.

Before you begin

Make sure that you have:

• Installed the MSSQL Connector.
• The hostname or IP address of the SQL Server.
• The TCP port used by SQL Server. The default is 1433.
• The database name.
• A database username and password.
• Enabled TCP/IP access and allowed the connection through the relevant firewalls.
• Granted the database account permission to run the required queries or stored procedures.

For SQL Server-side network preparation, see Setting up the database for external communication.

Create an MS SQL instance

1. In the Edge Portal, go to Instances.
2. Select Add instance.
3. Select the MSSQL Connector.
4. Enter a name for the instance.
5. Enter the SQL Server hostname or IP address in Address.
6. Complete the Connector parameters.
7. Select Create.

The instance attempts to connect when it is enabled and used.

Instance parameters

Create a read item

A read item runs the statement in Query default whenever the item is read. The result is stored as the item value.

1. Open the MS SQL instance.
2. Open the Items tab.
3. Select Add item.
4. Enter a name and UID for the item.
5. Set the item to allow reading.
6. Enter a read query in Query default.
7. Configure the read interval or event-based behavior required by your application.
8. Select Create.

For example:

SELECT TOP (1) temperature, status
FROM dbo.machine_state
WHERE machine_id = 1
ORDER BY recorded_at DESC

The returned value is a compact JSON array. A result could look like this:

[{"temperature":21.5,"status":"running"}]

Avoid unrestricted queries against large tables. Use filters, TOP, and an appropriate ORDER BY clause to limit the returned data.

Read data from a stored procedure

A read item can execute a stored procedure that returns a result set. Supported forms include EXEC, EXECUTE, CALL, and the ODBC call escape syntax. A bare procedure name can also be recognized.

For example:

EXEC dbo.GetLatestMachineState @MachineId = 1

The Connector returns the rows from the procedure's result sets in one JSON array. Stored-procedure reads use a five-second query timeout. The ODBC connection used for these reads defaults to TDS version 7.4 and a text size of 16777216 bytes unless those attributes are already present in the connection string.

If a procedure performs setup statements before it runs, leading SET statements can be included before the procedure call.

Write data to MS SQL

Each enabled MS SQL instance automatically receives a write-only item named Query write. Its UID ends with _WRITE.

Map a complete SQL statement to the write item. The Connector executes the incoming item value directly as SQL.

For example, the incoming value could be:

INSERT INTO dbo.measurements (source, value)
VALUES ('line_1_temperature', 21.5)

You can also send an UPDATE, DELETE, or stored-procedure statement if the database account is authorized to execute it.

For additional examples, see Query examples.

Understand returned values

Query results are returned as a JSON array. Each database row becomes an object, and each selected column becomes a property.

JSON stored in a SQL Server text column remains a string inside the returned object. Parse or unwrap it in a mapping if another system requires the nested JSON value.

Verify the configuration

1. Confirm that the instance and the required items are enabled.
2. Open the instance and check its status and most recent error.
3. Open a read item and confirm that its value is a JSON array containing the expected columns.
4. Trigger a test mapping to Query write.
5. Confirm the inserted or updated value in SQL Server.
6. Review the item, mapping, and system logs if the expected result is not returned.

Troubleshoot the MS SQL Connector

The instance reports Failed to open database or ODBC failed to connect

• Confirm the Address, Port, Username, Password, and Database values.
• Confirm that SQL Server is running and listening on the configured TCP port.
• Check the firewall between SIA Connect and SQL Server.
• Confirm that SQL Server authentication accepts the configured account.
• Review any additional ODBC attributes entered in Connection string.

SQL Server reports Login failed for user

Confirm the username and password, the enabled authentication mode, and whether the login is permitted to connect to the server.

SQL Server cannot open the requested database

Confirm the Database value and grant the configured login access to that database.

The connection times out

Check routing, DNS resolution, the TCP port, and firewall rules. The Connector applies a five-second connection and login timeout. Stored-procedure reads also use a five-second query timeout.

The query reports Invalid object name or Invalid column name

Confirm the database, schema, table, view, and column names. Include the schema, such as dbo.machine_state, when needed.

The query reports a syntax error

Run the same statement in a SQL Server query tool using the same database account. Correct SQL Server-specific syntax, quoting, procedure parameters, or missing separators before updating Query default or the mapping.

The query reports a permission error

Grant only the required SELECT, INSERT, UPDATE, DELETE, or EXECUTE permission. Do not grant broader database roles solely to bypass an error.

A read query is blocked

Move modifying statements to a mapping that writes to Query write. Read items reject detected UPDATE, DELETE, INSERT INTO, DROP, and TRUNCATE statements.

The Connector reports Empty query

Confirm that Query default is not empty and that the statement returns a result set. For a stored procedure, confirm that it returns rows rather than only a return code or affected-row count.

A stored procedure fails or returns no data

• Test the procedure with the same account and parameters in SQL Server.
• Confirm that the account has EXECUTE permission.
• Confirm that the procedure returns a tabular result set within five seconds.
• Use an explicit EXEC statement if a bare procedure name is ambiguous.
• Check for ODBC query failed, ODBC failed while fetching rows, or ODBC failed while advancing result sets in the item error and logs.

Large numbers or decimals are returned as strings

This protects values from precision loss in JSON. Handle the value as a string, or explicitly convert it in SQL when a numeric JSON value is required and the reduced precision is acceptable.

The connection drops after previously working

Errors such as Communication link failure, Invalid cursor state, Unexpected EOF, Connection reset, or Broken pipe cause the Connector to mark its cached connection for reconnection. Check SQL Server availability and the network path if the error continues.

A write mapping fails

Review the mapping error. It includes the SQL Server error and the query that was executed. Confirm that the incoming value is a complete SQL statement and check for invalid quoting, conversion errors, data-length limits, key conflicts, deadlocks, and insufficient permissions.

Related articles

• Setting up the database for external communication
• Query examples
• Install and manage Connectors
• Create and edit instances
• Create and edit items
• Create and edit mappings
• Troubleshoot Connector, instance, item, and mapping errors
• Configure and review system logs