Showing posts with label replication. Show all posts
Showing posts with label replication. Show all posts

Monday, March 19, 2012

How to configure replication triggers to SET NOCOUNT ON?

Hopefully simple question:

How to configure replication so that replication triggers issue a SET NOCOUNT ON?

Also: are there any negative consequences of adding a SET NOCOUNT ON to replication triggers?

I'm not a DBA so my experience with replication is pretty much non-existent. Hopefully, there's a simple configuration option that I can apply when setting up replication for a database. If not, is it going to be feasible for me to write a script that runs over the replication triggers/procs to add this SET statement (any pointers on inbuilt procs that would help here would be real useful)?

Thanks in advance
Christian

Are you talking about the system replication procedures added to tables by replication?

If so I would not advise altering those procedures. They do set nocount on at a certain stage and do use this function so they are probibly not going to work if you start messing with them

Martin

|||Thanks Martin for the reply.

Yes, I was refering to system replication *triggers* added to the tables.

I need to dig into this as an sql Update statement (issued via ADO) against a single row in a table with replication triggers is returning a rowcount greater than 1, which must be as a result of the trigger.

I need for these single row updates to return a row count of 1 as this is playing havoc with an Object-Relational Mapper that I'm using. This ORM is relying on a row count of 1 as a way of determining whether a single row is affected by an update, and is throwing an exception for tables with replication triggers.

Christian
|||

I dont think this is coming from the replication triggers. They do set no count on before they do any inserting to the replication tables.

Are sure you its not something else?

declare @.is_mergeagent bit, @.at_publisher bit, @.retcode int

set rowcount 0

set transaction isolation level read committed

exec @.retcode = sys.sp_MSisreplmergeagent @.is_mergeagent output, @.at_publisher output

if @.@.error <> 0 or @.retcode <> 0

begin

rollback tran

return

end

declare @.article_rows_updated int

-- Should use @.@.rowcount below but there is a bug because of which sometimes in the presence of

-- other triggers on the table, the @.@.rowcount cannot be relied on.

select @.article_rows_updated = count(*) from inserted

if @.article_rows_updated=0

return

declare @.contents_rows_updated int, @.updateerror int, @.rowguid uniqueidentifier

, @.bm varbinary(500), @.missingbm varbinary(500), @.lineage varbinary(311), @.cv varbinary(49), @.partchangebm varbinary(500), @.joinchangebm varbinary(500), @.logicalrelationchangebm varbinary(500)

, @.tablenick int, @.partchange int, @.joinchange int, @.logicalrelationchange int, @.oldmaxversion int

, @.partgen bigint, @.newgen bigint, @.child_newgen bigint, @.child_oldmaxversion int, @.child_metadatarows_updated int

, @.logical_record_parent_oldmaxversion int, @.logical_record_lineage varbinary(311), @.logical_record_parent_regular_lineage varbinary(311), @.logical_record_parent_gencur bigint, @.logical_record_parent_rowguid uniqueidentifier

, @.replnick binary(6), @.num_parent_rows int, @.parent_row_inserted bit

declare @.dt datetime

declare @.nickbin varbinary(8)

declare @.error int

set nocount on

set @.tablenick = 758116

select @.replnick = 0xaccaad5dd04a

select @.nickbin = @.replnick + 0xFF

select @.oldmaxversion = maxversion_at_cleanup from dbo.sysmergearticles where nickname = @.tablenick

select @.dt = getdate()

......

|||Right, I'm going to have to look more in to this. Maybe the different replication models produce different trigger code?

Thanks
Christian|||

That was a merge trigger. What replication are you using?

Martin

|||Martin,

I need to get some time to really identify what's happening here. Once I have investigated further, I will post back to this thread, and hopefully be a little more informed when making my replies.

Thanks
Christian

How to configure replication triggers to SET NOCOUNT ON?

Hopefully simple question:

How to configure replication so that replication triggers issue a SET NOCOUNT ON?

Also: are there any negative consequences of adding a SET NOCOUNT ON to replication triggers?

I'm not a DBA so my experience with replication is pretty much non-existent. Hopefully, there's a simple configuration option that I can apply when setting up replication for a database. If not, is it going to be feasible for me to write a script that runs over the replication triggers/procs to add this SET statement (any pointers on inbuilt procs that would help here would be real useful)?

Thanks in advance
Christian

Are you talking about the system replication procedures added to tables by replication?

If so I would not advise altering those procedures. They do set nocount on at a certain stage and do use this function so they are probibly not going to work if you start messing with them

Martin

|||Thanks Martin for the reply.

Yes, I was refering to system replication *triggers* added to the tables.

I need to dig into this as an sql Update statement (issued via ADO) against a single row in a table with replication triggers is returning a rowcount greater than 1, which must be as a result of the trigger.

I need for these single row updates to return a row count of 1 as this is playing havoc with an Object-Relational Mapper that I'm using. This ORM is relying on a row count of 1 as a way of determining whether a single row is affected by an update, and is throwing an exception for tables with replication triggers.

Christian
|||

I dont think this is coming from the replication triggers. They do set no count on before they do any inserting to the replication tables.

Are sure you its not something else?

declare @.is_mergeagent bit, @.at_publisher bit, @.retcode int

set rowcount 0

set transaction isolation level read committed

exec @.retcode = sys.sp_MSisreplmergeagent @.is_mergeagent output, @.at_publisher output

if @.@.error <> 0 or @.retcode <> 0

begin

rollback tran

return

end

declare @.article_rows_updated int

-- Should use @.@.rowcount below but there is a bug because of which sometimes in the presence of

-- other triggers on the table, the @.@.rowcount cannot be relied on.

select @.article_rows_updated = count(*) from inserted

if @.article_rows_updated=0

return

declare @.contents_rows_updated int, @.updateerror int, @.rowguid uniqueidentifier

, @.bm varbinary(500), @.missingbm varbinary(500), @.lineage varbinary(311), @.cv varbinary(49), @.partchangebm varbinary(500), @.joinchangebm varbinary(500), @.logicalrelationchangebm varbinary(500)

, @.tablenick int, @.partchange int, @.joinchange int, @.logicalrelationchange int, @.oldmaxversion int

, @.partgen bigint, @.newgen bigint, @.child_newgen bigint, @.child_oldmaxversion int, @.child_metadatarows_updated int

, @.logical_record_parent_oldmaxversion int, @.logical_record_lineage varbinary(311), @.logical_record_parent_regular_lineage varbinary(311), @.logical_record_parent_gencur bigint, @.logical_record_parent_rowguid uniqueidentifier

, @.replnick binary(6), @.num_parent_rows int, @.parent_row_inserted bit

declare @.dt datetime

declare @.nickbin varbinary(8)

declare @.error int

set nocount on

set @.tablenick = 758116

select @.replnick = 0xaccaad5dd04a

select @.nickbin = @.replnick + 0xFF

select @.oldmaxversion = maxversion_at_cleanup from dbo.sysmergearticles where nickname = @.tablenick

select @.dt = getdate()

......

|||Right, I'm going to have to look more in to this. Maybe the different replication models produce different trigger code?

Thanks
Christian|||

That was a merge trigger. What replication are you using?

Martin

|||Martin,

I need to get some time to really identify what's happening here. Once I have investigated further, I will post back to this thread, and hopefully be a little more informed when making my replies.

Thanks
Christian

how to configure one-way merge replication?

With the appropriate replacements, this should do the job:
C:\Program Files\Microsoft SQL Server\80\COM>replmerg -
Publisher DH1791628 -PublisherDB xxxPublisher -
Publication xxxPublishertClients -Subscriber DH1791628 -Su
bscriberDB sub1 -Distributor DH1791628 -DistributorLogin
sa -DistributorPassword sa -ExchangeType 2
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thank you Paul responding back. I tried the script but I keep getting this
error 'the process could not connect to the distributor.sql server does not
exist or access denied'. which login and password are we supposed to be
using? is it same as what the sqlserver agent is running as?
here is my script:
C:\Program Files\Microsoft SQL Server\80\COM>replmerg -publisher westcoast
-publisherDB westcoast -publication westcoast -subcriber testwestcoast
-subscriberDB testwestcoast -Distributor westcoast -distributorLogin sa
-distributorpassword sa -exchangeType 2
I set the log in and password to what my sqlserveragent was running under.
It still doesnt work. Please give me your suggestion on this. Thanks
Jessy
"Paul Ibison" wrote:

> With the appropriate replacements, this should do the job:
> C:\Program Files\Microsoft SQL Server\80\COM>replmerg -
> Publisher DH1791628 -PublisherDB xxxPublisher -
> Publication xxxPublishertClients -Subscriber DH1791628 -Su
> bscriberDB sub1 -Distributor DH1791628 -DistributorLogin
> sa -DistributorPassword sa -ExchangeType 2
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Is this push or pull?
According to the script you have a server called
westcoast, which has a published database called
westcoast and a publication called westcoast - is this
correct?
It looks like it must be a pull, as your distributor is
also westcoast.
If you want to use trusted security for the merge agent,
then set the -SubscriberSecurityMode parameter to 1. It
defaults to SQL Server security, so in the original
script I put sa and a password of sa - don't laugh - I
saw this and blank recently.
Rgds,
Paul Ibison
|||Paul, I am still getting the same error. "could not connect to the
distributor'.
My publisher and publication is called westcoast. I have 'pull' from
subscriber. i am setting my distributor on the same server as my publisher.
Where do you look to check the distributor properties. I check under
Distribution Agent, but its blank. Is this is correct? When you set up the
merge replication the distribution db automactically is generated. what is
this used for?
I set the subscribersecuritymode to 0 because i am using the sql server
authenticaton. I am still having problems setting this up correctly.
Jessy
"Paul Ibison" wrote:

> Is this push or pull?
> According to the script you have a server called
> westcoast, which has a published database called
> westcoast and a publication called westcoast - is this
> correct?
> It looks like it must be a pull, as your distributor is
> also westcoast.
> If you want to use trusted security for the merge agent,
> then set the -SubscriberSecurityMode parameter to 1. It
> defaults to SQL Server security, so in the original
> script I put sa and a password of sa - don't laugh - I
> saw this and blank recently.
> Rgds,
> Paul Ibison
>
|||Can you log into testwestcoast and open up a query
analyser window (connection) to westcoast, using your
values for -DistributorLogin and -DistributorPassword to
log in and check that this works.
The distribution database holds merge metadata and things
like the next identity range to be used, and you'll need
a valid sql server login for your publisher/distributor
for this to work.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul - I was able to run script w/o any errors. however its not doing what
i want. I need to only pull from the publisher to the subscriber. If i add
new records in subscriber it gets updated to the publisher. I dont want
data send back to the publisher. What am i doing wrong here?
C:\Program Files\Microsoft SQL Server\80\COM>replmerg -Publisher [TN5347]
-PublisherDB [westcoast] -Publication [westcoast] -Subscriber [TN5347]
-SubscriberDB [testwestcoast] -subscriberlogin sa -subscriberpassword
mypassword -subscribersecuritymode 0 -subscriptionType 1 -Distributor
[TN5347] -distributorlogin sa -distributorpassword sibu27 -exchangetype 2
Here is what the result was after running the script :
Connecting to Subscriber 'TN5347'
Connecting to Distributor 'TN5347'
Initializing
Connecting to Publisher 'TN5347'
Retrieving publication information
Retrieving subscription information
The merge process is cleaning up meta data in database 'westcoast'.
The merge process cleaned up 0 row(s) in MSmerge_genhistory, 0 row(s) in
MSmerge
_contents, and 0 row(s) in MSmerge_tombstone.
The merge process is cleaning up meta data in database 'testwestcoast'.
The merge process cleaned up 0 row(s) in MSmerge_genhistory, 0 row(s) in
MSmerge
_contents, and 0 row(s) in MSmerge_tombstone.
Downloading data changes to the Subscriber
No data needed to be merged.
No data needed to be merged.
"Paul Ibison" wrote:

> Can you log into testwestcoast and open up a query
> analyser window (connection) to westcoast, using your
> values for -DistributorLogin and -DistributorPassword to
> log in and check that this works.
> The distribution database holds merge metadata and things
> like the next identity range to be used, and you'll need
> a valid sql server login for your publisher/distributor
> for this to work.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Jessy,
I think we're almost there. Please try -exchangetype 1.
Rgds,
Paul Ibison
"Jessy" <Jessy@.discussions.microsoft.com> wrote in message
news:9395EC21-B200-4ED4-9413-1F3FE22912B3@.microsoft.com...[vbcol=seagreen]
> Paul - I was able to run script w/o any errors. however its not doing
> what
> i want. I need to only pull from the publisher to the subscriber. If i
> add
> new records in subscriber it gets updated to the publisher. I dont want
> data send back to the publisher. What am i doing wrong here?
> C:\Program Files\Microsoft SQL Server\80\COM>replmerg -Publisher [TN5347]
> -PublisherDB [westcoast] -Publication [westcoast] -Subscriber [TN5347]
> -SubscriberDB [testwestcoast] -subscriberlogin sa -subscriberpassword
> mypassword -subscribersecuritymode 0 -subscriptionType 1 -Distributor
> [TN5347] -distributorlogin sa -distributorpassword sibu27 -exchangetype 2
> Here is what the result was after running the script :
> Connecting to Subscriber 'TN5347'
> Connecting to Distributor 'TN5347'
> Initializing
> Connecting to Publisher 'TN5347'
> Retrieving publication information
> Retrieving subscription information
> The merge process is cleaning up meta data in database 'westcoast'.
> The merge process cleaned up 0 row(s) in MSmerge_genhistory, 0 row(s) in
> MSmerge
> _contents, and 0 row(s) in MSmerge_tombstone.
> The merge process is cleaning up meta data in database 'testwestcoast'.
> The merge process cleaned up 0 row(s) in MSmerge_genhistory, 0 row(s) in
> MSmerge
> _contents, and 0 row(s) in MSmerge_tombstone.
> Downloading data changes to the Subscriber
> No data needed to be merged.
> No data needed to be merged.
>
> "Paul Ibison" wrote:
|||Paul- i tried the exchangetype 1 but its not working. I mean everything is
updated on both ends. I thought i should be using exchangeType 2 if i want
the publisher to only send data and not get data back. 'A value of 2
indicates that the agent should download data changes from the Publisher to
the Subscriber'. Do we need to reintalize in order for this to work?
Also I am using numeric id 'yes(not for replication)'. I know rowguid is
generated when we set up merge replication. however i noticed the numeric
id's show up twice on the table. If table 1 generated person 1 with id 1
this number is also added to the other table 2 as the same number. but table
2 already has a person with the same number 1. Is this correct?
Lastly, i previously deleted a replication on another database and ran some
store procedures sp_removedbreplication to clean up the merge replication
from publisher and subscriber. But the publications are still displayed in
the publisher folder and snapshot agent folder under replication monitor. So
my replication monitor is showing a red 'X'. How do you get rid of the
deleted publications on the replication monitor?
"Paul Ibison" wrote:

> Jessy,
> I think we're almost there. Please try -exchangetype 1.
> Rgds,
> Paul Ibison
> "Jessy" <Jessy@.discussions.microsoft.com> wrote in message
> news:9395EC21-B200-4ED4-9413-1F3FE22912B3@.microsoft.com...
>
>
|||Jessy,
yes, this value is incorrectly reported in some places so apologies. A value
of 1 indicates uploads to publisher only, 2 means subscriber to publisher.
Actually your previous post indicates you have it set correctly! I just
assumed you'd got the values the wrong way round. "Downloading data changes
to the Subscriber" appears and not the corresponding upload message. There's
no data exchange, but does it report the same message when you add a row to
publisher and subscriber ie no uploads?
Reinitialization is not necessary. Are you using MSDE? If not, then add the
parameter to the merge agents middle step and try there.
For the identity columns, it looks like you haven't enabled automatic range
management. You'll need this to partition the ranges. The best thing here is
to drop your subscription and then edit the article properties (elipsis
button) to enable a large identity range for the publisher and subscriber.
To get rid of the red x running sp_MSload_replication_status normally clears
this error or restarting the sql server service also works - tempdb needs
clearing out and this'll rebuild it.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||where do I look for the message report? I dont see any messages displayed
when i am inserting to the subscriber or publisher. I just click on the "!"
and the whatever i entered in the subscriber is still showing on the
publisher. How do i fix this?
I am using MSDE for just testing purpose. When i get this replication
working the way i want i will i try this on the server box. I believe thats
a standard/enterprise SQL Server.
"Paul Ibison" wrote:

> Jessy,
> yes, this value is incorrectly reported in some places so apologies. A value
> of 1 indicates uploads to publisher only, 2 means subscriber to publisher.
> Actually your previous post indicates you have it set correctly! I just
> assumed you'd got the values the wrong way round. "Downloading data changes
> to the Subscriber" appears and not the corresponding upload message. There's
> no data exchange, but does it report the same message when you add a row to
> publisher and subscriber ie no uploads?
> Reinitialization is not necessary. Are you using MSDE? If not, then add the
> parameter to the merge agents middle step and try there.
> For the identity columns, it looks like you haven't enabled automatic range
> management. You'll need this to partition the ranges. The best thing here is
> to drop your subscription and then edit the article properties (elipsis
> button) to enable a large identity range for the publisher and subscriber.
> To get rid of the red x running sp_MSload_replication_status normally clears
> this error or restarting the sql server service also works - tempdb needs
> clearing out and this'll rebuild it.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Monday, March 12, 2012

how to configure from local distributor to remote

Hi,
I already have a merge replication setup successfully.
However I now need to change the network from a central publisher to a
central publisher with remote distributor
See http://www.databasejournal.com/features/mssql/article.php/1458491 and
Ctrl+F for the words central publisher.
I tried to redo another publication but the wizard no longer asks me whether
I want my distributor to be on a remote machine.
Then I tried to follow this article
http://technet.microsoft.com/en-us/library/ms151192.aspx
and looked for a Configure distribution which I cannot find.
Then I tried this article
http://msdn2.microsoft.com/en-us/library/ms147363.aspx
, but realise that the value of installed in the result set is 1, so I am
not sure what I should do next. Furthermore I am unfamiliar with the T-SQL
syntax necessary to make the changes.
FYI, the result set from using the sp_get_distribution was
installed = 1
distribution server = machineName
distribution db installed = 1
is distribution publisher = 1
has remote distribution = 0
Please advise. I am running SQL server 2005 Trial edition on 2 separate
win2003 server with one of them already set up as a successful central
publisher.
If I really have no choice but to use T-SQL please give me more explicit
details. Thank you.
This is pretty much a one-off choice. To move to a remote distributor I'd
script out the publications, remove the subscriptionas and publications,
remove the distributor entirely, readd the distributor remotely then add the
publications again.
HTH,
Paul Ibison

How to configure email notify in replication...

Hi friends,
How to configure email notify in replication ?, so, sql
admin will know immediately if replication process is fail
or succed.
I already configure mail profile in sql agent, it works
properly.
I try to configure email notification in log reader agents
and distributor agent, but I got no email notification.
thanks.
Within the job you can create a TSQL step that runs xp_sendmail.
HTH,
Paul Ibison
|||use the replication alerts for this.
"kresna rudy k" <anonymous@.discussions.microsoft.com> wrote in message
news:1c48901c421fb$4bae5280$a301280a@.phx.gbl...
> Hi friends,
> How to configure email notify in replication ?, so, sql
> admin will know immediately if replication process is fail
> or succed.
> I already configure mail profile in sql agent, it works
> properly.
> I try to configure email notification in log reader agents
> and distributor agent, but I got no email notification.
>
> thanks.

How to configure bidirectional replication

In a MSCS SQL 2000 SP3 cluster environment I plan on implementing
transactional replication. Distributor and publisher are on the same server
(quad processor machine). I Intent to publish a relatively small db (@. 60
articles) to a subscriber in a remote SQL Server cluster and would like to
have updates made to subscribers also replicated back to publisher. I
understand bidirectional replication is not supported by the replication
wizard in enterprise manager. How can that be achieved?
For starters, this article may help:
http://support.microsoft.com/default...75&Product=sql
There's also an article on this subject in SQL Server Magazine
(www.sqlmag.com) by Bren Newman, ArticleID 42858.
"Cesar" wrote:

> In a MSCS SQL 2000 SP3 cluster environment I plan on implementing
> transactional replication. Distributor and publisher are on the same server
> (quad processor machine). I Intent to publish a relatively small db (@. 60
> articles) to a subscriber in a remote SQL Server cluster and would like to
> have updates made to subscribers also replicated back to publisher. I
> understand bidirectional replication is not supported by the replication
> wizard in enterprise manager. How can that be achieved?
>
>

how to configure biderectional transactional replication

Newb question: how to configure biderectional transactional replication in
sql server 2000
Message posted via http://www.droptable.com
http://support.microsoft.com/default...b;en-us;820675
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
"jhon via droptable.com" <u6539@.uwe> wrote in message
news:54e46ef6eeff0@.uwe...
> Newb question: how to configure biderectional transactional replication
in
> sql server 2000
>
> --
> Message posted via http://www.droptable.com
|||Thanks, i tried it how ever i got an message while try to enable publishing
and distribution :
"The snapshot folder path , ..................\ReplData uses a special
share name which can usually be accessed only by login with administrative
privelege ... .. . . . .. .. "
what i do wrong, or maybe i miss something
Thank you
Hilary Cotter wrote:[vbcol=seagreen]
>http://support.microsoft.com/default...b;en-us;820675
Message posted via http://www.droptable.com
|||That shouldn't be a problem. Keep going through the wizard accepting
defaults.
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
"jhon via droptable.com" <u6539@.uwe> wrote in message
news:54e4eaf0aadfc@.uwe...
> Thanks, i tried it how ever i got an message while try to enable
> publishing
> and distribution :
> "The snapshot folder path , ..................\ReplData uses a
> special
> share name which can usually be accessed only by login with administrative
> privelege ... .. . . . .. .. "
> what i do wrong, or maybe i miss something
> Thank you
>
> Hilary Cotter wrote:
>
> --
> Message posted via http://www.droptable.com
|||Thanks, Hilary,
So if i understand right i create transactional replication on server1 add
subsciber server2, and
create transactional replication on server2 add subsciber server1.
and that it??
Hilary Cotter wrote:[vbcol=seagreen]
>That shouldn't be a problem. Keep going through the wizard accepting
>defaults.
>[quoted text clipped - 12 lines]
Imagination is more important them knowledge. (A.Einshtein)
Message posted via http://www.droptable.com
|||Yes, that's it. Make sure you follow the kb article to the letter. It is
really important you set the name conflicts section of the table articles
property to keep existing table unchanged.
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
"jhon via droptable.com" <u6539@.uwe> wrote in message
news:54e6217e3e998@.uwe...
> Thanks, Hilary,
> So if i understand right i create transactional replication on server1 add
> subsciber server2, and
> create transactional replication on server2 add subsciber server1.
> and that it??
>
> Hilary Cotter wrote:
> --
> Imagination is more important them knowledge. (A.Einshtein)
>
> Message posted via http://www.droptable.com

How to configure a Database both, as a Publisher and a Subscriber ?

Hi ,
I am using merge replication wherein we have the following architecture.
We have three different databases. One DB acts as the back office server. The second acts as the desktop DB and the last one is a mobile DB i.e. SQLCE DB.
Data needs to be synchronized between the back office server and the desktop. The SQL CE database connects to the desktop for data synchronization. i.e.
I want to push and pull data from the mobile DB to the desktop DB. Hence, here the Desktop DB acts as the Publisher, and the SQL CE DB acts as subscriber.
Further, the desktop DB needs to push and pull data from the back office DB. Hence, in this case, the Desktop DB acts as the subscriber to the Server, and the back office DB acts as publisher.

Merge replication is not allowing me to make the desktop DB as publisher as well as the subscriber at the same time.

Is there any way we can accomplish this?

Thanks in advance.
Regards,
Pushkar.Hi,

Open the SQL Enterprise manager and select the distributor SQL server.
Select the replication option in the tools menu.
Select the option Configure publishers, subscribers and distributors.

Remember to declare all the involved SQL servers on the Distributor (!) via the Client Network Utility (a small program which is installed together with SQL server)

Success, VincentJS

Friday, March 9, 2012

how to completely remove replication?

What's the easiest scripted way to completely remove replication?
I've seen this article:
http://support.microsoft.com/default...b;en-us;324401
...but what I'd really like to find is a script that goes on a search
and destroy mission for replication related stuff (tables, data in
tables, jobs) and gets rid of it, without having to manually go through
all this.
The goal is to set up a test server with replication (it will be
publisher, distributor, and subscriber), and be able to rerun
replication setup from scripts - the first step being to remove
replication completely.
thanks for any tips,
Sylvia
have a look at this
http://groups.google.com/group/micro...7?dmode=source
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
"Sylvia" <sylvia@.vasilik.com> wrote in message
news:1130803614.051309.52110@.g49g2000cwa.googlegro ups.com...
> What's the easiest scripted way to completely remove replication?
> I've seen this article:
> http://support.microsoft.com/default...b;en-us;324401
> ...but what I'd really like to find is a script that goes on a search
> and destroy mission for replication related stuff (tables, data in
> tables, jobs) and gets rid of it, without having to manually go through
> all this.
> The goal is to set up a test server with replication (it will be
> publisher, distributor, and subscriber), and be able to rerun
> replication setup from scripts - the first step being to remove
> replication completely.
> thanks for any tips,
> Sylvia
>
|||Hilary Cotter wrote:
> have a look at this
> http://groups.google.com/group/micro...7?dmode=source
>
Thanks for the reference, Hilary, this looks very useful. I'm assuming
it's to be run in the Publication database?
One question - I'm confused as to why there's a lot of table create
statements at the end, instead of just deleting from those tables?
thanks,
Sylvia
|||It was meant for a subscriber, but it can be used for a publisher.
The create statements are there because if you restore a published db it may
be restored as published and then when you go to remove it, it will complain
about these missing objects.
"Sylvia" <Puget4753@.yahoo.com> wrote in message
news:1131998262.792041.14720@.g44g2000cwa.googlegro ups.com...
> Hilary Cotter wrote:
> Thanks for the reference, Hilary, this looks very useful. I'm assuming
> it's to be run in the Publication database?
> One question - I'm confused as to why there's a lot of table create
> statements at the end, instead of just deleting from those tables?
> thanks,
> Sylvia
>

Sunday, February 19, 2012

How to clear the Conflict tables from database

Dear Friends
I have created a Mearge Replication with name TEST for database DBTEST I
have deleted the same Replicaiton but now in my Database DBTEST i see the
tables with name Conflict_TEST_TableName
How i can clean the same.
Your guidance will enable me to solve the problem.
Thanks and best regards
Shailesh.
Shailesh,
you can drop these tables using query analyser.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

How to clean out Replication on DBRepaired DB

Hi all,

I had a suspect database that I had to kill.. it was non-recoverable.
The DB was a publisher and distributor for replication.
The Publications are still listed Enterprise Manager, the distribution database is still poopulated with publication info for this database.

Anyone know an easy way to clean out the replication info for the Dropped DB?

Thanks,
RyanHave you tried setting status with SP_RESETSTATUS and may use sp_helpreplicationdboption to get info. on replication.|||Yes, Thank you for replying.

I used SP_RESETSTATUS to set the status on the DB so it is not suspect.
I tried to kill replication at this point but it could not access the DB becasue the MDF file was corrupted. Nothing I could do would get the database recognizable...the recover function on start-up would consistantly fail because of a bad FCB on the file.

So I used the DBCC DBREPAIR command to kill the database...but in the replication monitor in Enterprise manager, the publications still exist for the non-existant DB. The Distribution database still contains info about those publications. Is there a Stored Procedure that anyone knows of to cleanup the distribution database?

I am also planning on applying SP4 to this SQL7 server..but have been waiting to clean up the databases first. Any thoughts?

Thank you so much to the DBFORUM community!

Ryan Jones

Originally posted by Satya
Have you tried setting status with SP_RESETSTATUS and may use sp_helpreplicationdboption to get info. on replication.|||Use SP_REMOVEDBREPLICATION and then drop the database using DROP DATABASE statement. Then go for SP4.