Copying Record Notes and OLE Attachments between Companies
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
select SM.VENDORID, SN.NOTEINDX, SN.DATE1, SN.TIME1, SN.TXTFIELD
from TWO.dbo.SY03900 SN
join TWO.dbo.PM00200 SM on SM.NOTEINDX = SN.NOTEINDX
order by SM.VENDORID
-- Delete notes from Target company if they exist
delete from TN
--select TN.NOTEINDX, TN.DATE1, TN.TIME1, TN.TXTFIELD
from TWO.dbo.SY03900 SN
join TWO.dbo.PM00200 SM on SM.NOTEINDX = SN.NOTEINDX
join TEST.dbo.PM00200 TM on TM.VENDORID = SM.VENDORID
join TEST.dbo.SY03900 TN on TN.NOTEINDX = TM.NOTEINDX
-- Copy Notes from Source Company to Target Company Mapping Note Index
insert TEST.dbo.SY03900
select TM.NOTEINDX, SN.DATE1, SN.TIME1, SN.TXTFIELD
from TWO.dbo.SY03900 SN
join TWO.dbo.PM00200 SM on SM.NOTEINDX = SN.NOTEINDX
join TEST.dbo.PM00200 TM on TM.VENDORID = SM.VENDORID
-- Display copied Notes
select TM.VENDORID, TN.NOTEINDX, TN.DATE1, TN.TIME1, TN.TXTFIELD
from TEST.dbo.SY03900 TN
join TEST.dbo.PM00200 TM on TM.NOTEINDX = TN.NOTEINDX
order by TM.VENDORID
-- Select Statement showing mapping of Note Index and Hexadecimal Note Index
select SM.VENDORID, SN.NOTEINDX, convert(binary(4),cast(SN.NOTEINDX as integer)) as OLEPATH
, TM.VENDORID, TN.NOTEINDX, convert(binary(4),cast(TN.NOTEINDX as integer)) as OLEPATH
from TWO.dbo.SY03900 SN
join TWO.dbo.PM00200 SM on SM.NOTEINDX = SN.NOTEINDX
join TEST.dbo.PM00200 TM on TM.VENDORID = SM.VENDORID
join TEST.dbo.SY03900 TN on TN.NOTEINDX = TM.NOTEINDX
-- Generate Copy Commands for OLE Note attachement files
declare @OLEPath char(100), @SCompany char(5), @TCompany char(5);
select @OLEPath = 'C:\Dyn1000\Data\Notes\', @SCompany = 'TWO', @TCompany = 'TEST'
select 'if exist '
+ '"' + rtrim(@OLEPath) + rtrim(@SCompany) + '\OLENotes\'
+ upper(right(sys.fn_varbintohexstr(convert(binary(4),cast(SN.NOTEINDX as integer))),8)) + '" '
+ 'copy '
+ '"' + rtrim(@OLEPath) + rtrim(@SCompany) + '\OLENotes\'
+ upper(right(sys.fn_varbintohexstr(convert(binary(4),cast(SN.NOTEINDX as integer))),8)) + '" '
+ '"' + rtrim(@OLEPath) + rtrim(@TCompany) + '\OLENotes\'
+ upper(right(sys.fn_varbintohexstr(convert(binary(4),cast(TN.NOTEINDX as integer))),8)) + '" '
as COMMAND
from TWO.dbo.SY03900 SN
join TWO.dbo.PM00200 SM on SM.NOTEINDX = SN.NOTEINDX
join TEST.dbo.PM00200 TM on TM.VENDORID = SM.VENDORID
join TEST.dbo.SY03900 TN on TN.NOTEINDX = TM.NOTEINDX
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.
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:
Example commands to copy OLE attachments
if exist "C:\Dyn1000\Data\Notes\TWO\OLENotes\000003B0" copy "C:\Dyn1000\Data\Notes\TWO\OLENotes\000003B0" "C:\Dyn1000\Data\Notes\TEST\OLENotes\00000033"
if exist "C:\Dyn1000\Data\Notes\TWO\OLENotes\000003E4" copy "C:\Dyn1000\Data\Notes\TWO\OLENotes\000003E4" "C:\Dyn1000\Data\Notes\TEST\OLENotes\00000035"
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
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.