This is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.
Following on from last week’s post on OLE Attachments and Record Notes, I can now get to the reason for the interest in notes and OLE attachments.
The partner was trying to copy master records for Vendors from one company to a new company. They wanted to use SmartList to export the Vendor data out of the source company and then use Integration Manager to bring the data into the target company. The problem was how to handle the record notes that may be attached and how to handle the OLE attachments if they existed.
Below is an example T-SQL script for the PM_Vendor_MSTR (PM00200) table. The script will display the notes in the source company, then remove any notes from the target company that would cause the insert step to fail (it will not remove any notes that it will not be inserting back). It then inserts the notes mapping the Note Index and displays the results. The last two steps are to show the Note Index mapping and to generate the commands to copy any OLE Notes if they exist.
You will need to do a find and replace on the Source DB name (TWO) and the Target DB name (TEST) and also set the OLEPath variable to the value from the OLEPath Setting in the dex.ini for the last query.
SQL Code Example (for SQL 2005 and SQL 2008)
The last query uses an undocumented system function (available in SQL 2005 and SQL 2008) sys.fn_varbintohexstr() along with convert() and cast() to translate the Note Index into a hexadecimal string. We then use right() and upper() to get the attachment file name.
For SQL 2000, we will need to create our own version of the function called dbo.ufn_varbintohexstr() and adjust the final query to use the user defined function. The attachment includes a SQL 2000 version of the script.
Once the last query to create the commands to copy and rename the OLE Attachments has run you can either:
Use Save As to save as a file (csv or tab delimited). Rename the extension to .bat. Then edit the file with Notepad and use the save as option to save it again ensuring that the encoding option is set to ANSI; or
Use Select All and Copy to copy to the clipboard. Open Notepad and paste in the commands generated by the query. Save the file being sure to set the encoding for ANSI. You can either save it with a .bat extension or rename the extension afterwards to .bat.
The final step is to execute the batch file and this will copy the OLE Attachments that exist between the companies and rename the files at the same time.
An example of the commands produced by the last query is below:
if exist “C:Dyn1000DataNotesTWOOLENotes00003E4” copy “C:Dyn1000DataNotesTWOOLENotes00003E4” “C:Dyn1000DataNotesTESTOLENotes0000035”
The SQL Scripts for SQL 2000 and for SQL 2005/2008 are available as an attachment at the bottom of this post.
For other posts on the topic of Notes and OLE Attachments see the following posts:
- Understanding Notes and the Note Index field
- OLE Attachments and Record Notes
- All about the Dexterity OLE Container
- Fixing missing Note Index values
Let me know if you find this useful.
05-Oct-2009: Scripts updated to surround attachment paths with double quotes to handle when path contains spaces. Thanks to Robert Cavill for bringing this to my notice.
07-Oct-2009: Added SQL 2000 version of the scripts which creates user defined functions to handle the conversion to hexadecimal as a string.
02-Feb-2010: Added links to related posts.
05-Feb-2010: Added link to post on Fixing missing Note Index values.
This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.