Skip to main content
Mitratech Success Center

Creating a Search Data Provider with a Linked SQL Server

Search Data Providers work with SQL views and tables that are available on the same SQL Server instance as the DataStoreDSX database. However, if the external view or table is not on the same SQL Server instance as the DataStoreDSX database, you will have to configure a linked server on this SQL instance connecting to the other SQL Server instance which contains the required view or table and then create another view on the SQL instance which contains the DataStoreDSX database which uses the linked server.

  1. Both Machine 1 and Machine 2 are running an instance of SQL server.
  2. Machine 1 contains the DataStoreDSX database.
  3. Machine 2 contains the SQL database containing the data required for the Search Data Provider. This database table must follow the format described below:
    • Every row must be identified by PK_idRow which can be either an Int or BigInt.
    • Integers can be passed through.
    • Floating point numbers need to be converted to “DECIMAL(28, 10)”
    • DateTime fields must be split in the following way:
      <ColumnName>
      <ColumnName>_Date
      <ColumnName>_Time
    • Strings must be collated as Latin1_General_CI_AI.
  4. Create a linked server on Machine 1. For example,
    EXEC master.dbo.sp_addlinkedserver
    @server = N'LINKTOB'
    ,@srvproduct=N'B'
    ,@provider=N'SQLOLEDB'
    ,@datasrc=N'Machine2\sql2008r2'; ‐‐ this is where the actual data required for
    -- the Search Data Provider exists 
  5. Create a View on Machine 1 which is based on the table on Machine 2. For example,

CREATE VIEW

[dbo].[ExternalCustomersLinkedServer]

AS
SELECT

every row must have a unique ID column with this name

ROW_NUMBER() OVER(ORDER BY linkc.[CustomerId]) AS [PK_idRow],

‐‐c.[CustId] AS [PK_idRow],

‐‐ integers can just pass through
linkc.[CustomerId] as [link CustomerId],
linkc.[Age],

‐‐ strings must be caseinsensitive

linkc.[AccountId] COLLATE Latin1_General_CI_AI AS [AccountId],
linkc.[Customer Name] COLLATE Latin1_General_CI_AI AS [CustomerName],
linkc.[CusomerAddress] COLLATE Latin1_General_CI_AI As [CustomerAddress],
linkc.Dob as [Dob123],

‐‐ arithmetic to convert day part into YYYYMMDD

CONVERT(BIGINT, (10000 * DATEPART(YEAR, linkc.Dob)) +
(100 * DATEPART(MONTH, linkc.Dob)) + 
DATEPART(DAY, linkc.Dob)) AS [Dob123_Date],
99999999 as [Dob123_Time],

‐‐ date/times must exist asis and must also be split into date and

‐‐ time components. The split columns must be named with _Date and

‐‐ _Time suffixes

linkc.accountopened AS [AccountOpened],

‐‐ arithmetic to convert day part into YYYYMMDDCONVERT(BIGINT, (10000 * DATEPART(YEAR, linkc.accountopened)) + (100 * DATEPART(MONTH, linkc.accountopened)) +

DATEPART(DAY, linkc.accountopened)) AS [AccountOpened_Date],

‐‐ arithmetic to convert time part into milliseconds since midnight
(3600000 * DATEPART(HOUR, linkc.accountopened)) +
(60000 * DATEPART(MINUTE, linkc.accountopened)) +
(1000 * DATEPART(SECOND, linkc.accountopened)) +
DATEPART(MILLISECOND, linkc.accountopened) AS [AccountOpened_Time]

FROM
    LINKTOB.Customers.dbo.Customers linkc

  • Was this article helpful?