GoldMine: Remove NULL String after Rehost

When rehosting GoldMine from version 6.7 to 9.0.0.102 (a required intermediate step on the way to a later version), the rehost process inserts the string “NULL” into the Notes of history and calendar records that should be empty:

This fairly minor issue can be corrected with some SQL search and replace commands.

Important Notes

• Using SQL search and replace on your database is an advanced method that is probably not supported.
• Any changes you make will not sync to other sites or undocked users. You would need at a minimum to re-sync the entire database. I did not test this as I am no longer using sync.
• Be sure to back up your SQL databases before running these scripts.
• Use at your own risk!

Find the NULL Strings

Notes are kept as SQL Image data so it’s a bit tricky to figure out what the NULL string looks like. This query should give you a few examples:

select top 5 AccountNo, Notes, cast(cast(notes as varbinary(max)) as varchar(max)) as TextNotes
from ContHist
where cast(cast(notes as varbinary(max)) as varchar(max)) like 'NULL%'

From those results, we can see that the hex value for “NULL” is 0x4E554C4C00. Use the AccountNo to find an example in the GoldMine UI.

This query will count how many occurrences of that string are in ContHist:

select count(*)
from ContHist
where cast(notes as varbinary(max)) = 0x4E554C4C00 -- the binary for a string = "NULL"

Note down the count. Run the same query against the Cal table to count occurrences in the calendar. Note that if you have multiple databases, Cal will only exist in the one that contains your GMBase data.

Determine What the Value Should Be

So what should the “NULL” value be?

1. To keep things simple, temporarily set GoldMine to use plain-text notes (Tools > Configure > System Settings > Display Tab).

2. Restart GoldMine.

3. Add a history item with the following string in the Reference field: Test with empty notes%”. Leave the Notes empty.

4. Run the following query to find that test and check the value of Notes:

select AccountNo, Ref, Notes, cast(cast(notes as varbinary(max)) as varchar(max)) as TextNotes
from ContHist
where Ref like 'Test with empty notes%'

From this we can determine that the correct value is 0x00. This is, I believe, simply an indication that the variable-length multibyte field has 0 characters.

5. Change GoldMine back to HTML notes (if that’s what you use) and re-start GoldMine.

Replace the Data

So far we’ve just been reading data. Now we’re ready to change it. Re-read the “Important Notes” above. Did you back up your databases?

Close GoldMine.

Run this query to replace the “NULL” string in ContHist.Notes with hex 0x00:

update ContHist
set Notes = 0x00
where cast(notes as varbinary(max)) = 0x4E554C4C00

The update count should match the count you retrieved above. Run the same query against the Cal table to update Cal.Notes.

Check your sample record. The “NULL” string should be gone: