1

Updating Attachment and Document Paths when moving GoldMine

Posted by

GoldMine doesn’t store files inside it’s database. Instead, the location of the file is stored as a drive letter/network path. This approach is taken in many areas of the program: e-mail attachments, linked documents, Word/Excel forms, reports, case/project/opportunity details, and more.

This practice has pluses and minuses that are, of course, endlessly debatable. One of the minuses is that if you get a new server, you’ll either need to give it the same name as your old server or you’ll have to somehow handle updating those stored paths. There’s a utility within GoldMine, itself, (tools|data management|move linked docs and attachments) but it doesn’t cover everything.

If you know me, you know that I tend to solve data problems with SQL. Over the years, I found that the GoldMine-provided utility (and numerous 3rd party at tools to handle this) falls short in various ways. Instead, I’ve developed a script that does the trick — and handles as many of the areas where one might store a path/filename as I could think of.


DECLARE @SEARCH AS VARCHAR(100)
DECLARE @REPLACE AS VARCHAR(100)

-- Indicate here what you want to search and replace
SET @SEARCH = '\\oldserver\apps\GoldMine'
SET @REPLACE = '\\newserver\apps\GoldMine'

-- E-mail Attachments for all before GoldMine 8.5. comment out this section is you are on version 8.5 or later!
--UPDATE MAILBOX
--SET RFC822= CAST(REPLACE(CAST(RFC822 AS VARCHAR(MAX)) ,@SEARCH, @REPLACE) AS TEXT)
--WHERE RFC822 LIKE '%'+@SEARCH+'%'
　
-- E-mail Attachments for GoldMine 8.5 and higher. comment out this section is you are on version 8.0 or earlier!
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/Excel 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+'%'

-- Report layouts/previews
UPDATE REPORT32 SET
LINKEDDOC=CAST(REPLACE(CAST(LINKEDDOC AS VARCHAR(MAX)) ,@SEARCH, @REPLACE) AS TEXT)
WHERE LINKEDDOC LIKE '%'+@SEARCH+'%'

-- Knowledge Base attachments
UPDATE INFOMINE SET
LINKEDDOC=CAST(REPLACE(CAST(LINKEDDOC AS VARCHAR(MAX)) ,@SEARCH, @REPLACE) AS TEXT)
WHERE LINKEDDOC LIKE '%'+@SEARCH+'%'

-- AP Actions (such as steps executing an external app)
UPDATE TRACKS SET
UPDTEXPR=CAST(REPLACE(CAST(UPDTEXPR AS VARCHAR(MAX)) ,@SEARCH, @REPLACE) AS TEXT)
WHERE UPDTEXPR LIKE '%'+@SEARCH+'%'

-- Opportunity details/links
UPDATE opmgrfld SET
LINKEDDOC=CAST(REPLACE(CAST(LINKEDDOC AS VARCHAR(MAX)) ,@SEARCH, @REPLACE) AS TEXT)
WHERE RECTYPE='F'
AND CAST(LINKEDDOC AS VARCHAR(MAX)) LIKE '%'+@SEARCH+'%'

-- CASE Attachments
UPDATE CaseAttachment SET
LOCATION=REPLACE(LOCATION,@SEARCH, @REPLACE)
WHERE RECTYPE=0
AND LOCATION LIKE '%'+@SEARCH+'%'

I did not build this script entirely on my own. The initial bones of the search/replace script were found in some random StackExchange thread, if I recall correctly. Various GoldMine community members (Shaul Bel and Paul Laufer come immediately to mind) have contributed edits/additions/discussion that proved invaluable along the way.

Heck, if YOU are reading this article, maybe you have some suggestions to improve it. Please pipe up in the comments, below! I’d love to hear feedback from actual admins out there!

Related Posts

One thought on “Updating Attachment and Document Paths when moving GoldMine”

  1. Doug Castell

    It’s probably worth noting that this script has evolved over the years to the following:

    DECLARE @SEARCH AS VARCHAR(100)
    DECLARE @REPLACE AS VARCHAR(100)
    — Indicate here want you want to search and replace
    SET @SEARCH = ‘\\oldserver\apps\GoldMine’
    SET @REPLACE = ‘\\newserver\apps\GoldMine’
    — E-mail Attachments for all before GoldMine 8.5. comment out this section is you are on version 8.5 or later!
    –UPDATE MAILBOX
    –SET RFC822= CAST(REPLACE(CAST(RFC822 AS VARCHAR(MAX)) ,@SEARCH, @REPLACE) AS TEXT)
    –WHERE RFC822 LIKE ‘%’+@SEARCH+’%’
    　
    — E-mail Attachments for GoldMine 8.5 and higher. comment out this section is you are on version 8.0 or earlier!
    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 = right(rtrim(ltrim(REPLACE (TEMPLATE,@SEARCH, @REPLACE))),80) ,
    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+’%’
    — AP Actions
    UPDATE TRACKS SET
    UPDTEXPR=CAST(REPLACE(CAST(UPDTEXPR AS VARCHAR(MAX)) ,@SEARCH, @REPLACE) AS TEXT)
    WHERE UPDTEXPR LIKE ‘%’+@SEARCH+’%’
    — Opportunity details/links
    UPDATE opmgrfld SET
    LINKEDDOC=CAST(REPLACE(CAST(LINKEDDOC AS VARCHAR(MAX)) ,@SEARCH, @REPLACE) AS TEXT)
    WHERE RECTYPE=’F’
    AND CAST(LINKEDDOC AS VARCHAR(MAX)) LIKE ‘%’+@SEARCH+’%’
    — CASE Attachments
    UPDATE CaseAttachment SET
    LOCATION=REPLACE(LOCATION,@SEARCH, @REPLACE)
    WHERE RECTYPE=0
    AND LOCATION LIKE ‘%’+@SEARCH+’%’

Leave a Reply