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.
- From Manage, go to Data Connections and click the New Data Connection button.
- Select SQL Server as the connection type.
- 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.
- Where SQL server authentication is being used. Use the credentials section to provide a username and password.
- Use the Test Connection button to confirm that SmartIQ is able to establish a connection.
- Click Save.
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.
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:
- In Manage, go to Settings > SQL Server.
- 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;
- Set the user id and password for the connection string.
- (Optional) For multiple accounts:
- Click the New Set button to create additional accounts. For example, Sandbox and Production.
- 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.
- Click Save.
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:
- Object Type as "Table/View"
- The name of your table or view in SQL e.g. 'employee' as the Object Name Definition.
- A friendly name for the table
- Select the access type, Read Only Or Read/Write as appropriate.
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)
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:
- Object Type: "Stored Procedure"
- 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)"
- Display Name: A friendly name for the stored procedure
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
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.
Updated 6 months ago