Scripts to be used to move linked documents, email attachments etc. - Linked Document Mover scripts

Version 1

    Details

    Scripts to be used to move linked documents, email attachments etc. - Linked Document Mover scripts


    If Linked Document Mover feature within GoldMine cannot be used due to any valid reason, the below scripts can be provided to the GoldMine Administrator, Partner or Microsoft SQL Server DBA.

     

    - These script or queries were initially available on the original virtual community platform and provided by partners and amended by GoldMine Support Members over several years.

     

    - These scripts are getting the task done - for example for Email Attachments and/or Linked Documents and/or Reports etc. - via Microsoft SQL backend update queries and the REPLACE  function of Microsoft SQL Server.

     

    Please keep in mind that any provided scripts are not officially supported by GoldMine Support  Team and using these scripts is solely at own risk. It is absolutely necessary to make sure to have proper and full running backups prior applying the scripts.


    Resolution

    Please find below the mentioned script which is provided as-is and without any kind of warranty

     


    The customer should make sure that
    - There is a full running backup of the GoldMine database available
    -  The process is preferably performed when no one is working within  GoldMine (it prevents current changes or access issues to attachments  data but also as it is highly recommended afterward to test and verify  that work is not lost in case when a backup needs to be restored)
    -  That after the process sporadically some attachments are tested (if the  users use also to link attachments as Links, this applies also this section)
    - The search and replace values need to be as specific as  necessary and as generic as possible, for example only changing C:\ as  Search value and  D:\ as Replace value may result in much more changes  than desired while C:\programdata\GoldMine\mailbox\master\2016\01 as  Search value and 
    D:\Apps\GoldMine\mailbox\master\2016\01 might result in only a few changes while more changes would be expected.

     

    - The script needs to be run directly from the SQL Server Management Studio

     

     

    - Open the SQL Server Management Studio
    1. Browse to the GoldMine database
    2. Button New Query
    3. Make sure that the database drop down is set to the GoldMine database, if not already set
    4. Copy and paste and adjust only the part of the script you will need, e.g. only Email attachments or only Linked documents the below script into the query pane and adjust the @SEARCH and the @REPLACE value in

    SET @SEARCH = 'C:\GoldMine'
    SET @REPLACE = 'D:\Apps\GoldMine'

     

    5. Execute the query
    6. Verify the result in GoldMine

     

     



    DECLARE @SEARCH AS VARCHAR(100)

     

    DECLARE @REPLACE AS VARCHAR(100)

     

     

     

    -- Give here want you want to search and replace

     

    SET @SEARCH = 'C:\GoldMine'

     

    SET @REPLACE = 'D:\Apps\GoldMine'

     

     

     

    -- E-mail Attachments for GoldMine 8.5 and higher

     

    UPDATE MAILBOX

     

    SET RFC822= REPLACE(CONVERT(VARCHAR(MAX),CONVERT(VARBINARY(MAX),RFC822)) ,@SEARCH, @REPLACE)

     

    WHERE CONVERT(VARCHAR(MAX),CONVERT(VARBINARY(MAX),RFC822)) LIKE '%'+@SEARCH+'%'

     

     

     

    -- Linked Documents

     

    UPDATE CONTSUPP SET

     

    ADDRESS1 = LEFT( REPLACE( RTRIM(ISNULL(ADDRESS1,'')+ISNULL(ADDRESS2,'')) + SPACE(41) , @SEARCH, @REPLACE) , 40 ) ,

     

    U_ADDRESS1 = UPPER( LEFT( REPLACE( RTRIM(ISNULL(ADDRESS1,'')+ISNULL(ADDRESS2,'')) + SPACE(41) , @SEARCH, @REPLACE) , 40 ) ) ,

     

    ADDRESS2 = SUBSTRING ( REPLACE( RTRIM(ISNULL(ADDRESS1,'')+ISNULL(ADDRESS2,'')) + SPACE(41) , @SEARCH, @REPLACE) , 41 , 40 ) ,

     

    LINKEDDOC=CAST(REPLACE(CAST(LINKEDDOC AS VARCHAR(MAX)) ,@SEARCH, @REPLACE) AS TEXT)

     

    WHERE RECTYPE='L'

     

    AND U_ADDRESS1 LIKE '%'+LEFT(@SEARCH,40)+'%'

     

     

     

    -- Word Templates

     

    UPDATE FORMS SET

     

    TEMPLATE = REPLACE (TEMPLATE,@SEARCH, @REPLACE) ,

     

    LINKEDDOC=CAST(REPLACE(CAST(LINKEDDOC AS VARCHAR(MAX)) ,@SEARCH, @REPLACE) AS TEXT)

     

    WHERE LINKEDDOC LIKE '%'+@SEARCH+'%'

     

     


     

     

    -- Reports

     

    UPDATE REPORT32 SET

     

    LINKEDDOC=CAST(REPLACE(CAST(LINKEDDOC AS VARCHAR(MAX)) ,@SEARCH, @REPLACE) AS TEXT)

     

    WHERE LINKEDDOC LIKE '%'+@SEARCH+'%'

     

     

     

    -- Knowledge Base documents

     

    UPDATE INFOMINE SET

     

    LINKEDDOC=CAST(REPLACE(CAST(LINKEDDOC AS VARCHAR(MAX)) ,@SEARCH, @REPLACE) AS TEXT)

     

    WHERE LINKEDDOC LIKE '%'+@SEARCH+'%'

     

     

     

    UPDATE INFOMINE SET

     

    NOTES=CAST(REPLACE(CAST(NOTES AS VARCHAR(MAX)) ,@SEARCH, @REPLACE) AS TEXT)

     

    WHERE NOTES LIKE '%'+@SEARCH+'%'

     

     


    -- Opportunity / Project Detail links
    UPDATE opmgrfld
    SET linkeddoc = REPLACE(CAST(linkeddoc AS varchar(MAX)), 'string to be replaced:', 'string which should appear afterwards')
    FROM OPMGRFLD
    WHERE Rectype ='F'