Upgrade Toolkit 2009 to 2015 and Outlook GUID fields

Mauddib

Hi all, Well some time ago I posted that I was going to try to upgrade from 5 to 2015, and we got to 2009 and coverted everyone to RTC and are now preparing the jump to 2015.

As part of this I was reading all the code in the Upgrade codeunits that you run before and after converting the database to 2015. It all looks good, and I commented out a LOT of code we do not need and optimised some others.

But the one thing worrying me is the changes to the Attachment Table, Interaction Log Entry and so forth.

The Upgrade Codeunit you run in 2009 basically does a CLEAR on all the fields that link to the emails in the Outlook Exchange Server. All the GUID fields are just cleared.

Since we use Email logging HEAVILY in our company this worries me. What is happening here? Am I basically going to lose every connection between our interaction log entries / attachments and the emails they connect to? How can I preserve this? What is the next step here?

Everything else in the upgrade process looks good so far, but this bit is a real concern for me.

trso

have you found a solution??

we have upgraded a customer to test, and haven't found a solution for the emails in the attachment table yet.

Mauddib

I have found nothing yet, nor on the web or Partner Source. Perhaps I am just stupid and I have missed something in the documentation somewhere, but I am not sure.

It seems to me simply that the functions in CU-104045 of the Upgrade toolkit simply clear the fields connecting the Attachment and Interaction Log Entries to Emails in the Outlook server.

The connection between Navision Entries and these emails is therefore simply gone. But it is hard to imagine this was simply over looked in the Data Migration tool - so I can only assume it is me that is missing something obvious.

Mauddib

Problem solved. I will detail my solution here in order that the next person doing an upgrade in a company that uses Email Logging with Outlook can save the 6 days I lost on it!!!

BACKGROUND OF ISSUE

When opening a "logged" email in Navision.... Navision uses the Attachment table and the "Store Pointer ID" and "Entry Pointer ID" and so on to get the emails in 2009.

In 2015 there are three NEW fields in the Attachment Table and all the old fields are deleted. The new fields are "Email Message ID" "Email Entry ID" "Email Message Checksum". However the only one required to open the emails from your OLD data seems to be "Email Entry ID". What the other fields are for I do not know and have not yet checked.

When using the UPGRADE TOOLKIT in 2009 however the old data is deleted and nothing done with it. You simply lose it completely! An error or oversight from the creators of the toolkit I fear?

SOLUTION I USED

I logged a few emails in the 2015 system and 2009 system. I used STREAMS to output the BLOB fields in the Attachment tables of both systems to a .TXT file so I could see them. To my eye the values in the 2009 system looked like HEX while the values in the 2015 system looked like BASE64.

So I decided this might be the solution. I followed the following procedure:

1) In 2009 Attachment table I created two Text fields on the table and called them OLDFIELD NEWFIELD.

2) I ran a codeunit that using STREAMS populated OLDFIELD with the contents of the BLOB field "Entry Pointer ID"

3) In SQL directly I then ran a view using CAST() commands to convert the HEX in OLDFIELD into BASE64 and place it in NEWFIELD

4) I performed my usual upgrade from 2009 to 2015.

5) In 2015 I then populated (again using STREAM in a codeunit in Navision) the new field "Email Entry ID" with the contents of NEWFIELD.

6) I deleted NEWFIELD and OLDFIELD from my attachment table and restored the standard 2015 version of this table.

RESULT

I can now open successully all the emails that were LOGGED in navision in Versions 5 and 6 historically.

Mauddib

For interest also, the SQL I used for the conversion I include below:

create view v as
 select [No_], cast('' as xml).value('xs:hexBinary(sql:column("[dbo].[MyCompany$Attachment].[OldField]"))', 'varbinary(max)') AS TTT
 FROM [dbo].[MyCompany$Attachment] GO

  update [MyCompany$Attachment]
  set [NewField] = cast(''as xml).value('xs:base64Binary(sql:column("v.TTT"))', 'varchar(max)')
  from [MyCompany$Attachment] JOIN v ON [MyCompany$Attachment].[No_] = v.[No_] 
GO

drop view v
GO

Arndt_Loermann

one Question:
what version of Exchange Server is in use for this Scenario ? Old Version -> new Version of Exchange 2007, 2010, 2013?
Thanks in advance