Microsoft SQL Search Data Provider
The Microsoft SQL Search Data Provider enables you to reference an external database table or view. When a Search Template (with a Microsoft SQL Search Data Provider configured) is used to search the DataStoreDSX database, data from the external view or database is also returned.
Licence required: Search Assistants require a licence. If you do not see the options described here, contact support@mitratech.com about purchasing a licence.
Caution: Although it is possible to configure Search Assistants and Search Data Providers which use the same Search Template fields, it is not recommended unless you are sure it will produce the desired results. The order these are run in is: Request Search Assistant, Search Data Provider and then Result Search Assistant.
In this example, a new Search Template is created from the Data Definition Bank Statement. The Reference Number field is used to retrieve the Customer Name and Customer Age from an external database view. This information is then returned with the Index field values.
Note: Only users who are proficient in using SQL Server should attempt to configure Microsoft SQL Data Providers.
The executing Management Studio client makes the database connection directly – it does not proxy through the DataStoreDSX service. Therefore, the Windows user running the Management Studio client must have direct access to query the database server. This applies even when the user logs in to DataStoreDSX using a username and password. In addition, the DataStoreDSX service configuration for the database connection string must use a machine name / IP, etc. that is accessible from the Management Studio client machine – not localhost as this will cause the Management Studio client to attempt to connect to itself.
Create the External View
The view used in this example is:
CREATE VIEW
[dbo].[ExternalCustomerView]
AS
SELECT
‐‐ every row must have a unique ID column with this name
ROW_NUMBER() OVER(ORDER BY c.[CustomerId]) AS [PK_idRow],
‐‐ integers can just pass through
c.[CustomerId],
c.[Age],
‐‐ strings must be case‐insensitive
c.[Customer Name] COLLATE Latin1_General_CI_AI AS [CustomerName],
c.[CusomerAddress] COLLATE Latin1_General_CI_AI AS [CusomerAddress],
c.[AccountId] COLLATE Latin1_General_CI_AI AS [ReferenceNumberId],
‐‐ date/times must exist as‐is and must also be split into date and
‐‐ time components. The split columns must be named with _Date and
‐‐ _Time suffixes
c.[Dob] AS [Dob],
‐‐ arithmetic to convert day part into YYYYMMDD
CONVERT(BIGINT, (10000 * DATEPART(YEAR, c.[Dob])) +
(100 * DATEPART(MONTH, c.[Dob])) +
DATEPART(DAY, c.[Dob])) AS [Dob_Date],
‐‐ arithmetic to convert time part into milliseconds since midnight
(3600000 * DATEPART(HOUR, c.[Dob])) +
(60000 * DATEPART(MINUTE, c.[Dob])) +
(1000 * DATEPART(SECOND, c.[Dob])) +
DATEPART(MILLISECOND, c.[Dob]) AS [Dob_Time],
‐‐ decimals are represented using the same SQL data type as in the
‐‐ DataStore decimal value table
CONVERT(DECIMAL(28, 10), c.[Balance]) AS [Balance]
FROM
Customers c
GO
Example Data
Create the Search Template
Note: You must first configure the Search Assistant Options in the Plug-in Options pane to activate the Search Assistant Configuration button.
- Right-click on the Data Definition from which you want to create the Search Template and select Create Search Template from the menu. Enter a name for the Search Template (SDP Bank Statement in this example) and click OK.
- Edit the newly created Search Template. Create a new search field Customer Name. Click on the Field type button, select the Search Assistants tab and select Search Assistant Text as the field type.
- Create another new search field Age. Click on the Field type button, select the Search Assistants tab and select Search Assistant Whole Number as the field type.
- Click on the Data tab and then click the Search Assistant Configuration button.
- Click the Add Microsoft SQL Data Provider button.
- Click the Refresh Databases button and then click the arrow to the left of it to display a drop-down list of available databases.
Note: To link to a database table which is on a different machine (that is, it is not on the same machine as the DataStoreDSX database) see “Creating a Search Data Provider with a Linked SQL Server”
- Select the database which contains the required table or view.
- Select the required table or view from the drop-down list.
- The selected table or view is displayed beside Configured Table or View.
- When you click the Auto Map button, Management Studio searches for search fields. If there is a search field name which includes ‘Id’ and it matches a column name, the search field and column name are mapped in the Joining fields. (Spaces in the names and capitalisation is ignored, so the search field Reference Number Id matches the column named ReferencenumberId.
- A joining field provides the link between an Index field and a database column which is used to identify the extra data associated with that search result. The defined view defines the mapping between the database field and the Joining field’s External Column.
Alternatively, Joining fields can be configured manually. Select the search field which will be the joining field. In this example, the column named AccountID is collated to ReferenceNumberId. Hence the search field Reference Number Id is mapped to ReferenceNumberId.
Note: The field type of the Search field must be the same as the column type. For example, a Whole Number search field must be joined to an integer column in the database. The Search Assistant Configuration cannot be saved if the field types do not match.
- After mapping the Joining fields, Management Studio searches for Search Assistant type Search Fields and populates the Mapping Fields with those which match column names. (Spaces in the names and capitalisation is ignored, so the search assistant search field Customer named matches the column named CustomerName.)
Alternatively, Mapped Fields can be configured manually. Select a search assistant field from the Mapped Fields Search Template Field drop-down list. Then select the appropriate External Column from the drop-down list. Repeat for more fields, if required.
Note: The field type of the Search Assistant field must be the same as the column type. For example, a Search Assistant Whole Number search field must be joined to an integer column in the database. The Search Assistant Configuration cannot be saved if the field types do not match.
- The Mapped Fields are Search Assistant fields which take information from the configured table or view.
- Click the Save current Search Assistant button
to save only this Search Assistant or the Save all Search Assistants button 
to save all the Search Assistants in the current Search Assistant Configuration pane.
Note: There is a one-to-one mapping between fields and columns. Once a search field has been mapped or joined once, it cannot be used again – the Search Assistant Configuration cannot be saved when a field is used more than once.
Using Document Version and Document Identifier Fields in SDPs
The Audit fields Document Version and Version-Independent Document Identifier can be used as link and join fields in Search Data Providers.
Note: When a Document Version audit field is added to a Search Template, this field is automatically linked to the appropriate Data Definition and the option Hide from search user is automatically selected and cannot be changed. This does not apply to any other fields.
In addition, the database view must define the Document Identifier and the Document Version as shown in this example view.
‐‐ vwDoc_Version_Doc_Identifier_FileLookup
‐‐ External SDP view to Identifier and Version to search results CREATE VIEW [dbo].[vwDoc_Version_Doc_Identifier_FileLookup]
AS
SELECT
ROW_NUMBER() OVER(ORDER BY tb.UniqueKey) AS [PK_idRow],
tb.VersionIndependentDocument AS [DocIdentifier],
tb.MinorVersion + tb.MajorVersion * 65536 AS [Version],
tb.ExternalFileOwner COLLATE Latin1_General_CI_AI AS ExternalFileOwner
FROM
tbExternalFileData tb
GO
In the example,
- VersionIndependentDocument column is a uniqueidentifier data type.
- Minor and Major version columns are numeric, combined to a single integer.
- ExternalFileOwner is an example string column that you could use as a result column in the Search Data Provider.