What happened to all of my GP OLE Notes?

During the migration of OLE Notes to the GP 2013 and above to the document attach functionality not all OLE Notes will get migrated through the Migration tool. After the GP OLE Notes migration tool has completed you will see a screen detailing the process and the number of unsuccessfully extracted notes. Through research from other GP OLE Notes blog, I was able to piece together a manual process to identify, extract and then attach the document within GP to the appropriate record.

GP OLE Notes 002

The Manual Process

Part One – Identifying the Record the OLE Note is associated with

All OLE Notes that weren’t successfully extracted by Microsoft’s migration utility will remain in the original extraction path.

1. To create a list of the files within the folder follow the directions in this Microsoft KB https://support.microsoft.com/en-us/kb/196158. After that I created an Admin database in SSMS on the SQL server and then imported the results within the txt file to a table called OLENotes_Missing.

2. I ran the following SQL script in SSMS to find all missing OLE Note names and associated Note Indexes and convert the HEX Note name.

SELECT NoteINDEX, DATE1, HEX_REMOVE as [OLENote Name]

FROM [ADMIN].[dbo].[OLENotes_Missing]

Order By DATE1 DESC

3. Take the Note Index from first SQL Script and run below SQL script in SSMS to find associated tables. Replace Note Index searching for into yellow highlighted area.

USE [<Production Database>]

DECLARE @noteindx nvarchar(20)

DECLARE @tablename nvarchar(20)

DECLARE @sqlstring nvarchar(MAX)

DECLARE @param nvarchar(100)

DECLARE @print nvarchar(100)

SET @noteindx = ‘9708539’ –Change to note index that you would like to find

DECLARE notecursor CURSOR FOR

SELECT c.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS c

LEFT OUTER JOIN INFORMATION_SCHEMA.TABLES t ON t.TABLE_NAME = c.TABLE_NAME

WHERE c.COLUMN_NAME = ‘NOTEINDX’ AND t.TABLE_TYPE = ‘BASE TABLE’

OPEN notecursor

FETCH NEXT FROM notecursor INTO @tablename

SET @sqlstring = N’SELECT ”’ + @tablename + ”’ AS TableName, NOTEINDX, DEX_ROW_ID FROM ‘ + @tablename + ‘ WHERE NOTEINDX = ‘ + @noteindx + CHAR(13);

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sqlstring += N’ UNION SELECT ”’ + @tablename + ”’ AS TableName, NOTEINDX, DEX_ROW_ID FROM ‘ + @tablename + ‘ WHERE NOTEINDX = ‘ + @noteindx + CHAR(13);

FETCH NEXT FROM notecursor INTO @tablename

END

CLOSE notecursor

DEALLOCATE notecursor

EXEC(@sqlstring)

4. Output from above script will look like below.

GP OLE Notes 004

5. Disregard TableName SY03900 and substitute the other TableName and DEX_ROW_ID into SQL script below and execute in SSMS. This will give you the record that the OLE Notes document will need to be attached to.

SELECT * from SVC00600 WHERE DEX_ROW_ID = 51259

GP OLE Notes 006

PART 2 – Document Extraction and Attachment

Using 7Zip you can manually extract the contains of the file.

1. Open 7Zip and navigate to original OLE Note extraction location.

2. Find Note Name you wish to open and then drill down into Embedding folder(s)

GP OLE Notes 008

3. Open CONTENTS file with Internet Explorer

GP OLE Notes 010

4. Print output to PDF

GP OLE Notes 012

5. Log into GP and find the record from SQL script from step 5 of Part 1 and then attached record using document attach functionality. Follow Steps 1 through 3.

GP OLE Notes

A very manual process, but worth the time if you need the documents within your GP system.