Microsoft KB Archive/312983

= INF: Lotus Notes GUID Format Different Than SQL Server GUID =

Article ID: 312983

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q312983



SUMMARY
Lotus Notes stores GUID columns that are returned with the dashes or braces unlike SQL Server. This article outlines how you can transform a Lotus Notes GUID column to a SQL Server GUID.



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 (&quot;-&quot;) and curly braces (&quot;{&quot;) 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(&quot;SQLServerGUIDCol&quot;) = &quot;{&quot; + _ Left(DTSSource(&quot;LotusNotesGUIDCol&quot;),8) + &quot;-&quot; + _ Mid(DTSSource(&quot;LotusNotesGUIDCol&quot;),9,4) + &quot;-&quot; + _ Mid(DTSSource(&quot;LotusNotesGUIDCol&quot;),13,4) + &quot;-&quot; + _ Mid(DTSSource(&quot;LotusNotesGUIDCol&quot;),17,4) + &quot;-&quot; + _ Right(DTSSource(&quot;LotusNotesGUIDCol&quot;),12) + &quot;}&quot; 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(&quot;LotusNotesGUIDCol&quot;) = _ Mid(DTSSource(&quot;SQLServerGuidCol&quot;),2,8) + _ Mid(DTSSource(&quot;SQLServerGuidCol&quot;),11,4) + _ Mid(DTSSource(&quot;SQLServerGuidCol&quot;),16,4) + _ Mid(DTSSource(&quot;SQLServerGuidCol&quot;),21,4) + _ Mid(DTSSource(&quot;SQLServerGuidCol&quot;),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

Additional query words: DTS data transformation services braces curly brackets

Keywords: kbinfo KB312983

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.