The paths to linked documents and infocentre attachments are pretty easy to find in a GoldMine database, but email attachments, if not saved under the Links tab, can be a nightmare to needle out as the attachment path is buried deep in the rfc822 long text field.

The following query will list all the attachment paths in alphabetical order, with a count of attachments in each.

Replace ‘goldmine’ with the name of your database.


use goldmine

DECLARE @string varchar(300)
DECLARE @rstring varchar(300)
DECLARE @output varchar(300)
DECLARE @start smallint
DECLARE @end smallint
DECLARE @results table(path varchar(300))

DECLARE read_cursor CURSOR
FOR SELECT substring(rfc822,charindex('Content-Disposition: attachment; filename',rfc822),300)
FROM mailbox WHERE charindex('Content-Disposition: attachment; filename',rfc822)>0

open read_cursor
fetch next from read_cursor into @string

while @@fetch_status=0
begin
set @start=charindex('Encoding: base64',@string)+20
set @end=charindex('--',@string,@start)
set @rstring=reverse(substring(@string,@start,@end-@start))
set @output=(reverse(substring(@rstring,charindex('',@rstring),300)))
insert into @results values(@output)
fetch next from read_cursor into @string
end

CLOSE read_cursor
DEALLOCATE read_cursor

select path, count(path) from @results group by path order by path


It uses a cursor, which SQL purists will doubtless castigate me for. Still, it works, and that’s all that matters!