For Users Upgrading Connectivity Hub

If you are upgrading Connectivity Hub from version 2.0 to 2.1 or later, run the following script against your Smart Previews database before the upgrade to keep previews and content indexed synchronized.

The script can be found in the following Connectivity Hub installation directory:

• <ConnectivityHubInstallDir>\DatabaseCreationScript\UpdateSmartPreviewsDatabases.sql

Before running the script, replace the following tokens:

• #SmartPreviews_Configuration_DB#

  • Put configuration database name here.

• #SmartPreviews_PreviewCache_DB#

  • Put preview cache database name here.

  • If your installation of Smart Previews uses multiple preview cache databases, run the script for each preview cache database separately.

• #SmartPreviews_UserProfile_DB#

  • Put user profiles database name here.

The script below is provided for reference.

Run the script which is deployed by the Connectivity Hub installer.

DECLARE @batchSize int = 1000;
 
DECLARE @ids TABLE(Id int);
DECLARE @lastId int = 0;
 
USE #SmartPreviews_Configuration_DB# -- Put Configuration database name here.
 
PRINT 'Updating [ProcessedDocuments] table.'
WHILE @lastId = 0 OR EXISTS(SELECT 1 FROM @ids)
BEGIN
    DELETE FROM @ids;
    INSERT INTO @ids
        SELECT TOP(@batchSize) ProcessedDocumentId
        FROM ProcessedDocuments
        WHERE DocumentUri LIKE 'spworks://%' AND ProcessedDocumentId > @lastId
        ORDER BY ProcessedDocumentId;
 
    SELECT @lastId = MAX(Id) FROM @ids;
     
    UPDATE ProcessedDocuments
        SET DocumentUri = REPLACE(REPLACE(REPLACE(REPLACE(DocumentUri, '&id=&', '&'), '&subid=&', '&'), '&fid=&', '&'), '&fsubid=&', '&'),
            UriHash = CONVERT(binary(16), HASHBYTES('MD5', REPLACE(REPLACE(REPLACE(REPLACE(DocumentUri, '&id=&', '&'), '&subid=&', '&'), '&fid=&', '&'), '&fsubid=&', '&')))
        WHERE ProcessedDocumentId IN (SELECT Id FROM @ids);
END
 
USE #SmartPreviews_PreviewCache_DB# -- Put PreviewCache database name here.
 
PRINT 'Updating [DocumentContainer] table.'
SET @lastId = 0;
WHILE @lastId = 0 OR EXISTS(SELECT 1 FROM @ids)
BEGIN
    DELETE FROM @ids;
    INSERT INTO @ids
        SELECT TOP(@batchSize) DocumentContainerId
        FROM DocumentContainer
        WHERE DocumentUri LIKE 'spworks://%' AND DocumentContainerId > @lastId
        ORDER BY DocumentContainerId;
 
    SELECT @lastId = MAX(Id) FROM @ids;
     
    UPDATE DocumentContainer
        SET DocumentUri = REPLACE(REPLACE(REPLACE(REPLACE(DocumentUri, '&id=&', '&'), '&subid=&', '&'), '&fid=&', '&'), '&fsubid=&', '&')
        WHERE DocumentContainerId IN (SELECT Id FROM @ids);
END
 
USE #SmartPreviews_UserProfile_DB# -- Put UserProfiles database name.
 
PRINT 'Updating [ResearchNotebooks] table.'
SET @lastId = 0;
WHILE @lastId = 0 OR EXISTS(SELECT 1 FROM @ids)
BEGIN
    DELETE FROM @ids;
    INSERT INTO @ids
        SELECT TOP(@batchSize) ProcessedDocumentId
        FROM ResearchNotebooks
        WHERE DocumentUri LIKE 'spworks://%' AND ProcessedDocumentId > @lastId
        ORDER BY ProcessedDocumentId;
 
    SELECT @lastId = MAX(Id) FROM @ids;
     
    UPDATE ResearchNotebooks
        SET DocumentUri = REPLACE(REPLACE(REPLACE(REPLACE(DocumentUri, '&id=&', '&'), '&subid=&', '&'), '&fid=&', '&'), '&fsubid=&', '&')
        WHERE ProcessedDocumentId IN (SELECT Id FROM @ids);
END
 
PRINT 'Crawl URLs have been updated.'