MORE INFORMATION
An example of a Lotus Notes GUID is:
94C9D2FAC0194357A7D7D538944A3016
When you use Transact-SQL, the SQL Server GUID column shows this structure:
94C9D2FA-C019-4357-A7D7-D538944A3016
Inside an ActiveX Script transformation, the SQL Server GUID column looks similar to:
{94C9D2FA-C019-4357-A7D7-D538944A3016}
A transfer of GUID's between Lotus Notes and SQL Server may cause a problem because of the missing hyphens ("-") and curly braces ("{") in the Lotus Notes format for the storage of GUID's.
To transform a Lotus Notes GUID column to a SQL Server GUID column by using a Data Transformation Services (DTS) ActiveX Script transform, refer to the following Microsoft Visual Basic Script example:
DTSDestination("SQLServerGUIDCol") = "{" + _
Left(DTSSource("LotusNotesGUIDCol"),8) + "-" + _
Mid(DTSSource("LotusNotesGUIDCol"),9,4) + "-" + _
Mid(DTSSource("LotusNotesGUIDCol"),13,4) + "-" + _
Mid(DTSSource("LotusNotesGUIDCol"),17,4) + "-" + _
Right(DTSSource("LotusNotesGUIDCol"),12) + "}"
You can use the following Microsoft Visual Basic Script example as another way to transform a SQL Server GUID column you need to put into a Lotus Notes GUID column:
DTSDestination("LotusNotesGUIDCol") = _
Mid(DTSSource("SQLServerGuidCol"),2,8) + _
Mid(DTSSource("SQLServerGuidCol"),11,4) + _
Mid(DTSSource("SQLServerGuidCol"),16,4) + _
Mid(DTSSource("SQLServerGuidCol"),21,4) + _
Mid(DTSSource("SQLServerGuidCol"),26,12)
If you have a table in SQL Server that has Lotus Notes GUIDs, and you need to convert the GUIDs to a SQL Server GUID format, use the following Transact-SQL example:
insert into SQLServerGUIDTable (SQLServerGUIDCol)
select left(ltrim(LotusNotesGUIDCol),8) + '-' +
substring(ltrim(LotusNotesGUIDCol),9,4) + '-' +
substring(ltrim(LotusNotesGUIDCol),13,4) + '-' +
substring(LotusNotesGUIDCol,17,4) + '-' +
right(rtrim(LotusNotesGUIDCol),12) from LotusNotesGUIDTable
You may have to move the SQL Server GUID values into a table that require the GUIDs to be in a Lotus Notes format. If the GUIDs must be in a Lotus Notes format, you can use the following Transact-SQL example:
insert into DestLotusNotesGUIDTable
select substring(cast(SQLServerGUIDCol as char(36)),1,8) +
substring(cast(SQLServerGUIDCol as char(36)),10,4) +
substring(cast(SQLServerGUIDCol as char(36)),15,4) +
substring(cast(SQLServerGUIDCol as char(36)),20,4) +
substring(cast(SQLServerGUIDCol as char(36)),25,12)
from SQLServerGUIDTable