Goldmine SQL Query to Filter on Date

Below is a basic query that will output the results of all ContHist records from 1/1/2009 and forward.

select * from CONTHIST where ondate > '2009-01-01'

Query for Exporting from Goldmine CAL table to Excel

Below are two queries that can be used to extract from the CAL table in a Goldmine SQL database.

If the Notes and RFC822 fields are in a Blob format where their values are not standard text use this:


Select
c.USERID, c.ACCOUNTNO, c.RECTYPE, c.ONDATE, c.[ONTIME], c.[DURATION], c.REF as [Subject History - REF],
c.LINKRECID, c.recid,
CAST(CAST(c.NOTES AS varbinary(4000)) AS varchar(4000)) AS NOTES,
CAST(CAST(m.RFC822 AS varbinary(4000)) AS varchar(4000)) AS RFC822

From CAL as c
Left Join MAILBOX as m
ON c.LINKRECID = m.recid


If the Notes and RFC822 fields are in a standard text format you can use this query:



set textsize 4000

Select
c.USERID, c.ACCOUNTNO, c.RECTYPE, c.ONDATE, c.[ONTIME], c.[DURATION], c.REF as [Subject History - REF],
c.LINKRECID, c.recid, c.NOTES, m.RFC822

From CAL as c
Left Join MAILBOX as m
ON c.LINKRECID = m.recid

Goldmine - Exporting History and Email Bodies in One Step

STEP 1
First create a view in SQL that will do a left join of the ContHist and Mailbox tables. This means that it will pull in every single ContHist record and only pull in information from the Mailbox table where there is a match on the field they are joined on.

NOTE: I am only including the columns that I typically use in a migration, there are more ContHist and Mailbox columns to be aware of. Also I am only pulling the first 4,000 characters of both the notes and mail messages because of the limitations of Excel. As part of this view we are converting both Notes (on ContHist) and RFC822 (on Mailbox) from blob to varchar(4000).

SQL QUERY 1


GO
CREATE VIEW [dbo].[HistoryANDMail]
AS

Select

c.USERID, c.ACCOUNTNO, c.RECTYPE, c.ONDATE, c.REF as [Subject History - REF],
CAST(CAST(c.NOTES AS varbinary(4000)) AS varchar(4000)) AS NOTES,
c.LINKRECID, c.recid,

CAST(CAST(m.RFC822 AS varbinary(4000)) AS varchar(4000)) AS RFC822

From Conthist as c
Left Join MAILBOX as m
ON c.LINKRECID = m.recid
WHERE c.ACCOUNTNO is not null and datalength(c.ACCOUNTNO)>0

END SQL QUERY 1


STEP 2
Now you have a view that contains all ContHist data converted and ready to be exported to excel. I like to try to strip the HTML before going into excel because they excel files can be very large and hard to deal with. Here is the process for this. Following a great blog post you can create a function that removes HTML from specific columns.

Essentially copy and paste this into a query and run it, this will create a user defined function we will use later.

SQL QUERY 2


CREATE FUNCTION [dbo].[udf_StripHTML]
(@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Start INT
DECLARE @End INT
DECLARE @Length INT
SET @Start = CHARINDEX('<',@HTMLText)
SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
SET @Length = (@End - @Start) + 1
WHILE @Start > 0
AND @End > 0
AND @Length > 0
BEGIN
SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'')
SET @Start = CHARINDEX('<',@HTMLText)
SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
SET @Length = (@End - @Start) + 1
END
RETURN LTRIM(RTRIM(@HTMLText))
END
GO

END SQL QUERY 2

STEP 3
Now we need to run an export task sending a query we will write into an excel file. Right click on the database > tasks > export data... When it comes time for the query enter the following.

NOTE: this has to be done in sets of 50,000 due to the size limitation of a spreadsheet in excel 2007 and I was unable to run this on a spreadsheet using 2010.



SQL QUERY 3


SELECT

USERID, ACCOUNTNO, RECTYPE, ONDATE, [Subject History - REF], 
dbo.udf_stripHTML(NOTES) as NOTES, LINKRECID, recid, dbo.udf_stripHTML(RFC822) as RFC822

from 

( SELECT
    ROW_NUMBER() OVER (ORDER BY recid ASC) AS ROW_NUMBER,
    *
  FROM dbo.FINALHistoryANDMail
) foo
WHERE ROW_NUMBER <= 50000


END SQL QUERY 3


Hope this helps! 

Exporting Emails from Goldmine

Converting companies from Goldmine to Salesforce.com presents a number of difficulties, one of which was exporting emails.

Because files must be in CSV format in order to import to Salesforce.com we need to write a query that will take the data stored int he Mailbox.dfb table and export it to excel. Below are the steps I went through recently to accomplish this.

1. The query used to create a view that converts the RFC822 image column to a text field.

GO
CREATE VIEW [dbo].[vMailbox]
AS
SELECT     LINKRECID, FLAGS, USERID, FOLDER, FOLDER2, ACCOUNTNO, CREATEON, MAILSIZE, MAILDATE, MAILTIME, MAILREF, LOPRECID, MAILID, EXT,
                      CAST(CAST(RFC822 AS varbinary(MAX)) AS varchar(4000)) AS RFC822, recid
FROM         dbo.MAILBOX

NOTE: I had to insert 'varchar(4000)' instead of varchar(max) because when I didn't put a cap on the size of the email messages they would be too large and produce errors when exporting to excel. This limits the email message size to 4,000 characters, but that's better than nothing!

2. Export this new view to an excel document.

• Right click on the database > Tasks > Export Data...
• Choose a Data Source:
• Next
• Choose a Destination: 
• Destination: Microsoft Excel
• Browse: [Give your files a name and destination]
• Excel Version: Microsoft Excel 97-2003 (I have had problems trying this with 2007)
• Next
• Write a query to specify the data to transfer
• Copy and paste the query below into the text area (this pulls data from the view created)
• SELECT LINKRECID, FLAGS, USERID, FOLDER, FOLDER2, ACCOUNTNO, CREATEON, MAILSIZE, MAILDATE, MAILTIME, MAILREF, LOPRECID, MAILID, EXT, CAST(CAST(RFC822 AS varbinary(MAX)) AS varchar(4000)) AS RFC822, recid FROM dbo.vMAILBOX
• Next
• Select Source Tables and Views
• Next
• Review Data Type and Mapping
• Next
• Save and Run Package
• Check 'Run Immediately'
• Finish >>|
• Complete the Wizard
• Finish

If the number of Mailbox files is in excess of 65,000 you may need to pull the data out in batches of 50,000 or 60,000 because excel 97-2003 will allow a maximum of a little over 65,000 rows in a file.Refer to my previous post about limiting the size of an output for more details. http://cloudrevelations.blogspot.com/2012/04/limit-results-in-sql-server-using.html