SQL Server Data Connector

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

For SQL databases that are not Microsoft SQL Server, this connection type may work, however it is not guaranteed to and you may need to use the OLEDB connector.

Creating a SQL Server Data Connection

1. From the left hand menu navigate 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 defalt 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

To create a default connection string:

1. Go to the environment setting page in Manage > Settings > SQL Server.
2. Set the user id and password for the connection string.
3. Enter the remaining Connection Attributes as a semi colon delimited string containing whatever settings are required eg:
   Server=myserverlocation;Initial Catalog=MyCatalog;Persist Security Info=False;MultipleActiveResultSets=True;Encrypt=False;TrustServerCertificate=False;Connection Timeout=30;
4. 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.