SQL Server and PostgreSQL
SmartIQ can connect directly to Microsoft SQL Server and PostgreSQL tables, views and stored procedures/functions to to read and write data. Available actions that use SQL connections deliver data to an SQL Server via a Stored Procedure via OLEDB.
Relevant Links
External documentation: https://www.connectionstrings.com/sql-server/ , https://www.connectionstrings.com/postgresql/, https://www.connectionstrings.com/microsoft-ole-db-provider-for-sql-server-sqloledb/
SQL Server and PostgreSQL impose a limit of 2100 parameters per query
In certain cases, particularly when using Aggregate data filters, SmartIQ may exceed this limit. There is no workaround to increase the parameters, only the recommendation to limit the number of values that are passed to SQL Server as filters.
Connector Set-Up
SQL Server Data Sources require a valid connection attributes containing the location and authentication information of the server, i.e. Persist Security Info=True;Initial Catalog=SampleData;Data Source=server001
| String Key | Description |
|---|---|
| Server | Specifies the server/database name |
| Persist Security Info | It is recommended to set this to False when the user ID and password are included in the connection string |
| Encrypt | Encrypts SQL server data connections/actions. By default, this is set to ‘true’. |
| TrustServerCertificate | Set to ‘true’ if the SSL Provider connection mentions the authority or certificate name. |
| User ID | Login ID |
| Password | Login Password |
| Initial Catalog | The SQL database name |
The connection attributes for SQL Server, PostGres, and OLEDB get used as a standard SQL-style connection string. External documentation: https://www.connectionstrings.com/sql-server/ , https://www.connectionstrings.com/postgresql/, https://www.connectionstrings.com/microsoft-ole-db-provider-for-sql-server-sqloledb/
Credentials
No credentials
Simple username and password
Data Objects
Table/View Data Objects
Tables can be used as a read only or read/write data objects.
Rows within SQL tables containing a single Primary Key or Identity Column, with access set to read/write, can be used with Writable Data Objects to add, modify, and delete data.
Stored Procedure/Function Data Objects
SQL Stored procedures/Functions provide read only access to data, with their input parameters defining the filter fields. Data Object Name / Definition: The name of the stored procedure/function, with valid sample inputs for each parameter, separated by commas surrounded by brackets, e.g function(valid input, ...)
Sample Parameter Inputs
As stored procedures do not provide a static response, the data object must be provided with valid sample input parameters so that a sample response can be processed by the data object. Ensure the sample parameter inputs are valid and will return an efficient, consistent response.
Updated about 1 month ago