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!
