Merge Replication using a guid as a dynamic filter

Hi ...

I am working on a project where the server version of application has vouchers from different entities. I have created a publication manually. My next step was to create a client subscription using rmo and to execute a pull. This part works fine. Code samples from http://msdn2.microsoft.com/en-us/library/ms147314.aspx

My next step would be to implement dynamic filtering using the guid of the entity as a parameter.

I dont want to use suser_sname() or host_name() as I want to use a fixed login for the replication for all users, and a client could have several host dbs (sql express, sql mobile)

My goal would be to pass a guid-value to the HostName Property of the MergePullSubscription class and convert it to an uniquidentifier and use it as a filter as I have not found any other way to pass a guid as a filter.

RMO-Code:

subscription.HostName = "4bb0e468-c68a-4253-ba82-f71c3a6e302d"

Filter:

SELECT <published_columns> FROM [dbo].[voucher] WHERE [entity_ID] = dbo.fx_ConvertHostToEntity()

Function:

create function fx_ConvertHostToEntity()
returns uniqueidentifier
as
Begin
declare @.host nvarchar(50)
set @.host = host_name()
declare @.entity uniqueidentifier
set @.entity = cast( @.host as uniqueidentifier)
return @.entity
End

When trying to set the filter sql server complains that a character string cannot be casted to a uniqueidentifier - so i can not set this filter. Is there a way to pass a parameter other then the username or the hostname as a filter?

SELECT <published_columns> FROM [dbo].[voucher] WHERE [entity_ID] =@.entity, where @.entity is a guid

Thanks for your support

Alex

Hi, Alex,

You can convert the string to binary then to uniqueidentifier, like following:

declare @.host nvarchar(50)
set @.host = host_name()
declare @.entity uniqueidentifier
set @.entity = cast( convert(varbinary, @.host) as uniqueidentifier)
print @.entity

Output:

00450044-004C-004C-3000-340032003700

Thanks,

Zhiqiang Feng

|||

Thanks for the info changed the function according to your suggestion.

Now a new problem arrises: I receive 0 rows for the filtered tables. When changing the funktion to

ALTER FUNCTION [dbo].[fx_ConvertHostToEntity] ()
returns uniqueidentifier
as
Begin
declare @.host nvarchar(50)
set @.host = host_name()
declare @.entity uniqueidentifier
set @.entity = cast( convert(varbinary, @.host) as uniqueidentifier)
-- return @.entity
return '4bb0e468-c68a-4253-ba82-f71c3a6e302d'
End

I get the rows in the replicated table. So the problem must be somewhere either in the creation of the sanpshots or the resolution of the hostname

|||

Using Replication Monitor for the publication I also found out using the properties of the publication that the snapshot for the data partition has not been created. So I did this manually and will code it later on. When replicating again I got the follwowing error shown up in the Replication Monitor (I left out the first one as I am not using web sync right now):

Partitioned snapshot validation failed for this Subscriber. The snapshot validation token stored in the specified partitioned snapshot location does not match the value '{00620034-0062-0030-6500-340036003800}' used by the Merge Agent when evaluating the parameterized filter function. If specifying the location of the partitioned snapshot (using -DynamicSnapshotLocation), you must ensure that the snapshot files in that directory belong to the correct partition or allow the Merge Agent to automatically detec (Source: MSSQL_REPL, Error number: MSSQL_REPL27223)

Find the full source code below:

Imports Microsoft.SqlServer.Replication

Imports Microsoft.SqlServer.Management.Common

Public Class Replication

Private subscriberName As String

Private publisherName As String

Private windowsLogin As String

Private windowsPWD As String

Private publicationName As String

Private publicationDbName As String

Private subscriptionDbName As String

Private passedHostname As String

''' <summary>

''' a new instance of the replication object

''' </summary>

''' <param name="EntityID">id of the entity as string</param>

''' <param name="SubscriberHost">the hostname of the subscribing sql instance</param>

''' <param name="PublisherHost">the hostname of the publishing sql instance</param>

''' <param name="Login">the name of the windows login to authenticate towards the publisher: domain\user</param>

''' <param name="PWD">the password</param>

''' <param name="Publication">the name of the puplicaiton on the publisher</param>

''' <param name="PublicationDB">the name of the puplication db</param>

''' <param name="SubscriptionDB">the name of the subscription db</param>

''' <remarks></remarks>

Sub New(ByVal EntityID As Guid, ByVal SubscriberHost As String, ByVal PublisherHost As String, ByVal Login As String, ByVal PWD As String, ByVal Publication As String, ByVal PublicationDB As String, ByVal SubscriptionDB As String)

subscriberName = SubscriberHost

publisherName = PublisherHost

'the guid of the entity is passed as hostname to be used for filtering

PassedHostname = EntityID.ToString

windowsLogin = Login

windowsPWD = PWD

publicationName = Publication

subscriptionDbName = SubscriptionDB

publicationDbName = PublicationDB

End Sub

Sub SetupPullSubscription()

'Create connections to the Publisher and Subscriber.

Dim subscriberConn As ServerConnection = New ServerConnection(subscriberName)

Dim publisherConn As ServerConnection = New ServerConnection(publisherName)

' Create the objects that we need.

Dim publication As MergePublication

Dim subscription As MergePullSubscription

Try

' Connect to the Subscriber.

subscriberConn.Connect()

' Ensure that the publication exists and that

' it supports pull subscriptions.

publication = New MergePublication()

publication.Name = publicationName

publication.DatabaseName = publicationDbName

publication.ConnectionContext = publisherConn

If publication.LoadProperties() Then

If (publication.Attributes And PublicationAttributes.AllowPull) = 0 Then

publication.Attributes = publication.Attributes Or PublicationAttributes.AllowPull

End If

' Define the pull subscription.

subscription = New MergePullSubscription()

subscription.ConnectionContext = subscriberConn

subscription.PublisherName = publisherName

subscription.PublicationName = publicationName

subscription.PublicationDBName = publicationDbName

subscription.DatabaseName = subscriptionDbName

subscription.HostName = passedHostname

' Specify the Windows login credentials for the Merge Agent job.

subscription.SynchronizationAgentProcessSecurity.Login = windowsLogin

subscription.SynchronizationAgentProcessSecurity.Password = windowsPWD

' Make sure that the agent job for the subscription is created.

subscription.CreateSyncAgentByDefault = True

' Create the pull subscription at the Subscriber.

subscription.Create()

Dim registered As Boolean = False

' Verify that the subscription is not already registered.

For Each existing As MergeSubscription In _

publication.EnumSubscriptions()

If existing.SubscriberName = subscriberName Then

registered = True

End If

Next

If Not registered Then

' Register the local subscription with the Publisher.

publication.MakePullSubscriptionWellKnown(subscriberName, subscriptionDbName, SubscriptionSyncType.Automatic, MergeSubscriberType.Local, 0)

'publication.MakePullSubscriptionWellKnown(subscriberName, subscriptionDbName, SubscriptionSyncType.Automatic, MergeSubscriberType.Local, 0)

End If

Else

' Do something here if the publication does not exist.

Throw New ApplicationException(String.Format("The publication '{0}' does not exist on {1}.", publicationName, publisherName))

End If

Catch ex As Exception

' Implement the appropriate error handling here.

Throw New ApplicationException(String.Format("The subscription to {0} could not be created.", publicationName), ex)

Finally

subscriberConn.Disconnect()

publisherConn.Disconnect()

End Try

End Sub

Sub PullMergeReplication()

' Create a connection to the Subscriber.

Dim conn As ServerConnection = New ServerConnection(subscriberName)

Dim subscription As MergePullSubscription

Try

' Connect to the Subscriber.

conn.Connect()

' Define subscription properties.

subscription = New MergePullSubscription()

subscription.ConnectionContext = conn

subscription.DatabaseName = subscriptionDbName

subscription.PublisherName = publisherName

subscription.PublicationDBName = publicationDbName

subscription.PublicationName = publicationName

' If the pull subscription and the job exists, start the agent job.

If subscription.LoadProperties() And Not subscription.AgentJobId Is Nothing Then

subscription.SynchronizeWithJob()

Else

' Do something here if the subscription does not exist.

Throw New ApplicationException(String.Format("A subscription to '{0}' does not exists on {1}", publicationName, subscriberName))

End If

Catch ex As Exception

' Do appropriate error handling here.

Throw New ApplicationException("The subscription could not be synchronized.", ex)

Finally

conn.Disconnect()

End Try

End Sub

End Class

|||

this means you're trying to apply a dynamic snapshot that's not for your partition. i.e. your filter is for 'StoreA', but you're trying to apply snapshot belonging to 'StoreB'.

|||

After finding no solution to the problem, feeling that the problem has something to to with the conversion vom guid to string, I decided to have a second col in the table that was filled with a trigger: the guid converted to nvarchar.

And suddenly I knew where the problem was hiding:

hostname value: 4bb0e468-c68a-4253-ba82-f71c3a6e302d

cast( convert(varbinary,host_name()) as uniqueidentifier) -> 4BB0E468-C68A-4253-BA82-F71C3A6E302D

And that is the solution: When using a guid as the hostname and filter, the values have to be either both lower case or upper case. Otherwise you will get an empty result for dynamicly filtered tables :)

Thanks for your support

Alex