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)
-- Display Source Notes
-- Delete notes from Target company if they exist
-- Copy Notes from Source Company to Target Company Mapping Note Index
-- Display copied Notes
-- Select Statement showing mapping of Note Index and Hexadecimal Note Index
-- Generate Copy Commands for OLE Note attachement files
Notes:
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.
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:
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:
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
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.
David
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.
Post from Mohammad Daoud
http://mohdaoud.blogspot.com/2009/10/copying-ole-notes.html
Posting from DynamicAccounting.net
http://msdynamicsgp.blogspot.com/2009/10/copying-record-notes-and-ole.html
Thanks David for this article. I used it today to copy Item Notes from one company to another. The instructions were very clear and worked perfectly. Trying to import the notes through Integration Manager kept truncating the text at 364 characters.
PLEASE READ BEFORE POSTING
Please only post comments relating to the topic of this page.
If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.