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
