locked
Horrible Limitation of Microsoft.Syncronization.Data.SqlServerCe. Why was this done? RRS feed

  • Question

  • Hello,

    We are using sql server 2005 database and some of our table name are long.

    For example we have a table that looks like this:

     

    Name

    Column Name Length

    Table Name

    LabTestOfferingReportToOrganizationPurposeOfTests

    49

    Primary Key

    LabTestOfferingReportToOrganizationPurposeOfTestKey

    51

    FK

    LabTestOfferingReportToOrganizationsKey

    39

     

    <Other columns>

     


    There are several places throughout the sqlserverce dll that call a method that converts the column name and truncates it.  I have no idea why its doing this, but it causing headaches. 

    The function looks like this:

       Friend Shared Function ParameterNameOf(ByVal columnName As String) As String
    
            Dim builder As New System.Text.StringBuilder
    
            builder.Append("@P_")
    
            Dim bytes As Byte() = New Byte(System.Text.Encoding.Unicode.GetMaxByteCount(columnName.Length) - 1) {}
    
            Dim charsUsed As Integer = 0
    
            Dim bytesUsed As Integer = 0
    
            Dim completed As Boolean = False
    
            System.Text.Encoding.Unicode.GetEncoder.Convert(columnName.ToUpper(System.Globalization.CultureInfo.InvariantCulture).ToCharArray, 0, columnName.Length, bytes, 0, bytes.Length, True, charsUsed, bytesUsed, completed)
    
            builder.Append(BitConverter.ToString(bytes))
    
            builder.Replace("-", "")
    
            If (builder.Length > &H80) Then
    
                builder.Remove(&H80, (builder.Length - &H80))
    
            End If
    
    
    
            Return builder.ToString
    
        End Function
    
    
    Whenever the column name is used in first run the name through this method and uses that name.

    After those columns names are ran through this method they look like this:

    Name

    Name after Conversion

    LabTestOfferingReportToOrganizationPurposeOfTests

     

    LabTestOfferingReportToOrganizationPurposeOfTestKey

    @P_4C004100420054004500530054004F00460046004500520049004E0047005200450050004F005200540054004F004F005200470041004E0049005A0041005

    LabTestOfferingReportToOrganizationsKey

    @P_4C004100420054004500530054004F00460046004500520049004E0047005200450050004F005200540054004F004F005200470041004E0049005A0041005

    <Other columns>

     

     

    They are exactly the same.  The values don't get set correctly and many other issues occur.  This would be fine if the string builder didn't strip the string down to 80 characters.

    Basically what it boils down to is, you can't have two columns within the same table that have the first same 30 characters.

    Can someone please advice me in why this is happening?  Is there some place where I can post this so the microsoft development team can answer this?

    Thank you,
    Brandon
    • Edited by Rohrer Thursday, June 25, 2009 5:33 PM
    • Moved by Hengzhe Li Friday, April 22, 2011 2:52 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Thursday, June 25, 2009 5:04 PM

All replies

  • You could report this at https://connect.microsoft.com/

    Erik Ejlskov Jensen, MCTS: WM App, MCITP: SQL 2008 Dev - http://erikej.blogspot.com Please mark as answer, if this was it.
    Thursday, June 25, 2009 7:11 PM
  • Hi Brandon,

    You can work around this by mapping the column names to something shorter on the client side. 

    The reason behind this limitation is the mapping of the column name to parameter names in the code (required to eliminate invalid characters for parameters).  This mapping increases the length of the name and so if you had a large name to begin with, the mapped name might be longer than the allowed column name.  This issue was fixed for the peer-to-peer providers.

    -Jesse
    Thursday, June 25, 2009 9:41 PM
  • Jesse,

    I can see where you would want to eliminate the invalid characters, but why are you striping it down to 80 characters after the conversion.  This issue is fixed now? In what version and how was it fixed? 

    I don't have a problem mapping on the client side.  Where would the best place to map be, in the  SqlCeClientSyncProvider, it is the only place where I see event during the sync process?

    Thank you,
    Brandon
    Friday, June 26, 2009 12:12 PM
  • The issue is fixed in the peer to peer sync providers (ie. SqlCeSyncProvider rather than SqlCeClientSyncProvider).  I'm not terribly familiar with the code or why it's doing that.

    But you can do the mapping in the sync adapter.  On the SqlCeSyncAdapter there is DbSyncColumnMappingCollection that you can add to.

    -Jesse
    Monday, June 29, 2009 7:43 PM