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.
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. 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.
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
PART 2 – Document Extraction and Attachment
Using 7Zip you can manually extract the contents 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)
3. Open CONTENTS file with Internet Explorer
4. Print output to PDF
5. Log into GP and find the record from SQL script from step 5 of Part 1 and then attach record using document attach functionality. Follow Steps 1 through 3.
This is a very manual process, but worth the time and effort if you need the documents within your GP system.
BARRY CROWELL, MBA | BI/EDW Solution Architect
Barry uses his expertise to lead and teach a team of GP consultants to assure our solution scoping, architecting, and delivery meet and exceed the customer’s expectations. He helps clients analyze their current business processes and provides them with process improvements. He has performed implementations as the lead consultant and end-user project manager. Barry has over 20 years of experience working in accounting and the Microsoft Dynamics GP industry. He also possesses a bachelor’s degree in accounting and business administration from Black Hills State University and a master’s in business administration from La Salle University.
