Transaction Logs

SmartIQ connects directly to Microsoft SQL Server tables, views and stored procedures to to read and write data using ODBC.

SQL Server Data Sources require a valid connection string containing the location and authentication information of the server, as in the example below

Persist Security Info=True;Initial Catalog=SampleData;Data Source=server001

Creating a SQL Server Data Connection

1. From Manage, go to Data Connections and click the New Data Connection button.
2. Select SQL Server as the connection type.
3. If you don't have a default SQL Server connection string configured or need to override the default connection string, provide the connection string in the Connection Attributes box.
4. Where SQL server authentication is being used. Use the credentials section to provide a username and password.
5. Use the Test Connection button to confirm that SmartIQ is able to establish a connection.
6. Click Save.

👍

Good Practice

It is recommended to enable "Allow Connection Export" as it will include the connection string in exported projects which use it, allowing the connection to be exported to a different environment

After the connection to SQL Server Data Source has been established, it is possible to point to specific tables and views contained on the server, referred to as data objects.

Configure a Default SQL Server Connection String

🚧

Important

SQL Server connections are now secure by default, which means that encryption is expected to be enabled for these connections.

To create a default connection string:

1. In Manage, go to Settings > SQL Server.
2. Enter the remaining Connection Attributes as a semi colon delimited string containing whatever settings are required, for example:
   Server=myserverlocation;Initial Catalog=MyCatalog;Persist Security Info=False;MultipleActiveResultSets=True;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;
3. Set the user id and password for the connection string.
4. (Optional) For multiple accounts:
   1. Click the New Set button to create additional accounts. For example, Sandbox and Production.
   2. Click the Set as Default button to change the default connector set. If the previous Default was set in Design, SmartIQ will use the new Default values.

5. Click Save.

Table/View Data Objects

Tables can be used as a read only or read/write data objects. With a data connection established using the steps above, click the "Data Objects" button to view existing data objects. To create a new SQL table data object click "New Data Object" and provide:

1. Object Type as "Table/View"
2. The name of your table or view in SQL e.g. 'employee' as the Object Name Definition.
3. A friendly name for the table
4. Select the access type, Read Only Or Read/Write as appropriate.
   Click "Save"
   A success message together with dialogues allowing the selection of Filter, Schema and Display Fields will display. For more information on how to configure these see This article for more information.

📘

Writable Data Objects

Rows within SQL tables containing a single Primary Key or Identity Column can be added, modified and deleted when their access is set to Read/Write.

The above screen shot connects to a table similar to the one scripted below.

CREATE TABLE [dbo].[Employee] (
  [EmployeeNo] [int] IDENTITY (1, 1) NOT NULL,
  [FirstName] [nvarchar](100) NULL,
  [LastName] [nvarchar](100) NULL,
  [Title] [nvarchar](100) NULL,
  [Salary] [numeric](7, 2) NULL,
  CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
  (
  [EmployeeNo] ASC
  ))
INSERT INTO Employee (FirstName, LastName, Title, Salary)
  VALUES ('John', 'Schimdt', 'Solution Engineer', 1200)
INSERT INTO Employee (FirstName, LastName, Title, Salary)
  VALUES ('Lincoln', 'Smith', 'Manager', 1200)
INSERT INTO Employee (FirstName, LastName, Title, Salary)
  VALUES ('Jackson', 'Martin', 'Manager', 1200)

Stored Procedure Data Objects

SQL Stored procedures provide read only access to data. Their input parameters are defined as filter fields. To Add a SQL Stored Procedure Data Object, from the appropriate data connection click on "New Data Object" and provide:

1. Object Type: "Stored Procedure"
2. Data Object Name / Definition: The name of the stored procedure together with sample input parameters separated by commas surrounded by brackets. For example "spGetEmployee(0)"
3. Display Name: A friendly name for the stored procedure
   Click "Save"

📘

Sample Parameters

As stored procedures do not provide a static response the data object must be provided with sample input parameters so that a sample response can be processed by the data object. Ensure the sample parameters will return an efficient, consistent response.

A success message together with dialogues allowing the selection of Filter and Display Fields will display. The available filter fields is a list containing the input parameters of the procedure, use the add all button and hit save so that these can later be configured in Design. For more information on how to configure filter and display fields This article for more information.

The above examples were taken from the procedure below.

CREATE PROCEDURE [dbo].[spGetEmployee]
--Input parameter
@EmployeeNo int
AS
BEGIN
	SET NOCOUNT ON
	SELECT *
	FROM Employee
	WHERE EmployeeNo = @EmployeeNo
END
GO

Known Limitations

SQL Server imposes 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 for this other than to limit the number of values that are passed to SQL Server as filters.

SQL Server

🚧

Important

SQL Server connections are now secure by default, which means that encryption is expected to be enabled for these connections.

The SQL Server Database Action Delivers Data to SQL Server via a Stored Procedure via OLEDB. This connection type will produce the best performance for SQL datasources compared to other connection types.
The basic format for the connection string is:

Server=server-name;Persist Security Info=True/False;Encrypt=True;TrustServerCertificate=True;User ID=user-ID;Initial Catalog=SQL-Database-name;password=login-password

Example Connection Strings

Server=tcp:xyzsqlserver.database.windows.net,1433;Persist Security Info=False;Encrypt=True;TrustServerCertificate=True;User ID=smartiqID;Initial Catalog=Sample Data;Data Source=smartiqXYZ;password=smartiqpassword

SQL Server connections usually require a username and password, these credentials can be part of the connection string or more securely, the username and password fields can be used on the data source screen properties hiding the password from plain view.