none
These columns don't currently have unique values. RRS feed

  • Question

  • I have a Primary Key on a table Opportunities that contains two fields OpportunityID and OpportunityItemNumber. OpportunityItemNumber uses Latin1_General_CS_AS collation everything else in the database uses the default, which is Latin1_General_CI_AS.

    I had the same issue as the guys in this thread 'Sync provisioning not honouring column collation' http://social.microsoft.com/Forums/br/syncdevdiscussions/thread/1dcd6fd4-335d-407a-9fd3-5ac1f82d10ad and have found what is posted there very useful. I updated the PKs on the necessary columns during CREATE TABLE's, CREATE TYPE'S and DECLARE @changed TABLE commands in the provisioning script. This worked fine for provisioning the Server and a client, but it now falls over on the initial synchronisation.

    I am wondering whether I have missed something as the Initial sync is breaking down whilst processing a batch which contains a Case Sensitive key. I receive a 'These columns don't currently have unique values' error. Should I be modifying anything else in the script ?

    thanks

    Eliot

    Tuesday, July 17, 2012 12:56 PM

Answers

  • Hi JuneT

    Thank you so much for your help over the last couple of days, you are a legend ;-)

    Everything is working fine, batching with a different Collation 'Latin1_General_CS_AS' on a Primary Key on a single table (other tables all use Latin1_General_CI_AS the Db default).

    As you suggested I subscribed to a new event handler for ApplyingChanges in ConfigureProvider ...

    AddHandler Me.dbProvider.ApplyingChanges, AddressOf syncProvider_ApplyingChanges

    Then added the following eventhandler for ApplyingChanges ...

            Public Shared Sub syncProvider_ApplyingChanges(ByVal sender As Object, ByVal e As Microsoft.Synchronization.Data.DbApplyingChangesEventArgs)
                ' We look for every table in the scope
                Dim i As Integer = 0
                Do While (i < e.Context.DataSet.Tables.Count)
                    Dim dataTable As DataTable = e.Context.DataSet.Tables(i)
                    If dataTable.TableName.Equals("Opportunities") Then ' << table with different COLLATION .. case sensitive
                        dataTable.CaseSensitive = True
                    End If
                    i = (i + 1)
                Loop
            End Sub

    thanks 

    Eliot

    • Marked as answer by EliotRayner Wednesday, July 18, 2012 3:06 PM
    Wednesday, July 18, 2012 3:06 PM

All replies

  • can you enable sync framework tracing and post the trace just prior to the error being raised? i have a feeling the error is not in SQL but in the Dataset/Datatable.
    Tuesday, July 17, 2012 1:31 PM
    Moderator
  • Hi June

    Sync tracing doesn't show any errors, but heres the stack trace ...

       at System.Data.ConstraintCollection.AddUniqueConstraint(UniqueConstraint constraint)
       at System.Data.ConstraintCollection.Add(Constraint constraint, Boolean addUniqueWhenAddingForeign)
       at System.Data.DataTable.set_PrimaryKey(DataColumn[] value)
       at Microsoft.Synchronization.Data.SqlServer.SqlChangeHandler.PopulateAppliedRowList(DataView dataView, DataTable failedRowPks, DataViewRowState applyType)
       at Microsoft.Synchronization.Data.SqlServer.SqlChangeHandler.ApplyBulkChanges(DataTable dataTable)
       at Microsoft.Synchronization.Data.RelationalSyncProvider.ApplyChangesInternal(DbSyncScopeMetadata scopeMetadata, IDbTransaction transaction, FailedDeleteDelegate_type failedDeleteDelegate, DataSet dataSet, ChangeApplicationType applyType)
       at Microsoft.Synchronization.Data.RelationalSyncProvider.ApplyChanges(DbSyncScopeMetadata scopeMetadata, IDbTransaction applyTransaction, DataSet dataSet, DbSyncSession DbSyncSession, Boolean commitTransaction, FailedDeleteDelegate_type failedDeleteDelegate, String batchFileName, ChangeApplicationAction& action)
       at Microsoft.Synchronization.Data.RelationalSyncProvider.SingleTransactionApplyChangesAdapter.Apply(DataSet dataSet, Boolean commitTransaction, FailedDeleteDelegate_type failedDeleteDelegate, String batchFileName, ChangeApplicationAction& action)
       at Microsoft.Synchronization.Data.DbSyncBatchConsumer.ApplyBatches(DbSyncScopeMetadata scopeMetadata, DbSyncSession syncSession, SyncSessionStatistics sessionStatistics)


    thanks

    Eliot

    Tuesday, July 17, 2012 1:35 PM
  • am not sure you can actually do something about this as the error is actually coming from when setting the PK column in the DataTable.

    The setting of the PK column will automatically set the Unique property for the column as well which is where the error is coming from i think.

    this is a long shot, but see if this makes a difference:

    subscribe to the ChangesSelected event.

    inside the event, just go thru each DataTable and set the CaseSensitive property of the table to true.

    Tuesday, July 17, 2012 1:52 PM
    Moderator
  • Hi June

    Tried that but the same error occurred at proxy.ApplyChanges ....

    Tuesday, July 17, 2012 2:44 PM
  • is this the same place where you were getting the error before? which provider did you catch the ChangesSelected event?
    Wednesday, July 18, 2012 6:02 AM
    Moderator
  • Hi June

    I caught it with the localProvider

                If TypeOf localProvider Is SqlSyncProvider Then
                    AddHandler CType(localProvider, SqlSyncProvider).ChangesSelected, AddressOf syncProvider_ChangesSelected
                End If

    Heres the ChangeSelected ...

     Private Shared Sub syncProvider_ChangesSelected(ByVal sender As Object, ByVal e As Microsoft.Synchronization.Data.DbChangesSelectedEventArgs)
                ' We look for every table in the scope
                Dim i As Integer = 0
                Do While (i < e.Context.DataSet.Tables.Count)
                    Dim dataTable As DataTable = e.Context.DataSet.Tables(i)
                    If dataTable.TableName.Equals("Opportunities") Then
                        dataTable.CaseSensitive = True
                    End If
                    i = (i + 1)
                Loop
            End Sub

    This is the same place (I removed the ChangesSelected to test) it occured before in ProcessChangeBatch part of RelationalProviderProxy class.

    Public Overrides Sub ProcessChangeBatch(ByVal resolutionPolicy As ConflictResolutionPolicy, ByVal sourceChanges As ChangeBatch, ByVal changeDataRetriever As Object, ByVal syncCallbacks As SyncCallbacks, ByVal sessionStatistics As SyncSessionStatistics)
                Dim context As DbSyncContext = TryCast(changeDataRetriever, DbSyncContext)
                If context IsNot Nothing AndAlso context.IsDataBatched Then
                    Dim fileName As String = New FileInfo(context.BatchFileName).Name
    
                    'Retrieve the remote peer id from the MadeWithKnowledge.ReplicaId. MadeWithKnowledge is the local knowledge of the peer 
                    'that is enumerating the changes.
                    Dim peerId As String = context.MadeWithKnowledge.ReplicaId.ToString()
    
                    'Check to see if service already has this file
                    If Not Me.proxy.HasUploadedBatchFile(fileName, peerId) Then
                        'Upload this file to remote service
                        Dim stream As New FileStream(context.BatchFileName, FileMode.Open, FileAccess.Read)
                        'Dim contents As Byte() = New Byte(CByte(stream.Length) - 1) {}
                        Dim contents() As Byte = New Byte((stream.Length) - 1) {}
                        Using stream
                            stream.Read(contents, 0, contents.Length)
                        End Using
                        Me.proxy.UploadBatchFile(fileName, contents, peerId)
    
                    End If
    
                    context.BatchFileName = fileName
                End If
    
                Dim stats As SyncSessionStatistics = Me.proxy.ApplyChanges(resolutionPolicy, sourceChanges, changeDataRetriever)
                sessionStatistics.ChangesApplied += stats.ChangesApplied
                sessionStatistics.ChangesFailed += stats.ChangesFailed
    
            End Sub

    thanks

    Eliot



    • Edited by EliotRayner Wednesday, July 18, 2012 7:29 AM
    Wednesday, July 18, 2012 7:24 AM
  • when batching, sync framework rehydrates the datasetsurrogate to a new dataset.

    try disabling batching and see if it works.

    Wednesday, July 18, 2012 8:15 AM
    Moderator
  • Hi June

    It wasn't exactly the same place just the same error ... occuring in my RelationalWcfSyncService

    I'll try turning off batching now.

    thanks

    Eliot

    Wednesday, July 18, 2012 8:39 AM
  • Hi June

    When I disable batching and just sync with this table (Opportunities that causes the error when batching) it works. I'm not going to be able to do this without batching so can I get to the new dataset that the Sync Framework rehydrates to ?

    regards Eliot

    Wednesday, July 18, 2012 11:02 AM
  • can you try the ApplyingChanges event on the remote provider? on the service side, subscribe to the event and do the same thing you did with the ChangesSelected.
    Wednesday, July 18, 2012 11:56 AM
    Moderator
  • where on the server side should I subscribe to the event ? i have the following ...

    ApplyChanges is in my RelationalWcfSyncService class.

    Wednesday, July 18, 2012 12:14 PM
  • inside RelationalWebWcfSyncService, i think you will find a call to ConfigureProvider, you can put the event subscription inside ConfigureProvider or after the call to ConfigureProvider
    Wednesday, July 18, 2012 1:02 PM
    Moderator
  • Hi June

    I'm a little unsure of what goes there ... i have

    'Utility functions that the sub classes need to implement.
            Protected MustOverride Function ConfigureProvider(ByVal scopeName As String, ByVal hostName As String) As RelationalSyncProvider
    
            Public Event GetChangesSelected(what goes in here ??) 
    
            Private Shared Sub ChangesSelected(ByVal sender As Object, ByVal e As Microsoft.Synchronization.Data.DbChangesSelectedEventArgs)
                ' We look for every table in the scope
                Dim i As Integer = 0
                Do While (i < e.Context.DataSet.Tables.Count)
                    Dim dataTable As DataTable = e.Context.DataSet.Tables(i)
                    If dataTable.TableName.Equals("Opportunities") Then
                        dataTable.CaseSensitive = True
                    End If
                    i = (i + 1)
                Loop
            End Sub

    regards

    Eliot

    Wednesday, July 18, 2012 1:30 PM
  • No, am not asking to create an event ChangesSelected inside ConfigureProvider. What i meant is inside ConfigureProvider, subscribe and create an event handler for ApplyingChanges and put in the same do...while block that you had in the ChangesSelected event
    Wednesday, July 18, 2012 1:51 PM
    Moderator
  • Hi JuneT

    Thank you so much for your help over the last couple of days, you are a legend ;-)

    Everything is working fine, batching with a different Collation 'Latin1_General_CS_AS' on a Primary Key on a single table (other tables all use Latin1_General_CI_AS the Db default).

    As you suggested I subscribed to a new event handler for ApplyingChanges in ConfigureProvider ...

    AddHandler Me.dbProvider.ApplyingChanges, AddressOf syncProvider_ApplyingChanges

    Then added the following eventhandler for ApplyingChanges ...

            Public Shared Sub syncProvider_ApplyingChanges(ByVal sender As Object, ByVal e As Microsoft.Synchronization.Data.DbApplyingChangesEventArgs)
                ' We look for every table in the scope
                Dim i As Integer = 0
                Do While (i < e.Context.DataSet.Tables.Count)
                    Dim dataTable As DataTable = e.Context.DataSet.Tables(i)
                    If dataTable.TableName.Equals("Opportunities") Then ' << table with different COLLATION .. case sensitive
                        dataTable.CaseSensitive = True
                    End If
                    i = (i + 1)
                Loop
            End Sub

    thanks 

    Eliot

    • Marked as answer by EliotRayner Wednesday, July 18, 2012 3:06 PM
    Wednesday, July 18, 2012 3:06 PM