What I would like to do is set up transactional replication from server1 (Publisher) to server2 (Subscriber), with server2 acting as the (Distributor).
I am successful on server2 in allowing server2 as a distributor for server1. When I try to configure replication on server1 to use server2 as a distributor and subscriber I get this message once
"
SQL Server Enterprise Manager could not enable 'server2' as a Subscriber.
Error 14071: Could not find the Distributor or the distribution database fom the local server. The Distributor may not be installed, or the local server may not be configured as a Publisher at the Distributor.
"
and this mesage for each database I enable for subscription, I get this message.
"
SQL Server Enterprise Manager could not enable database 'x' for snapshot or transactional replications.
Error 20028: The Distributor has not been correctly installed. Could not enable database for publishing. The replication 'publish' of database 'x' has been set to false.
"
The last message I get is
"
SQL Server Enterprise Manager successfully enabled 'server2' as the Distributor for 'server1'.
"
This message is apparently false.
When I try to Configure Publishing, Subscribers and Distributing... on server1, I get the message
"
SQL Server Enterprise Manager could not retrieve information about the Distributor or the distribution database.
"
In an attempt to fix this I:
1. disable publishing on server1,
2. disable publising and distribution on server2.
3. enable publishing and distribution on server2.
4. enable publishing on server1 using server2 as distributor.
Again I get errors on step 4.
I have even tried re-installing SQL Server 2000 on server2 with no improvement.
Can some one please shed some light on this problem?
Thanks in advance.Connect to the SQL Server using Query Analyzer and run the following query: select @.@.servername.
If it returns NULL you will need to run the sp_addserver procedure to get
the internal server name correct. You may need to run sp_dropserver as
well. These are documented in Books on Line.
A NULL server name can cause wierd behavior with replication.|||I do have a NULL server name problem. I have run the sp_dropserver, sp_addserver and have retarted SQL Server.
I have tried again to create the replication & it now WORKS!
Satya, thanks soooo much for you help!
Showing posts with label acting. Show all posts
Showing posts with label acting. Show all posts
Wednesday, March 21, 2012
Wednesday, March 7, 2012
Replication not updating the subscriber
I am having some trouble getting merge replication working. I have 1 server
acting as a publisher and distributer and another server acting as my
subscriber. Both are running SQL 2000 SP4.
I need both the publisher and subscriber to be able to update the database.
One database is for internal company apps and the other is for our web
server both of which need access to the same database.
The steps are as follows.
1. Create a new publication using the "Create Publication Wizare" and using
all the defaults. When I come to the "Specify Articles" dialog I add my
table articles using the defaults and a few minor changes as follows:
- Keep the existing table unchanged.
- Automatically assign and maintain a unique range for each
subscription.
- Range size at pub = 10000
- Range size at sub = 10000
- Threshold = 90
2. I run the snapshot agent
3. I PUSH a new subscription using the "Push Subscription Wizard", using all
the defaults.
So far I have not gotten this to work at all. I get one of two major errors.
1. Violation of Primary Key constraint 'tablename'. Cannot insert duplicate
key in object 'tablename'
2. (If I select "Drop the existing table and re-create it" when adding
articles) I get an error complaining that the table cannot be dropped
because of a foreign key relationship (which it does have).
3. If I try and add any data at the subscriber I get an error telling me I
have to run Sp_adjustpublisheridentityrange.
What is the correct process here.
1) can we get more info on the primary key violation. Where does this show
up? Do you see it in the conflict viewer?
2) Ideally your subscriber would be an empty database when applying the
subscriber. Can you drop the problem table on the subscriber before
distributing the snapshot.
3) Check the table on the subscriber and drop the replication check
constraint on the identity column.
"Robert Sheppard" <sheprts@.cox.net> wrote in message
news:%23xX47jx5FHA.884@.TK2MSFTNGP14.phx.gbl...
>I am having some trouble getting merge replication working. I have 1 server
> acting as a publisher and distributer and another server acting as my
> subscriber. Both are running SQL 2000 SP4.
> I need both the publisher and subscriber to be able to update the
> database.
> One database is for internal company apps and the other is for our web
> server both of which need access to the same database.
> The steps are as follows.
> 1. Create a new publication using the "Create Publication Wizare" and
> using
> all the defaults. When I come to the "Specify Articles" dialog I add my
> table articles using the defaults and a few minor changes as follows:
> - Keep the existing table unchanged.
> - Automatically assign and maintain a unique range for each
> subscription.
> - Range size at pub = 10000
> - Range size at sub = 10000
> - Threshold = 90
> 2. I run the snapshot agent
> 3. I PUSH a new subscription using the "Push Subscription Wizard", using
> all
> the defaults.
> So far I have not gotten this to work at all. I get one of two major
> errors.
> 1. Violation of Primary Key constraint 'tablename'. Cannot insert
> duplicate
> key in object 'tablename'
> 2. (If I select "Drop the existing table and re-create it" when adding
> articles) I get an error complaining that the table cannot be dropped
> because of a foreign key relationship (which it does have).
> 3. If I try and add any data at the subscriber I get an error telling me I
> have to run Sp_adjustpublisheridentityrange.
> What is the correct process here.
>
>
acting as a publisher and distributer and another server acting as my
subscriber. Both are running SQL 2000 SP4.
I need both the publisher and subscriber to be able to update the database.
One database is for internal company apps and the other is for our web
server both of which need access to the same database.
The steps are as follows.
1. Create a new publication using the "Create Publication Wizare" and using
all the defaults. When I come to the "Specify Articles" dialog I add my
table articles using the defaults and a few minor changes as follows:
- Keep the existing table unchanged.
- Automatically assign and maintain a unique range for each
subscription.
- Range size at pub = 10000
- Range size at sub = 10000
- Threshold = 90
2. I run the snapshot agent
3. I PUSH a new subscription using the "Push Subscription Wizard", using all
the defaults.
So far I have not gotten this to work at all. I get one of two major errors.
1. Violation of Primary Key constraint 'tablename'. Cannot insert duplicate
key in object 'tablename'
2. (If I select "Drop the existing table and re-create it" when adding
articles) I get an error complaining that the table cannot be dropped
because of a foreign key relationship (which it does have).
3. If I try and add any data at the subscriber I get an error telling me I
have to run Sp_adjustpublisheridentityrange.
What is the correct process here.
1) can we get more info on the primary key violation. Where does this show
up? Do you see it in the conflict viewer?
2) Ideally your subscriber would be an empty database when applying the
subscriber. Can you drop the problem table on the subscriber before
distributing the snapshot.
3) Check the table on the subscriber and drop the replication check
constraint on the identity column.
"Robert Sheppard" <sheprts@.cox.net> wrote in message
news:%23xX47jx5FHA.884@.TK2MSFTNGP14.phx.gbl...
>I am having some trouble getting merge replication working. I have 1 server
> acting as a publisher and distributer and another server acting as my
> subscriber. Both are running SQL 2000 SP4.
> I need both the publisher and subscriber to be able to update the
> database.
> One database is for internal company apps and the other is for our web
> server both of which need access to the same database.
> The steps are as follows.
> 1. Create a new publication using the "Create Publication Wizare" and
> using
> all the defaults. When I come to the "Specify Articles" dialog I add my
> table articles using the defaults and a few minor changes as follows:
> - Keep the existing table unchanged.
> - Automatically assign and maintain a unique range for each
> subscription.
> - Range size at pub = 10000
> - Range size at sub = 10000
> - Threshold = 90
> 2. I run the snapshot agent
> 3. I PUSH a new subscription using the "Push Subscription Wizard", using
> all
> the defaults.
> So far I have not gotten this to work at all. I get one of two major
> errors.
> 1. Violation of Primary Key constraint 'tablename'. Cannot insert
> duplicate
> key in object 'tablename'
> 2. (If I select "Drop the existing table and re-create it" when adding
> articles) I get an error complaining that the table cannot be dropped
> because of a foreign key relationship (which it does have).
> 3. If I try and add any data at the subscriber I get an error telling me I
> have to run Sp_adjustpublisheridentityrange.
> What is the correct process here.
>
>
Subscribe to:
Posts (Atom)