Goldmine Query for Primary Email

SELECT contact1.ACCOUNTNO AS CustomerID, contact1.contact AS Contact, COALESCE(em.contsupref, '')+COALESCE(em.address1, '') AS Email, em.mergecodes
FROM contact1
 JOIN contsupp em on contact1.accountno=em.accountno
WHERE em.contact='E-Mail Address'
 AND em.rectype LIKE 'P'
 AND em.zip LIKE '_1%'

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! 

Strip HTML from SQL Columns

Just came across this, great info!

http://blog.sqlauthority.com/2007/06/16/sql-server-udf-user-defined-function-to-strip-html-parse-html-no-regular-expression/


Goldmin Use:
What I did was create a view converting the image fields in SQL to varchar, then when exporting the query I ran the udf_stripHTML(column) function so when the data is being exported to excel the HTML is coming off then.

SQL Row Count for All Tables

There have been times I want to see how many rows are in each table of a database without going into each table and running a query to see the row count. This returns a list of all tables with more than 2 rows of data and the row count for each table.

SELECT OBJECT_NAME(OBJECT_ID) TableName, st.row_count
FROM sys.dm_db_partition_stats st
WHERE index_id < 2
ORDER BY st.row_count DESC

Remove HTML Tags Using Find and Replace

Here is a quick and easy way to remove HTML tags completely by using the Find and Replace function in Microsoft.

Put this in the find box: \<*\>
Leave the Replace box Empty
Check the 'Use Wildcard' box
Replace All!

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

SQL Query that Excludes Null's and Empty Values

select accountno, notes
from contact1
where notes is not null and datalength(notes) > 0