Previous versions of GoldMine, and Heat, store notes as Long Text fields. Now they are stored as Image fields. The trouble with both of these is that it’s not easy to read what’s in there using a query.

Here is a handy little script that will display the contents of a Long Text field. It needs to be targeted, using the ‘where’ clause, at just a single record.

This example brings out the body of an email from GoldMine’s mailbox table in an ‘old’ version of GoldMine, but the same principle holds for any table with long text fields.


use goldmine
DECLARE @data VarChar(max)
SELECT @data = rfc822 FROM mailbox WHERE mailref like '%this is a test%'
Print @data


What about Image fields though? The query above will fail if you’re using GM9, and a quick experiment with CAST or CONVERT in SQL will show that it’s not possible to directly translate from Image to VarChar.

They key is to convert the Image field to Binary, and then the Binary to VarChar, as here:


use goldmine
DECLARE @data VarChar(max)
SELECT @data = convert(varchar(max), convert(varbinary(max),rfc822)) FROM mailbox WHERE mailref like '%this is a test%'
Print @data


These queries won’t work from within GoldMine though; they have to be run in SQL itself.