We need to export Sametime Log data from Domino 8.5.3 to MS SQL Server version 2012 using LEI 8.5.3. Each document has a rich text field which contains formatted text and often inline images.
It is essential that the format is retained and images remain in their original position. I have used a third party product to export the Notes rich text data to html,
then replaced the <img src> in the html ouptut with the file contents encoded as a Base64 string.
LEI Connections are defined for Text and OLE DB. The LEI Direct Transfer Activity works fine for documents containing no or very small images, but with large images
(which can be several hundred Kb in size) an error is reported:
22/08/2016 08:46:24 AM WARNING: Unable to set ChatStart for [Input Record 78], field exceeds record length (32766), Connector 'Sametime Text', Method -Fetch-
This error occurs for every column in every document After the one with the large image(s), suggesting that the very long Base64 string has been 'wrapped' to a new line within the Activity.
The source data is in the format below, using "^" as the field separator, with the Body html exported as the last column. The Base64-encoded string can run for pages if the image is large...
There are Definitely no line feeds/carriage returns in the source data, although I've added some below so that it doesn't all disappear off the right of the screen.
0003d552-fd5e-4dc7-9504-33814898354e^23/04/2014 14:35:26^23/04/2014 14:42:53^Ann Other;John Person^2^2^18^<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html lang="en">
<!-- Midas Rich Text LSX Version 4.31, 08/22/2016 --><head><title></title><meta http-equiv="Content-Style-Type" content="text/css" /><style type="text/css">div.pabid2 {margin-left: 2.40in; text-indent: -2.40in; }
</style></head><body text='#000000' bgcolor='#ffffff'><div class="pabid2"><span style="font-size: 9pt; color: rgb(128,128,128); ">02:35:26 pm: </span><span style="font-size: 9pt; ">
Ann Other:</span><span style="font-size: 9pt; color: rgb(128,128,128); "><i> (Joined chat)</i></span></div><div class="pabid2">
<span style="font-size: 9pt; color: rgb(128,128,128); ">02:35:26 pm: </span><span style="font-size: 9pt; color: rgb(255,0,0); "> John Person:
</span><span style="font-size: 9pt; color: rgb(128,128,128); "><i> (Joined chat) </i></span></div><div class="pabid2"><span style="font-size: 9pt; color: rgb(128,128,128); ">
02:35:26 pm: </span><span style="font-size: 9pt; "> Ann Other: </span><span style="font-size: 9pt; font-family: Segoe UI;
">yep we can do that, I'll just confirm with Sue tomorrow - we gave them a week </span><img src="data:image/gif;base64,
R0lGODlhEgASAOZ3AP/mI//mIv/jH//kIPC2AP/kH/fLAMrKyoB+efzZEffMAPvXDf/iHWhfTPvYD//kIeyrAH99ePC3AP7gG/bJAO+xAKF+JuecAPrTCP7hHemiAIlsMPvXDvK9APjMAPfKAO+yAP3dF/3fGX9+eIlpL9iUBvrUCu3HC4VzMLd
/F/bIAOe1Bf7iHvbKAPTDAGhgTNqRBcSeF4BkMN2TBd6WBvK/AINrMI53L/HKC9+ZBuSmBYhpMOy+Buq4BfvZEGlhTPjOAv7hHP3eGI97L7R9GGpiTL6eGPzbE/rSB7iEF/zbFO3BBpxxJvPCAP7jH//iHuyqAO2uAI97MPPBAOG
jBbaDGOq+BmhhTP7iHdiSBsSjF+vABvO/AIpvMIFpMI1zMIloMI11MLyYGI56MP/nI/C4APTCAKeOJvvaEf7gGv3cFv7fGf7fGv/lIaSkpISEhMvLy25ubpiYmNbW1v/lIv//mTMzmf///
wAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACH/C05FVFNDQVBFMi4wAwEAAAAh+QQFZAB3ACwAAAAAEgASAAAH/4B3goJwbm9xcW9ucIONd3NyV2NGSzxiYS9yc44HCCgnCwkJHEgfKzYIB4NzEWcLamwMDBMhDh4dF
iObd3JSC2t2dgIDA8EOBhJdcndwRTghdnR0Aw9tD3R2GGY6DYVDCxMCdXZtbdh1GT4UFRuGWgkM4tEB5wVHBgRJh1vwA+MBAOopUUCgBCIrHIIMkAaQTEAnaFqAyJIoBgYhdqoBBACgjR0TLiCkUHTjgwNh5gIEaIPFjoIyGkgstHDw8kMB
QqwEGEHCBcIM7rxtLBwMlgwRTUiHIBzLJHESwQmKLCgAIKTSRAucAkwq47nbxQqUCAAAgIGmDISOUIUoMNVRBy0CCyo4EmR4MKHUq0yFEgACH5BAUPAHcALAQAAwAJAAMAAAcUgHd2dneFg4JPAoV3Aml2T4uRIoEAIfkEBQ8AdwAsBAADAAkAA
wAABxWAd2psd4UhDndrdnaFd4sOjI2NdoEAIfkEBQ8AdwAsBAADAAkAAwAABxSAd3Z2d4WDgk8ChXcCaXZPi5EigQA7">
</div><div class="pabid2"><span style="font-size: 9pt; color: rgb(128,128,128); ">02:35:38 pm: </span><span style="font-size: 9pt; color: rgb(255,0,0); ">
John Person: </span><span style="font-size: 9pt; font-family: Segoe UI; ">ok</span></div><div class="pabid2"><span style="font-size: 9pt; color: rgb(128,128,128); ">
02:42:53 pm: </span><span style="font-size: 9pt; color: rgb(255,0,0); "> John Person:</span><span style="font-size: 9pt; color: rgb(128,128,128); "><i> (Left chat)
</i></span></div><div class="pabid2"><span style="font-size: 9pt; color: rgb(128,128,128); ">02:42:53 pm: </span><span style="font-size: 9pt; "> Ann Other:</span>
<span style="font-size: 9pt; color: rgb(128,128,128); "><i> (Left chat)</i></span></div><div class="pabid2"><span style="font-size: 9pt; color: rgb(128,128,128); "><i></i></span></div></body></html>^
The Field Specifications in the Text Connection document are defined as follows:
ChatId: TYPE TEXT UNTIL "^"
ChatStart: TYPE DATETIME FORMAT "DD/MM/YYYY hh:mm:ss" UNTIL "^"
ChatEnd: TYPE DATETIME FORMAT "DD/MM/YYYY hh:mm:ss" UNTIL "^"
ListParticipants: TYPE TEXT UNTIL "^"
PartyCount: TYPE NUMBER UNTIL "^"
MessageCount: TYPE NUMBER UNTIL "^"
WordCount: TYPE NUMBER UNTIL "^"
BodyHTMLString: TYPE TEXT UNTIL "^"
The corresponding columns in the SQL Server table are:
ChatId: nvarchar(128), not null
ChatStart: datetime, not null
ChatEnd: datetime, not null
ListParticipants: nvarchar(max), null
PartyCount: int, not null
MessageCount: int, not null
WordCount: int, not null
BodyHTMLString: nvarchar(max), null
Questions to the forum are:
1. Can the BodyHTMLString value be defined as something other than Text in the source data in order to overcome what seems to be a character limit?
2. Is nvarchar(max) a data type that LEI recognises? In the Lotus Connectors and Connectivity Guide, it mentions NVARCHAR Text (variable length, bound <= 8000) as a SQL Server data type. What does the 8000 refer to?
3. How has anybody else exported attachments to SQL Server using LEI? Maybe there is a better way of storing the attachments?
Your suggestions are appreciated.
Thanks,
Steve
Return to top
field exceeds record length (32766)... (Steve Sherwood 22.Aug.16)
. . 
Print this page
