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.
- Both Machine 1 and Machine 2 are running an instance of SQL server.
- Machine 1 contains the DataStoreDSX database.
- 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.
- 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 - 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 case‐insensitive
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 as‐is 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