Merge Replication and MSDE: publication

Showing posts with label publication. Show all posts

Friday, March 30, 2012

Merge snapshot problem with views and ArticleType property with SQ

I am trying to create a merge publication with a snapshot that includes all
tables, views, procs, and UDFs using SQLDMO. I have no problems creating the
publication with tables only. When I try to add procs using the following
code I get the error
The @.schema_option parameter for a procedure or function schema article can
include only the options 0x0000000000000001 or 0x0000000000002000.
I assume I am not setting up the objMrgArt object properly. Any help is
appreciated.
For i = 1 To objRepSps.Count
Set objRepSp = objRepSps(i)
Set objMrgArt = New SQLDMO.MergeArticle2
objMrgArt.Name = objRepSp.Name
objMrgArt.SourceObjectName = objRepSp.Name
objMrgArt.SourceObjectOwner = objRepSp.Owner
objMrgArt.ArticleType = SQLDMORep_ProcSchemaOnly
objMrgPub.MergeArticles.Add objMrgArt
Next i
Since different article types accept different set of schema options (or in
SQLDMO terminology, MergeArticle.CreationScriptOptions) and the (arguably
semi-buggy) SQLDMO behavior of having a set of default CreationScriptOptions
that is not compatible with all article types, you would need to explicitly
specify a compatible set of CreationScriptOptions when creating a non-table
article. Extending the example that you have given below, you can write:
For i = 1 To objRepSps.Count
Set objRepSp = objRepSps(i)
Set objMrgArt = New SQLDMO.MergeArticle2
objMrgArt.Name = objRepSp.Name
objMrgArt.SourceObjectName = objRepSp.Name
objMrgArt.SourceObjectOwner = objRepSp.Owner
objMrgArt.ArticleType = SQLDMORep_ProcSchemaOnly
objMrgArt.CreationScriptOptions =
SQLDMO_CREATIONSCRIPT_TYPE.PrimaryObject Or
SQLDMO_CREATIONSCRIPT_TYPE.ExtendedProperties
objMrgPub.MergeArticles.Add objMrgArt
Next i
To ensure that the base stored procedure and the associated extended
properties are replicated with the initial snapshot.
HTH.
-Raymond
This posting is provided "as is" with no warranties and confers no rights.
"mspradley" <mspradley@.discussions.microsoft.com> wrote in message
news:57624519-68F3-4073-BF90-D2DD80EBCEF3@.microsoft.com...
> I am trying to create a merge publication with a snapshot that includes
all
> tables, views, procs, and UDFs using SQLDMO. I have no problems creating
the
> publication with tables only. When I try to add procs using the following
> code I get the error
> The @.schema_option parameter for a procedure or function schema article
can
> include only the options 0x0000000000000001 or 0x0000000000002000.
> I assume I am not setting up the objMrgArt object properly. Any help is
> appreciated.
>
> For i = 1 To objRepSps.Count
> Set objRepSp = objRepSps(i)
> Set objMrgArt = New SQLDMO.MergeArticle2
> objMrgArt.Name = objRepSp.Name
> objMrgArt.SourceObjectName = objRepSp.Name
> objMrgArt.SourceObjectOwner = objRepSp.Owner
> objMrgArt.ArticleType = SQLDMORep_ProcSchemaOnly
> objMrgPub.MergeArticles.Add objMrgArt
> Next i
>

Merge snapshot frequency

I have the following scenario,
1.merge replication with 3000 subscribers
2.I have dynamic publication (NOT dynamic snapshot)
3.we observed that synchronization is slow whenever we do a data import at
the publisher
4.because of this we ran the snapshot agent immediately after the data import
5.after the snapshot run, we find the subscriber syncs faster
I would like to know what are the implications of running snapshot agent.
Will it remove the delta changes which are pending for sync? Or is it okay if
I run snapshot as much as I like?
Expect slowness whenever you do a data import as there is always the impact
of the dataload and then there is the added data you have to merge.
Are you saying if you run the snapshot you get faster sync's? This only
makes sense if you re-initialize your subscribers after regenerating the
snapshot.
If you have anonymous subscribers the snapshot is always generated each time
you run it. If you have named it is only regenerated if subscribers expire
or require reinitialization.
Hilary Cotter
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
"Ravi Lobo" <RaviLobo@.discussions.microsoft.com> wrote in message
news:45E03380-6DE7-4AB1-B20D-A97C18B04717@.microsoft.com...
>I have the following scenario,
> 1.merge replication with 3000 subscribers
> 2.I have dynamic publication (NOT dynamic snapshot)
> 3.we observed that synchronization is slow whenever we do a data import at
> the publisher
> 4.because of this we ran the snapshot agent immediately after the data
> import
> 5.after the snapshot run, we find the subscriber syncs faster
>
> I would like to know what are the implications of running snapshot agent.
> Will it remove the delta changes which are pending for sync? Or is it okay
> if
> I run snapshot as much as I like?
>
>
|||Thank you Hilary for you time. I have some more clarifications,
1.I have sql server ce subscribers
2.Hence I need to use anonymous subscription
I have the following questions here,
a)Can I use pre-generated snapshot in my case? (Anonymous + sql ce
subscribers)
b)I also have dynamic filters on the publisher. What impact I will have by
re-running the snapshot second time, on the subscriber?
"Hilary Cotter" wrote:

> Expect slowness whenever you do a data import as there is always the impact
> of the dataload and then there is the added data you have to merge.
> Are you saying if you run the snapshot you get faster sync's? This only
> makes sense if you re-initialize your subscribers after regenerating the
> snapshot.
> If you have anonymous subscribers the snapshot is always generated each time
> you run it. If you have named it is only regenerated if subscribers expire
> or require reinitialization.
> --
> Hilary Cotter
> 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
>
> "Ravi Lobo" <RaviLobo@.discussions.microsoft.com> wrote in message
> news:45E03380-6DE7-4AB1-B20D-A97C18B04717@.microsoft.com...
>
>

Merge Rpl. Pull from subscriber access denied problem

Hi,
I have set up one laptop as the Distributer/Publisher. Went through the
wizard and set up a Publication also, used Pubs. Then registered another
remote laptop that I can see via the network, and it can see me. I went
through the wizard again and set up a Push to that laptop. Said it ran good,
and I can see the tables on the remote laptop now.
I deleted the Push and keep trying to create a Pull at the other laptop,
(subscriber). The wizard sets it up, but when it Starts Syncronizing, it
immediately gets the big Red X.
The error said The schema script
'\\ACER\ReplShare\ReplData\unc\ACER_pubs_pubs_arti cles\20050928212317\stores_1.sch' could not be propagated to the subscriber.
I can see this share from both ends. I have read a ton about the accounts
the agent has to run under in the last 12 hours, but can't see what I am
doing wrong.
Is there a trick here?
Thanks.
Steve,
try logging on to the subscriber laptop using the same account that the sql
server agent uses as a service account. The see if you can browse to the
snapshot folder
\\ACER\ReplShare\ReplData\unc\ACER_pubs_pubs_artic les\20050928212317. If you
can, see if you can copy the contents of this directory locally. I'm
guessing that the first part won't be possible due to permission
restrictions, but please post back with your results.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I had tried some other things prior to being able to read your response.
Here is what I have done that is now working.
I set up new SQL users on both Publisher/Dist. and Subscriber, giving them
the proper roles. I then set up a new Windows login on each box, "Bob", of
type admin. I then changed both the MSSQLSERVICE and SQLAGENTSERVICE on each
to run under "Bob". When it asks for logon credintials for the subscriber, I
use the sa, when it asks for the publisher, I use the new SQL user I had set
up. Probably may have been able to use that for the subscriber instead of
the sa, don't know.
I have tried so many things since yesterday afternoon, I am a little unsure
as to what actually solved it. From all I read over night, having the two
service run under "Bob" was needed.
Thank you for the response,
Steve
"Paul Ibison" wrote:

> Steve,
> try logging on to the subscriber laptop using the same account that the sql
> server agent uses as a service account. The see if you can browse to the
> snapshot folder
> \\ACER\ReplShare\ReplData\unc\ACER_pubs_pubs_artic les\20050928212317. If you
> can, see if you can copy the contents of this directory locally. I'm
> guessing that the first part won't be possible due to permission
> restrictions, but please post back with your results.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||This is OK - what you've set up is known as pass-through authentication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Is there a better or more preferred method? We have one laptop that acts as
the publisher/distributor, and two other laptops that will be subscribers.
They run over a wireless network. The two subscribers will be able to
initial pull merge replications.
Thanks,
Steve
"Paul Ibison" wrote:

> This is OK - what you've set up is known as pass-through authentication.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Steve,
are the laptops all on the same domain? If so, you could use a domain
account, which is given rights to the distributor's working folder. If not,
it's either pass-through, FTP, backup and restore or alternative snapshot
locations.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Merge reversed

Running Win 2000 & SQL 2000
I have a merge publication/subscription (rows are
filtered by location), replicating data between 16
servers (15 remote location & a main office). When data
is changed at the a remote location, the changes gets
propagated to the main office, but at some point, the
change gets reversed.
Any idea what is causing this?
Larry,
when you say it gets reversed, do you mena the row is deleted from the
subscriber? This could be caused if you are partitioning data and a row has
been added on a subscriber which doesn't accord with its filter.
HTH,
Paul Ibison
|||Look in the conflict table, for the article, on the publisher. This might
give you a clue as to what is going on.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Paul,
No the row is not deleted, but changed back to its
original state, prior to being updated at the
subscriber. This happens sporadically, but is causing
MAJOR problems.
Larry...
|||Rand,
There is nothing in the conflict tables for any article
in the publication.
Larry...
|||I saw your reply to Rand and if both filtering and conflicts are ruled out
(and no user on the publisher is altering the data!) then this is very
strange. I'd use profiler or an audit trigger to capture what is going on.
HTH,
Paul Ibison
|||Thanks Paul...will do.
|||Does the MSmerge_history table on the distributor offer any reason why
the updates were not applied?
Reinout Hillmann
SQL Server Product Unit
This posting is provided "AS IS" with no warranties, and confers no rights.
anonymous@.discussions.microsoft.com wrote:
> Rand,
> There is nothing in the conflict tables for any article
> in the publication.
> Larry...
>

Merge Repliction - Run Stored Procedure when merge agent starts

I have database on SQL Server 2000 set up with a merge publication.
This publication is configured with a number of dynamic filters to
reduce the amount of data sent to each client. Each client has an
anonymous pull subscription. The merge process can be triggered by the
windows sync manager and my application.

To improve performance I have created some helper tables to hold the
mapping between user login and primary keys of selected entities.

For the replicated data to be correct the contents of the helper tables
needs to be up to date.

I need to fire off a stored procedure on the publisher before
replication starts to verify that this data is up to date. I can not
see any documented way of doing this however I have been experimenting
with some unorthodox systems.

Firstly has anyone any ideas?

I have been considering adding a trigger to some of the tables used by
the Microsoft replication code - yes I know this is very nasty.

My problems arise because executing this stored procedure will cause
some data to be updated. In updating data we could create a new
generation in the database. I must therefore run my stored procedure
before any the Microsoft code makes any generation checks / updates.

Anyone done anything similar, Anyone have any better ideas?

Any comments would be gratefully received.(tedd_n_alex@.yahoo.com) writes:
> My problems arise because executing this stored procedure will cause
> some data to be updated. In updating data we could create a new
> generation in the database. I must therefore run my stored procedure
> before any the Microsoft code makes any generation checks / updates.
> Anyone done anything similar, Anyone have any better ideas?

Not being very good at replication, I can only recommend you to visit
microsoft.public.sqlserver.replication. There are some guys that knows
replicataion really well there.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Merge Replication: Dropping an article from existing Publication

Hi All,
How to drop a single article (TABLE) from merge replication keeping
publication intact.
Regards
Javed Iqbal
Javed,
unlike transactional, this is not possible. The nearest you can get is to
drop the publication hten recreate it without the article and do a nosync
initialization.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
Thanks for the help.
Actually I want to change a column width in a replicated table which is
appearing at first position.
I can add dummy column then update dummy column with data. Then drop the
main column. Recreate main column with new size and update it. And finally
removing the dummy column using sp_repladdcolumn and sp_repldropcolumn.
But it will add the column at last position.
Is there any way to redefine column position on a replicated table?
OR
How to add a column at specific position?
Thanks in advance.
Regards
Javed Iqbal
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ucTtJkYTGHA.4132@.TK2MSFTNGP11.phx.gbl...
> Javed,
> unlike transactional, this is not possible. The nearest you can get is to
> drop the publication hten recreate it without the article and do a nosync
> initialization.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Javed,
ideally the application is not dependant on the column position. We have no
TSQL to specify it, and the only practical way is to recreate the table. In
terms of merge, this would be a reinitialization. If at all possible, make
your application use column names rather than position numbers. As an
interim measure can you perhaps define a view?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul for your prompt help.
Ideally my TSQL should refer column names but in my applications I am not
sure about what programmers have coded in TSQL. And it is not possible to
check those programs

.
There are many reports based on this table hence I do not want to play with
the column position.
Correct me if I wrong at following.
Scenario - Merge Replication with NOSYNC.
I have only option to drop subscription / publication and recreate it with
the earlier generated scripts.
Regards
Javed Iqbal
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23x2oERZTGHA.1160@.TK2MSFTNGP09.phx.gbl...
> Javed,
> ideally the application is not dependant on the column position. We have
no
> TSQL to specify it, and the only practical way is to recreate the table.
In
> terms of merge, this would be a reinitialization. If at all possible, make
> your application use column names rather than position numbers. As an
> interim measure can you perhaps define a view?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Javed,
a nosync initialization would be ok. You could change the column position on
the publisher and subscriber before synchronising. In this case you'd take
over control of the identity range management. You could only use the
previous scripts if they were themselves defined as nosync.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Yes Paul,
I am using NOSYNC since MERGE Replication is created / started.
Identities are ODD on Publisher EVEN on subscriber.
Any further inputs are highly appreciated.
Thanks a lot again Paul.
Regards
Javed Iqbal
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ecN9afZTGHA.6084@.TK2MSFTNGP14.phx.gbl...
> Javed,
> a nosync initialization would be ok. You could change the column position
on
> the publisher and subscriber before synchronising. In this case you'd take
> over control of the identity range management. You could only use the
> previous scripts if they were themselves defined as nosync.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>

Wednesday, March 28, 2012

Merge replication, Identity not for replication, identity range

Hi all,
When setting up a (merge) publication using the wizard, you can click
the three-dotted button in the "specify articles" and then setup the
identity range for publisher and subscribers.
While this is an interesting option, I see that the default is not
checked (yes, I have the identity column setup as not for replication).
Now my database only has about 50 tables but nevertheless, not my
favorit waste of time to call up each table one by one and adjust these
values one by one (tabbing to the correct tab, checking the option,
adjusting the range-values).
So, is there any way to get this checked by default (that would be one
step forward) and preferably also adjust the values while we're at it?
Many thanks in advance,
Ferry
Ferry,
there's no way that I'm aware of. One posibility is to script out the
publication and use a find and replace in notepad to make the alteration
then drop the original and recreate the publication using this script -
admittedly not nice but would save a lot of leg-work.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||From Paul Ibison :
> Ferry,
> there's no way that I'm aware of. One posibility is to script out the
> publication and use a find and replace in notepad to make the alteration then
> drop the original and recreate the publication using this script - admittedly
> not nice but would save a lot of leg-work.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
Thanks Paul. I guess I'll have a look and see if I can do either that
or perhaps find some 'backdoor'...
Ferry
|||From Ferry :
> From Paul Ibison :
> Thanks Paul. I guess I'll have a look and see if I can do either that or
> perhaps find some 'backdoor'...
> Ferry
Having said that, I started a more detailed search on Google and came
up with this one. Going to check this later:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/*
'************************************************* **
' Purpose :Add Identity range to the user defined Tables under a given
publisher
' Inputs :Publisher Name and Identity Range
' Returns :None
' Author :Mahesh M Kodli
'************************************************* ***
CREATE PROCEDURE AddMergeArticle
@.pPublisherName VARCHAR(255),
@.pIdentityRange BIGINT
As
DECLARE @.tRV INT
DECLARE @.tArticle VARCHAR(255)
SET @.tRV = 0
DECLARE Merge_Article_Cursor CURSOR FOR
--Get all the user defined Tables for which to add Identity Range
SELECT TABLE_NAME TableName FROM INFORMATION_SCHEMA.TABLES WHERE
rtrim(ltrim(table_type))='BASE TABLE'
AND TABLE_NAME Not like 'Conflict%' AND TABLE_NAME Not like
'dtproperties'
AND TABLE_NAME Not like 'sys%' AND TABLE_NAME Not like 'MS%'
OPEN Merge_Article_Cursor
FETCH NEXT FROM Merge_Article_Cursor INTO @.tArticle
WHILE @.@.FETCH_STATUS = 0
BEGIN
--Check if the Table has identity column and accordingly set the Auto
--Identity Range to TRUE or FALSE Before adding Identity Range to the
--article
IF OBJECTPROPERTY ( OBJECT_ID(@.tArticle), 'TableHasIdentity') = 1
BEGIN
IF NOT EXISTS (SELECT * FROM sysmergeextendedarticlesview WHERE name
= @.tArticle AND pubid IN (select pubid FROM sysmergepublications WHERE
name like @.pPublisherName AND UPPER(publisher)=UPPER(@.@.servername) and
publisher_db=db_name()))
BEGIN
--Use the System stored procedure add merge article to add Identity
range for -- each article
EXECUTE sp_addmergearticle @.publication = @.pPublisherName, @.article =
@.tArticle, @.source_owner = N'dbo', @.source_object = @.tArticle, @.type =
N'table', @.description = null, @.column_tracking = N'true',
@.pre_creation_cmd = N'drop', @.creation_script = null, @.schema_option =
0x000000000000CFF1, @.article_resolver = null, @.subset_filterclause =
null, @.vertical_partition = N'false', @.destination_owner = N'dbo',
@.auto_identity_range = N'true', @.pub_identity_range = @.pIdentityRange,
@.identity_range = @.pIdentityRange, @.threshold = 80,
@.verify_resolver_signature = 0, @.allow_interactive_resolver = N'false',
@.fast_multicol_updateproc = N'true', @.check_permissions =
0,@.force_invalidate_snapshot=1
END
END
ELSE
BEGIN
IF NOT EXISTS (SELECT * FROM sysmergeextendedarticlesview WHERE name
= @.tArticle AND pubid IN (select pubid FROM sysmergepublications WHERE
name like @.pPublisherName AND UPPER(publisher)=UPPER(@.@.servername) and
publisher_db=db_name()))
BEGIN
EXECUTE sp_addmergearticle @.publication = @.pPublisherName, @.article
= @.tArticle, @.source_owner = N'dbo', @.source_object = @.tArticle, @.type
= N'table', @.description = null, @.column_tracking = N'true',
@.pre_creation_cmd = N'drop', @.creation_script = null, @.schema_option =
0x000000000000CFF1, @.article_resolver = null, @.subset_filterclause =
null, @.vertical_partition = N'false', @.destination_owner = N'dbo',
@.auto_identity_range = N'False', @.pub_identity_range = NULL,
@.identity_range = NULL, @.threshold = NULL, @.verify_resolver_signature =
0, @.allow_interactive_resolver = N'false', @.fast_multicol_updateproc =
N'true', @.check_permissions = 0,@.force_invalidate_snapshot=1
END
END
FETCH NEXT FROM Merge_Article_Cursor INTO @.tArticle
END
CLOSE Merge_Article_Cursor
DEALLOCATE Merge_Article_Cursor
IF (@.@.ERROR <> 0)
BEGIN
SELECT @.tRV = -95 --UnSuccessful
GOTO XIT
END
XIT:
RETURN @.tRV
|||Ferry,
thanks for the heads up. I've found the link
http://www.devarticles.com/c/a/SQL-S...2000-Part-2/3/
and will add it onto my site. The only problem with the proc above is that
it assumes you want to replicate all tables, so there's room for another
(simpler) version which takes a tablename as a third parameter.
To have this occur without any intervention such as using Mahesh's script
you would have to edit the stored procedure sp_addmergearticle itself to
hardcode the automatic range management. This is a posibility but which
obviously invalidates support agreements - depends on how confident you are
of getting it spot on

Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

merge replication with pull subscriber(compress snapshot)

Hi
I am trying to implement merge publication with compress snapshot, copy it
to the subscriber,
create a pull subscriber with the alternate folder(where the snapshot was
copied to)
and then run the agent for synchronization (first time).
i get error that the table in the publication does not exist in the
subscriber database.
It seems that the agent ignores the snapshot and it tries to just merge the
changes between publisher and subscriber.
when i create the table manually at the subscriber with the rowguid column,
it does not fail but it stills does not aplly the snapshot.
if i update/insert new row and run the agent it does merge the changes.
am i missing a crucial step in the process that causes that problem?
Thanks in advance
Daniel
daniel
Please can you post up the error message in its entirety. I suspect that it
is related to a script missing. Also, please try not using the CAB file
method. If you need to compress the snapshot, zip it up and unzip on the
subscriber (I only say this because I've never tested using an alternative
folder with a CAB file).
Paul Ibison
|||Hi and thank u for the replying.
When i start the agent for the first time i get the following error:
"Table 't3' does not exist in the Subscriber database."
At the log i can see that he does try to get the snapshot from alternate
folder:
" Snapshot will be applied from the alternate folder ..."
thanks in advance
Daniel
daniel
"Paul Ibison" wrote:

> Please can you post up the error message in its entirety. I suspect that it
> is related to a script missing. Also, please try not using the CAB file
> method. If you need to compress the snapshot, zip it up and unzip on the
> subscriber (I only say this because I've never tested using an alternative
> folder with a CAB file).
> Paul Ibison
|||Interesting - if you enable full logging is there more info? Also, are you
using the uncompressed snapshot files (if not please try this way also)? In
the snapshot files do you see the table creation script for t3?
(the alternative to this investigation if you like is to do a nosync
initialization).
Paul Ibison
|||Hi Paul
Yes there is a script for table creation.
I tried to use the uncompressed and i got the same error.
How do i do the no sync initialization?
daniel
"Paul Ibison" wrote:

> Interesting - if you enable full logging is there more info? Also, are you
> using the uncompressed snapshot files (if not please try this way also)? In
> the snapshot files do you see the table creation script for t3?
> (the alternative to this investigation if you like is to do a nosync
> initialization).
> Paul Ibison
|||Please have a look here. It largely applies to transactional, but mostly the
same info applies: http://www.replicationanswers.com/NoSyncInitializations.asp
HTH,
Paul Ibison

Monday, March 26, 2012

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

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 to multiple servers losing data

Hi,
We are using merge replication between four servers (1 publisher & 3
subscribers). The same articles (tables) are in each publication. I run the
application which changes data in some tables and adds records in another
table. The inserted data is immediately updated via trigger. After running
the application all expected data is present. If I manually force
replication to each subscriber sequentially, all expected data is present.
If I run replication between the servers at the same time, the table to
which data was added will lose some data. The data lost was not the data
that was just added. We are running SQL Server 2000 SP3a on all servers. Any
ideas?
tia,
Paul
Look at the 'view conflict' at replication monitor...
"PaulW" <MSNewsGroup@.Digi-Sol.com>
news:urv9zNxlHHA.1216@.TK2MSFTNGP03.phx.gbl...
> Hi,
> We are using merge replication between four servers (1 publisher & 3
> subscribers). The same articles (tables) are in each publication. I run
> the application which changes data in some tables and adds records in
> another table. The inserted data is immediately updated via trigger.
> After running the application all expected data is present. If I manually
> force replication to each subscriber sequentially, all expected data is
> present. If I run replication between the servers at the same time, the
> table to which data was added will lose some data. The data lost was not
> the data that was just added. We are running SQL Server 2000 SP3a on all
> servers. Any ideas?
> tia,
> Paul
>
|||There are no recorded conflicts. This table only has data inserted, then
updated through a trigger. We did view the transaction log. The only entries
with the table were the inserts we initiated followed by a delete/insert for
the trigger update.
Paul
"Grigoris Tsolakidis" <gcholakidis@.spam_remove.hotmail.com> wrote in message
news:uhRQsHGmHHA.4852@.TK2MSFTNGP03.phx.gbl...
> Look at the 'view conflict' at replication monitor...
> "PaulW" <MSNewsGroup@.Digi-Sol.com>
> news:urv9zNxlHHA.1216@.TK2MSFTNGP03.phx.gbl...
>
sql

Merge replication throwing very weird error messages

I've been setting up subscriptions to a merge publication for the past 3 days. All of a sudden, I'm getting a pile of very strange errors. Replication is configured. I have 16 subscribers to an existing publication configured and synchronizing changes without any issues. The script that I'm using to create all of the subscriptions is as follows:

use [PIC]

exec sp_addmergesubscription @.publication = N'PIC', @.subscriber = N'machinename\SQLEXPRESS',

@.subscriber_db = N'MyDatabase', @.subscription_type = N'Push', @.sync_type = N'Automatic',

@.subscriber_type = N'Global', @.subscription_priority = 75, @.description = N'', @.use_interactive_resolver = N'False'

exec sp_addmergepushsubscription_agent @.publication = N'PIC', @.subscriber = N'machinename\SQLEXPRESS',

@.subscriber_db = N'MyDatabase', @.job_login = null, @.job_password = null, @.subscriber_security_mode = 1,

@.publisher_security_mode = 1, @.frequency_type = 64, @.frequency_interval = 0, @.frequency_relative_interval = 0,

@.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0,

@.active_end_time_of_day = 235959, @.active_start_date = 0, @.active_end_date = 0

The last one that I added #17, gives the following errors after successfully creating the subscription.

Command attempted:

{call sys.sp_MSmergesubscribedb ('true', 0) }

Error messages:

The merge process could not initialize the subscription. Ensure that the subscription registration exists at the publisher, and reregister the subscription if necessary. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201011)
Get help: http://help/MSSQL_REPL-2147201011

RegCreateKeyEx() returned error 5, 'Access is denied.' (Source: MSSQLServer, Error number: 22002)
Get help: http://help/22002

Could not add article resolver 'Microsoft SQL Server Additive Conflict Resolver' information to the registry (Source: MSSQLServer, Error number: 21713)
Get help: http://help/21713

Could not register article resolver: 'Microsoft SQL Server Additive Conflict Resolver'. (Source: MSSQLServer, Error number: 21715)
Get help: http://help/21715

The system tables for merge replication could not be created successfully. (Source: MSSQLServer, Error number: 20008)
Get help: http://help/20008

I've tried to manually create it using the GUI and get an even stranger error message as follows:

TITLE: New Subscription Wizard

Microsoft SQL Server Management Studio is unable to access replication components because replication is not installed on this instance of SQL Server. For information about installing replication, see the topic Installing Replication in SQL Server Books Online.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.ReplUtilitiesErrorSR&EvtID=ReplicationNotInstalled&LinkId=20476

ADDITIONAL INFORMATION:

Replication components are not installed on this server. Run SQL Server Setup again and select the option to install replication. (Microsoft SQL Server, Error: 21028)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=21028&LinkId=20476

BUTTONS:

I very obviously have replication installed by virtue of the fact that I have a publication and 16 subscriptions that are currently synchronizing. Any ideas?

Yes, I can connect to the subscriber from the server. That's how I created the subscription database in the first place, by connecting a query window to the subscriber and issuing a create database statement.|||

Is it the same account you are using to run this command as the other 16 subscriptions?

Did the sql server service account change between the success and this failure attempt?

Did you add sp_addmergesubscription_agent to those successful subscriptions too?

|||Same account was used to run these two commands for all of the other 16 subscriptions. No, the service account did not change. Yes, we successfully added sp_addmergesubscription_agent to the other 16.