Merge Replication with identity keys

Hi ,
I am new in replication area . I have created a merge replication . The
tables which is present in source table has identity key, after creating
replication , I am able to add duplicate values in source as well as
destinations table having same identity values .
Secondly , When new table having identity values are added in article e.g
test1 ,Current Identity seed of test1 in different in destination database
.. I want to synchronise the data in 2 database . ie. when I am inserting
values in Test 1 table on DB1, identity seed should be taken correct seed
automatically and vice versa for destination table . Most of our tables are
containing identity keys , and all reports are based on that key .
Also ,pls let me know is there any helpful articles which simulatiing same
kind of problem.
Pls help me in this regard.
Regards,
Swati
Swati,
when you're ading the table, have a look at the table properties (this is an
elipsis button next to the table). There is a tab for identities. You can
select automatic range management here - ideally select a big enough range
so that there will not be any need to get another. This will ensure no
overlaps.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Merge replication when new tables are created regularly

Hi all,
I am trying to replicate a database (sql server 2000) to a remote site. I
use merge replication and do almost continous replication.
My problem is I have one application which creates atleast 6 or 7 new tables
a day. Everytime it create a tables, snapshot agent restarts again and this
makes the entire server slow. Also when doing this snapshot agent fails most
often!
How can I get around this issue. Anybody with insight to this issue, plz
help me..
Regards,
Maani
The problem with the snapshot agent on merge publications is that it
snapshots the entire publication, even when only one article is added, and
there isn't an option of a concurrent snapshot unlike transactional. This is
probably causing the snapshot errors you are getting. It's not always very
practical, but you could potentially add the new tables to a new
publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Sounds to me like a design problem in the application. At that rate, you
would be adding approx. 2,500 tables a year! I'd hate to administer that
database.
David
"Maani" <Maani@.discussions.microsoft.com> wrote in message
news:709E0B34-622A-4E17-9996-5DCBC45D3C93@.microsoft.com...
> Hi all,
> I am trying to replicate a database (sql server 2000) to a remote site. I
> use merge replication and do almost continous replication.
> My problem is I have one application which creates atleast 6 or 7 new
> tables
> a day. Everytime it create a tables, snapshot agent restarts again and
> this
> makes the entire server slow. Also when doing this snapshot agent fails
> most
> often!
> How can I get around this issue. Anybody with insight to this issue, plz
> help me..
> Regards,
> Maani
|||So.. What should I do?
create a new publication periodically and and all newly created tables
should be added on to newest publication? How can i do that then? Somebody
plz help me with the scripts please as I am not professional DBA... :-)
"David" wrote:

> Sounds to me like a design problem in the application. At that rate, you
> would be adding approx. 2,500 tables a year! I'd hate to administer that
> database.
> David
> "Maani" <Maani@.discussions.microsoft.com> wrote in message
> news:709E0B34-622A-4E17-9996-5DCBC45D3C93@.microsoft.com...
>
>

Merge Replication 'wait' issues

Hello,
I have created 3 merge publications on my server. I notice
that when I right click and try to view the properties of
the publication, SQL takes a long time to pull up the
properties of the publication.
Any ideas on how to make this 'wait' time less or why this
occurs?
Thanks,
niv
Niv,
a few things to try to narrow it down:
when you run sp_helpmergepublication does it also take a long time? How about sp_helpmergearticle? How many records are there in sysmergepublications and sysmergesubscriptions? Are things generally slow in EM? If it is just the merge publications, what oc
curs if you open another EM and look at the current activity windows - any evidence of blocking?
HTH,
Paul Ibison
|||Paul,
Tried both those procedures and it returned very fast.
Looks like it may have been another issue. I can get into
the properties quickly now... hmm. weird..
Anyhow,
I am in need of some advice in regards to the best
replication option to select when making changes to
triggers, views, sprocs.
I tried this at one point but I think I choose drop and
recreate.. needless to say.. this was not good
I await your reply.
niv

>--Original Message--
>Niv,
>a few things to try to narrow it down:
>when you run sp_helpmergepublication does it also take a
long time? How about sp_helpmergearticle? How many records
are there in sysmergepublications and
sysmergesubscriptions? Are things generally slow in EM? If
it is just the merge publications, what occurs if you open
another EM and look at the current activity windows - any
evidence of blocking?
>HTH,
>Paul Ibison
>.
>
|||Niv,
glad it's working.
You mention "Changes to triggers, views, sprocs" - are these objects created by SQL Server as part of the replication setup, or are they user objects. If the former, I would advise against altering, although in the case of a recent poster I mentioned edit
ing the triggers, but this was a specific business scenario.
If they are user objects then you might replicate the views and sprocs as separate articles. Triggers are more difficult, and you can use sp_addscriptexec if you are using transactional replication but if not then scripting changes and using linked server
s may be considered.
HTH,
Paul Ibison

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

Merge replication UNIQUEIDENTIFIER Column?

Hi all,
I am using SQL 2005 sp1 to setup Merge replication for PDA access.
i created my Primary Key column a GUID using NEWID() as the default value.
When the snapshot was created it still went and added anothe GUID to all my
tables called rowguid.
i thought it was suposed to use the existing Unique GUID column before
creating a new one?
thanks for you advice. i hope i have the corect groups here?
inkquote:
If a published table does not have a uniqueidentifier column with the
ROWGUIDCOL property and a unique index, replication adds one
So, check for the rowguidcol property...
MC
"iKiLL" <iKill@.NotMyEmail.com> wrote in message
news:uS1$gVbWHHA.4796@.TK2MSFTNGP05.phx.gbl...
> Hi all,
> I am using SQL 2005 sp1 to setup Merge replication for PDA access.
> i created my Primary Key column a GUID using NEWID() as the default value.
> When the snapshot was created it still went and added anothe GUID to all
> my tables called rowguid.
> i thought it was suposed to use the existing Unique GUID column before
> creating a new one?
> thanks for you advice. i hope i have the corect groups here?
> ink
>|||Sorry my point was that i had created one and SQL2005 still created its own.
Now from what i can tell i think i have figgerd it out.
The behaviour i was expecting was how SQL 2000 handled the row GUID column
for snapshots.
i am using SQL 2005.
it seems that there is in fact a property of the column called RowGuid that
must be set to Yes before creating the first snapshot.
Then SQL2005 will use that column instead of creating it's own. Just setting
the data type and making it the primary key is not enough.
Thanks for your input Marko.
"MC" <marko.culoNOSPAM@.gmail.com> wrote in message
news:eruu6p$pcf$1@.ss408.t-com.hr...
> quote:
> If a published table does not have a uniqueidentifier column with the
> ROWGUIDCOL property and a unique index, replication adds one
>
> So, check for the rowguidcol property...
>
> MC
>
> "iKiLL" <iKill@.NotMyEmail.com> wrote in message
> news:uS1$gVbWHHA.4796@.TK2MSFTNGP05.phx.gbl...
>|||Yes, you need RowGuid property. Point is, you can have any number of
uniqueidentifiers in a table, but one of them needs to have this property
set. Since SQL Server doesnt want to guess which one would you like to have
as the 'main' GUID in a table, it adds another with rowguid property set.
Offcourse, if you allready have one it doesnt need to add it.
MC
"iKiLL" <iKill@.NotMyEmail.com> wrote in message
news:e2jo3%23bWHHA.600@.TK2MSFTNGP05.phx.gbl...
> Sorry my point was that i had created one and SQL2005 still created its
> own.
> Now from what i can tell i think i have figgerd it out.
> The behaviour i was expecting was how SQL 2000 handled the row GUID column
> for snapshots.
> i am using SQL 2005.
> it seems that there is in fact a property of the column called RowGuid
> that must be set to Yes before creating the first snapshot.
> Then SQL2005 will use that column instead of creating it's own. Just
> setting the data type and making it the primary key is not enough.
> Thanks for your input Marko.
>
>
>
> "MC" <marko.culoNOSPAM@.gmail.com> wrote in message
> news:eruu6p$pcf$1@.ss408.t-com.hr...
>

Merge replication UNIQUEIDENTIFIER Column?

Hi all,
I am using SQL 2005 sp1 to setup Merge replication for PDA access.
i created my Primary Key column a GUID using NEWID() as the default value.
When the snapshot was created it still went and added anothe GUID to all my
tables called rowguid.
i thought it was suposed to use the existing Unique GUID column before
creating a new one?
thanks for you advice. i hope i have the corect groups here?
ink
Sorry my point was that i had created one and SQL2005 still created its own.
Now from what i can tell i think i have figgerd it out.
The behaviour i was expecting was how SQL 2000 handled the row GUID column
for snapshots.
i am using SQL 2005.
it seems that there is in fact a property of the column called RowGuid that
must be set to Yes before creating the first snapshot.
Then SQL2005 will use that column instead of creating it's own. Just setting
the data type and making it the primary key is not enough.
Thanks for your input Marko.
"MC" <marko.culoNOSPAM@.gmail.com> wrote in message
news:eruu6p$pcf$1@.ss408.t-com.hr...
> quote:
> If a published table does not have a uniqueidentifier column with the
> ROWGUIDCOL property and a unique index, replication adds one
>
> So, check for the rowguidcol property...
>
> MC
>
> "iKiLL" <iKill@.NotMyEmail.com> wrote in message
> news:uS1$gVbWHHA.4796@.TK2MSFTNGP05.phx.gbl...
>

Merge Replication structural change

Hi

Created a table User with the fields of Uname varchar(30) and pwd varchar(30) in SQL server 2005.

I need to create a publication and Merge subscription with the below structural changes

User table with the fields of Uname varchar(25) and pwd carchar(30).

The publication table having the Uname varchar(30) but we change this to the subscription table as Uname varchar(25).

Is it possible? If you, please give the details.

Thanks.

Hello,

Is it intended to have different size of Uname? Otherwise, please try to ALTER COLUMN Uname to varchar(25) on the publisher side and this change should be populated to the subscriber side.

Thanks.

This posting is provided AS IS with no warranties, and confers no rights

|||

Hi

Thanks for your reply.

I don't want to change this structural change in publication side. I need this change in subscription side only. so that i keep my Db with original structure and i changed the subscription db with modified structure.

|||as a workaround, you can alter table ..varchar(25), propagate the changes, then set @.replicate_ddl to 0, and alter your table back to varchar(30).|||

Thanks Greg I got some idea from your reply.

If I have only one subscription means this is OK. but I am creating multiple subscription at any time.

Here is my complete requirement.

I have two publications. there is some difference in these two publications.

I am creating subscription from WinMobile device. I configure the publication name there. So that it refer the corresponding publication. These two publications are refer same tables but there is some article changes. I don't want to change the structure in both. I need to change this in one publication and the other one having the same as the DB structure.

The users subscribe the publication at any time. so we are not able to alter the tables each and every time.

Thanks again.

|||I would say you have a design issue then. Replication is used primarily to keep data in sync in multiple locations, if the column sizes must be different between data and source, you can try the workaround I mentioned above or fix your apps so that the schemas are always consistent.|||OK Greg. Thanks.

Merge Replication structural change

Hi

Created a table User with the fields of Uname varchar(30) and pwd varchar(30) in SQL server 2005.

I need to create a publication and Merge subscription with the below structural changes

User table with the fields of Uname varchar(25) and pwd carchar(30).

The publication table having the Uname varchar(30) but we change this to the subscription table as Uname varchar(25).

Is it possible? If you, please give the details.

Thanks.

Hello,

Is it intended to have different size of Uname? Otherwise, please try to ALTER COLUMN Uname to varchar(25) on the publisher side and this change should be populated to the subscriber side.

Thanks.

This posting is provided AS IS with no warranties, and confers no rights

|||

Hi

Thanks for your reply.

I don't want to change this structural change in publication side. I need this change in subscription side only. so that i keep my Db with original structure and i changed the subscription db with modified structure.

|||as a workaround, you can alter table ..varchar(25), propagate the changes, then set @.replicate_ddl to 0, and alter your table back to varchar(30).|||

Thanks Greg I got some idea from your reply.

If I have only one subscription means this is OK. but I am creating multiple subscription at any time.

Here is my complete requirement.

I have two publications. there is some difference in these two publications.

I am creating subscription from WinMobile device. I configure the publication name there. So that it refer the corresponding publication. These two publications are refer same tables but there is some article changes. I don't want to change the structure in both. I need to change this in one publication and the other one having the same as the DB structure.

The users subscribe the publication at any time. so we are not able to alter the tables each and every time.

Thanks again.

|||I would say you have a design issue then. Replication is used primarily to keep data in sync in multiple locations, if the column sizes must be different between data and source, you can try the workaround I mentioned above or fix your apps so that the schemas are always consistent.|||OK Greg. Thanks.

Merge Replication problems

Greetings all...

I am trying to patch up a merge replication situation, and I have
1. configured Distributor & publisher
2. created a publication
3. created a subscription.

The subscription (on subscriber server) status=sucdeeded; Details=The initial snapshot for publication is not yet available.

When I go to snapshot agent (on publisher), it is saying: Status=Failed
Detail = The process could not bulk copy out of table 'contB22652175F9A4388BC64D1B43C8E8B30'.

Thanks!
Jester99This will sound crazy, but the first place I'd check is disk space availability, replication can use a lot of space, in particular when creating the initial snapshot.

How big is the database that the publication is based on ? how much of the total db will the publication contain ?

Merge Replication problem....

I created a new pull subscription and I noticed that the subscriber is downloading changes from the publisher (new rows or changes made on other subscribers) but is not uploading new info back to the publisher.

I have been working with this type of replication for a year now and never have any problem like this.

- The job succeeded always. but no changes are uploaded to the susbsciber.
- Checking the merge agent history has 0 on inserts, deletes and updates on the susbcriber.
- Publisher is running the same merge replication with other 42 subscribers without problems.
- Windows 2000 Server (sp4) , Sql 2000 (sp3)
- Sanpshot size is 12 Gigas , I don't want or can't affort to copy again the snapshot.

Thank you for your help.
:(Does your distribution database have the transactions that have not been applied to the Subscriber? If not, you have no option but to apply a snapshot - how you do that is dependent on what is missing and what is available. You can roll-your-own snapshot.

Test a dummy insert on your Publisher and see what happens from Distribution to Subscription(s). It appears something is not running correctly on the Subscriber, and checking the Distributor will help you determine that.

Finally, you may want to create a job that alerts when the difference between the date of the most recently inserted row in your most active article and GetDate() is > n hours.|||Thank you for your suggestions.

I described my problem incorrectly above in saying that I could upload my new data to the subscriber.

The problem is that one of my 42 subscribers is receiving all and any changers that any of the 42 subscriber makes, but it does not sent it's new info to the publsher.

So, it could receive changes but not send any upward.

Sorry, for the confusion.|||My suggestions still apply - just work from the Subscriber back to the Publisher. Use whatever Distributor that Subscriber is using to communicate Updates back to the Publisher to see where the problem is.|||If you find that any changes at the subscriber are not 'taking' i.e. they get overwritten from the publisher even when there are no changes to those particular records anywhere else, then you should check that you have set the subscription priority appropriately and not just left it as 'use publisher as proxy'. I speak from experience.

Merge Replication Filtered Publication

Hi People,
I need some help.
I am using SQL Server 2000 and have created a publication using Merge
Replication. One of the tables are filtered as follows:
SELECT <published_columns> FROM [dbo].[tblFingerPrint] WHERE
recordid in (select fingerprintid from tblKeyHolder)
The reason I do this is beacause I only want rows from fingerprint table to
be at the subscriber where the fingerprint ID is being used.
But for some reason it doesnt work, it will work when I reinitilize the
subscription, but not when I do a normal synch.
Can anyone help?
Thanks in advance
Warren
Warren,
can you try adding the table tblKeyHolder to the publication and having an
explicit join?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul I will give that a try.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OlRQ7lcvFHA.664@.tk2msftngp13.phx.gbl...
> Warren,
> can you try adding the table tblKeyHolder to the publication and having an
> explicit join?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Hi,
I tried the following
SELECT <published_columns> FROM [dbo].[tblFingerPrint] INNER
JOIN [dbo].[tblKeyHolder] ON fingerprintid =
[dbo].[tblFingerPrint].recordid
and got this error:
Error 107: The column prefix 'dbo.tblFingerPrint' does not match with a
table name or alias name used in the query.
A column used in filter clause 'fingerprintid =
[dbo].[tblFingerPrint].recordid' either does not exist in the table
'tblKeyholder' or cannot be excluded from the current partition.
Any ideas?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OlRQ7lcvFHA.664@.tk2msftngp13.phx.gbl...
> Warren,
> can you try adding the table tblKeyHolder to the publication and having an
> explicit join?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Hi Warren,
what I was thinking is to use the Filter Rows tab in the publication
properties and using the create join option. You can add 1=1 to the Filter
clause if this is not needed.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Merge Replication error while applying Snapshot

Hi,

i am getting the below error while applying running the Synchronization agent for the Subscriber. I have created replication topology with one central server and one subscriber. Here central server has windows server 2003 and subscriber has windows XP. Both are having SQL server 2005. After creating the merge subscriber, i am runnnig the Synchronization agent manually for the first time. While running that i am getting below error. Anybody aware of this error.

2006-06-24 00:26:00.175 Applying the snapshot to the Subscriber
2006-06-24 00:26:02.722 The schema script 'D_NUM_7.sch' could not be propagated to the subscriber.
2006-06-24 00:26:02.784 Category:NULL
Source: Merge Replication Provider
Number: -2147201001
Message: The schema script 'D_NUM_7.sch' could not be propagated to the subscriber.
2006-06-24 00:26:02.816 Category:AGENT
Source: WMBT-07
Number: 0
Message: The process could not read file '\\WMBT-01\repldata\unc\LTR-IN001_TEST_PUB\20060624034804\D_NUM_7.sch' due to OS error 1265.
2006-06-24 00:26:02.831 Category:OS
Source:
Number: 1265
Message: The system detected a possible attempt to compromise security. Please ensure that you can contact the server that authenticated you.

does the account under which the agent is running under have access to the share?|||

Greg,

Thanks for giving me the response...

Actually i am not clear about the Account... How to see the Account under which Agent is running?

I still dont understand where we are linking the Account and Agent.

Can you help on this ?

Thanks in advance.

|||

Greg,

Are you asking the about Agent in Central Server or in the Subscriber.

Thanks.

|||When you setup replication, you are asked to specify security credentials for the Snapshot agent, Log Reader Agent, Distribution Agent, Merge Agent, Queued Reader Agent. (Which agents you need to specify credentials for vary based on the method of replication.) The account that you specified for either the distribution agent (for snapshot or transactional replication) or the merge agent (for merge replication) needs to have the authority to access the snapshot folder in order for this to work successfully.|||

There are two places you may need to check.

1. Since snapshot files are saved under distributor, in your case, it may be the central server, which is both publisher and distributor, so make sure your publication snapshot files are saved under an alternate folder, UNC folder, which can be accessed by merge agent running on the subscriber.

2. Check merge agent account which is used to connect to distributor, it must have read permissions on the snapshot share. You can check it through open merge agent job properties.

Hope the above will be helpful.

Thanks

Yunjing

|||

Hi All,

Thanks for all you replies. I solved the problem i faced.

Normally when i create a Subscriber for Account under which Merge Agent will run i used to give as "Run Under SQL Server Agent Service Account" . It was working for me all these days. In all the machines where I created Replication was having windows XP. But when i was trying to create the Replication with systems with windows Server 2003, i have got the above said error.

To solve that error i have created one windows account in the Publisher and Subscriber with same name and same password. Then while creating the Publisher and Subscriber I was using this windows account as process Account for all the Agents. After that it was working fine. Here the windows account has to be there is both Publisher and Subscriber with same name and same Password. It was working for me. I have added that windows account as part of Administrator Group.

Thanks,

Thams.

Merge Replication error while applying Snapshot

Hi,

i am getting the below error while applying running the Synchronization agent for the Subscriber. I have created replication topology with one central server and one subscriber. Here central server has windows server 2003 and subscriber has windows XP. Both are having SQL server 2005. After creating the merge subscriber, i am runnnig the Synchronization agent manually for the first time. While running that i am getting below error. Anybody aware of this error.

2006-06-24 00:26:00.175 Applying the snapshot to the Subscriber
2006-06-24 00:26:02.722 The schema script 'D_NUM_7.sch' could not be propagated to the subscriber.
2006-06-24 00:26:02.784 Category:NULL
Source: Merge Replication Provider
Number: -2147201001
Message: The schema script 'D_NUM_7.sch' could not be propagated to the subscriber.
2006-06-24 00:26:02.816 Category:AGENT
Source: WMBT-07
Number: 0
Message: The process could not read file '\\WMBT-01\repldata\unc\LTR-IN001_TEST_PUB\20060624034804\D_NUM_7.sch' due to OS error 1265.
2006-06-24 00:26:02.831 Category:OS
Source:
Number: 1265
Message: The system detected a possible attempt to compromise security. Please ensure that you can contact the server that authenticated you.

does the account under which the agent is running under have access to the share?|||

Greg,

Thanks for giving me the response...

Actually i am not clear about the Account... How to see the Account under which Agent is running?

I still dont understand where we are linking the Account and Agent.

Can you help on this ?

Thanks in advance.

|||

Greg,

Are you asking the about Agent in Central Server or in the Subscriber.

Thanks.

|||When you setup replication, you are asked to specify security credentials for the Snapshot agent, Log Reader Agent, Distribution Agent, Merge Agent, Queued Reader Agent. (Which agents you need to specify credentials for vary based on the method of replication.) The account that you specified for either the distribution agent (for snapshot or transactional replication) or the merge agent (for merge replication) needs to have the authority to access the snapshot folder in order for this to work successfully.|||

There are two places you may need to check.

1. Since snapshot files are saved under distributor, in your case, it may be the central server, which is both publisher and distributor, so make sure your publication snapshot files are saved under an alternate folder, UNC folder, which can be accessed by merge agent running on the subscriber.

2. Check merge agent account which is used to connect to distributor, it must have read permissions on the snapshot share. You can check it through open merge agent job properties.

Hope the above will be helpful.

Thanks

Yunjing

|||

Hi All,

Thanks for all you replies. I solved the problem i faced.

Normally when i create a Subscriber for Account under which Merge Agent will run i used to give as "Run Under SQL Server Agent Service Account" . It was working for me all these days. In all the machines where I created Replication was having windows XP. But when i was trying to create the Replication with systems with windows Server 2003, i have got the above said error.

To solve that error i have created one windows account in the Publisher and Subscriber with same name and same password. Then while creating the Publisher and Subscriber I was using this windows account as process Account for all the Agents. After that it was working fine. Here the windows account has to be there is both Publisher and Subscriber with same name and same Password. It was working for me. I have added that windows account as part of Administrator Group.

Thanks,

Thams.

Merge Replication Error

Hello,
I have a publiction on my development box using SQL Server 2005 developer
edition. I created a subscription for my test box, sitting next to me,
intialized and synchronized with the subscriber. I also have SQL Server
Express installed on my development box. I created a subscription for this
server and tried to initialize and sychronize with that subscriber. However,
the subscription never appears in that servers Local Subscriptions list.
The SQL Server Agent is running under a domain account that we use for
replication and this account has the neccessary permissions on each
subcribers machines including my dev box. However, I get the error:
The job failed. Unable to determine if the owner (domain\username) of job
MachineName\SQL2005-Weed-WeedMerge-MachineName\SQLEXPRESS-14 has server
access (reason: Could not obtain information about Windows NT group/user
'domain\username', error code 0xea. [SQLSTATE 42000] (Error 15404) The
statement has been terminated. [SQLSTATE 01000] (Error 3621)).
When I view the job history.
Can anyone help me with this?
S
Just changing the job owner to sa should do it.
Cheers,
Paul Ibison
|||You might also want to consider using a SQL account for the merge agent.
relevantNoise - dedicated to mining blogs for business intelligence.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SAL" <SAL_@.NoNo.com> wrote in message
news:OunlT8o3HHA.5184@.TK2MSFTNGP02.phx.gbl...
> Hello,
> I have a publiction on my development box using SQL Server 2005 developer
> edition. I created a subscription for my test box, sitting next to me,
> intialized and synchronized with the subscriber. I also have SQL Server
> Express installed on my development box. I created a subscription for this
> server and tried to initialize and sychronize with that subscriber.
> However, the subscription never appears in that servers Local
> Subscriptions list.
> The SQL Server Agent is running under a domain account that we use for
> replication and this account has the neccessary permissions on each
> subcribers machines including my dev box. However, I get the error:
> The job failed. Unable to determine if the owner (domain\username) of job
> MachineName\SQL2005-Weed-WeedMerge-MachineName\SQLEXPRESS-14 has server
> access (reason: Could not obtain information about Windows NT group/user
> 'domain\username', error code 0xea. [SQLSTATE 42000] (Error 15404) The
> statement has been terminated. [SQLSTATE 01000] (Error 3621)).
> When I view the job history.
> Can anyone help me with this?
> S
>
|||Hi Paul,
I did try that but what finally worked was changing the account that SQL
Server runs under to the account that it couldn't determine if it had access
rights.
So, event though the owner of the job was the domain replication account,
SQL Server Express was running under a different account and the job was
actually running under my acccount. I change SQL Server Expresses log on
account to me and it started working. This only seemed to be a problem on my
dev server for some reason.
Thanks again.
S
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:75E0428A-BF72-4E1E-B428-76743E99AEA5@.microsoft.com...
> Just changing the job owner to sa should do it.
> Cheers,
> Paul Ibison
>

Merge Replication deployment model

In order to deploy the replication implementation along with the software that has been created,how to package the correct set of necessary assemblies along with our client to ensure that the software can function correctly?
While trying to include the SQL Server assemblies that we are using from the SDK directory, we get some internal security token errors.

Please also suggest what would be the recommended deployment model for stand-alone clients which are replicating between a local and remote server where the local doesn't include an install of the SQL management tools? (It will have express.)

What are the assembiles you are getting error on?

If your client is going to install SQL Express (with replication components) before your application will be installed, you will not have problems.

Also you could make SQL Express as a pre-requisite when you publish and that way the client will be able to install Express and then your application will not have any problems with dependencies.

Merge Replication Conflict - Primary Key Constraint

I have an application that uses Merge Replication. In my database design,
before I created the merge replication publication, I modified the tables and
set my identity columns to Yes (Not for replication) option.
I am hitting a problem however, when I try to insert a new row in one of the
tables and then replicate the data back to the server. I am getting a
conflict with the reason being:
Reason Type 5, Reason code 2627
Reason Text:
The row was inserted at Subscriber.x' but could not be inserted at Server.X.
Violation of PRIMARY KEY constraint 'PK_X'. Cannot insert duplicate key in
object X.
I thought that having Not for replication option set for identity columns
would cause replication to use the server and/or subscriber environment to
generate identity column values on inserts.
Any help would be greatly appreciated.
Hi Guy - you'll need to partition the identity ranges to avoid identity
conflicts.
The easiest, most maintainable way is to change the article properties to
enable Automatic identity range management and then reinitialize.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul. I will try this out and see if the problem is resolved. My
apologies for this message posting so many times. My browser was acting up
and reporting an error when I posted. So I thought my post had failed.
One Question: Do you know why this is happening. I must not be
understanding the purpose of not for replication option. Because I thought
this is what would resolve this type of problem.
"Paul Ibison" wrote:

> Hi Guy - you'll need to partition the identity ranges to avoid identity
> conflicts.
> The easiest, most maintainable way is to change the article properties to
> enable Automatic identity range management and then reinitialize.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||The NFT replication allows replication agents to do an identity insert when
distributing changes. However if the renge isn't partitioned, there will
still be a conflict.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .