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

Version 5

    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 scripts or queries were initially developed by Doug Castell with Castell Computers and were then 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 prior any modification!
    - 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.

    - All physical files which are related to the replaced database references must be moved manually, e.g. via copy/move the files externally within the Windows Explorer

    - When moving email attachments, it should bemade sure that the user's preferences for saving attachments are verified and also updated to the new location via Tools >> Options >> E-Mail >> More Options >> Retrieval >> Attachments directory settings (especially the path)

     

     

    - 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 which are E-mail attachments (they appear under the Links tab when using Tools >> Options >> E-Mail >> More Options >> Retrieval >> Save attachments as 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)+'%' AND (LINKACCT <> '')

     

    -- Linked Documents which are NO E-mail attachments

    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)+'%' AND (LINKACCT = '')

     

     

    -- 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= REPLACE(CONVERT(VARCHAR(MAX),CONVERT(VARBINARY(MAX),NOTES)) ,@SEARCH, @REPLACE)

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

     

     

     

    -- Opportunity / Project Detail links

    UPDATE opmgrfld

    SET linkeddoc = REPLACE(CAST(LINKEDDOC AS varchar(MAX)), @SEARCH , @REPLACE)

    FROM OPMGRFLD

    WHERE Rectype ='F' AND LINKEDDOC LIKE '%'+@SEARCH+'%'

     

     

     

     


    UPDATE INFOMINE SET
    LINKEDDOC=CAST(REPLACE(CAST(LINKEDDOC AS VARCHAR(MAX)) ,@SEARCH, @REPLACE) AS TEXT)
    WHERE LINKEDDOC LIKE '%'[email protected]+'%'

    UPDATE INFOMINE
    SET NOTES= REPLACE(CONVERT(VARCHAR(MAX),CONVERT(VARBINARY(MAX),NOTES)) ,@SEARCH, @REPLACE)
    WHERE CONVERT(VARCHAR(MAX),CONVERT(VARBINARY(MAX),NOTES)) LIKE '%'[email protected]+'%'