October 27, 2009 at 6:42 am
Hello everyone,
This is the first time , Im setting up a transactional replication. I have setup a Central publisher with remote distributor model of transactional replication. My test database have already one subscription to my test publisher with name MServer01. Now when im trying to create another subscription , Im receving an error stating Subscription with name Mserver01 already exists. Is it not possible to have more than one subscription in a database for different table?
ta
October 27, 2009 at 6:54 am
You can, you need a different name.
You can have multiple subscribers for a single publication, and you can have multiple publications, each with the same subscriber.
October 27, 2009 at 6:58 am
There is no option for providing new name for sucscription. Can you help me here. I received error 14040.. My Distributor is running SQL 2005 and Publisher is SQL 2000. Distributor also acts as subscriber...
October 27, 2009 at 7:20 am
did you create the second publication?
From there, are you looking for a push or pull subscription? I would think that you'd need to use SSMS since you have a 2005 distributor. There are instructions here: http://msdn.microsoft.com/en-us/library/ms152566%28SQL.90%29.aspx
October 27, 2009 at 7:44 am
Hi yeh,
yes, I have created second publication with pull subscription. What if I change this subscription to push?
I would like to explain my working scenaraio here.
I created Districbutor on Mserver01 which is running SQL 2005. For testing purpose I have designated this server as Subscribers.
My publisher is NServer01 running on SQL Server 2000.
When I created first publication on table 1 and subscribed to it to table 1(push subscription) on Mserver01 it worked fine.
Now, when I created new publication on table 2 on NServer and subscribed to table 2 on Mserver01 with pull subscription , I'm not able to create it and SQL errored with Subscription with name Mserver01 already exists.
As per your past reply , I assume you can have many publication mapped to a subscription. And your recent reply suggest that I need to use SSMS. This derives that all publication from the publisher to a same subscriber needs to be either push or pull not same. Is that correct?
ta
October 27, 2009 at 8:09 am
I haven't tried mixing, but I would assume it wouldn't matter. Each publication and subscription should be separate.
There are 3 servers involved. Some actual instance might perform 2 roles, but you have a publisher, distributor, and subscriber. I don't have a 2000 instance handy, but when you created the publisher and distributor, did you use EM? Also, why did you move the distributor to another server? It's fine, but it does make it slightly more complicated.
I think once the distributor is on SS2K5, you need to use SSMS to manage it. A "new subscription" is what I think you want to set up.
October 27, 2009 at 8:24 am
Hi,
I used EM to create publication. I moved Distributor to another server as it might overload my server in real scenario. I will try using SSMS and see how i get on and update this post
Ta
October 27, 2009 at 9:05 am
Hi,
Even my first publication is now throwing an error "Error 8526 Level 16: Cannot go remote while the session is enlisted in a distributed transaction that has an active savepoint. " . I think this is probably becoz of transaction compactability between SQL 2005 and earlier version. Can any one help?
October 27, 2009 at 9:47 am
On the publisher in the published database can you run select * from syspublications and post the output here.
On the subscriber in the subscription database run select * from syssubscriptions and post the output here as well.
I will probably need you to run a few more queries after that as well. Just trying to get a view into what you are replicating. Thanks.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 27, 2009 at 10:21 am
Hi David,
Im sorry result is unformatted as I have copied it from remote server.
Publisher output :
Transactional publication of CommerceServer database from Publisher NTPP12.,ShopVariant,3,0,1,0,0x407DE31B265D8F4BB476C52E26825B19,0,0,0,1,1,0,0,0,0,336,0,1,NULL,NULL,NULL,0,NULL,21,NULL,anonymous,NULL,0,0,NULL,14,NULL,NULL,NULL,10
Subscription Output
Msg 208, Level 16, State 1, Line 1
Invalid object name 'syssubscriptions'. - I assume this is probably becoz there is no active subscription on this database.
October 27, 2009 at 10:24 am
Hi David,
I have tested it with SQL Server 2005 as publisher and subscriber It works just fine. I think we need to resolve some compactability issue.
ta
October 27, 2009 at 10:29 am
The subscription query needs to be run on the subscriber server in the subscription database. That table has to exist there else the other subscription couldn't even be in place. Let me know what you find.
Thanks.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 27, 2009 at 10:30 am
Hi David,
Im not able to add SQL 2005 server as subscriber in SQL 2000 (publisher) Subscriber window.
ta
October 27, 2009 at 10:49 am
I was trying to create a first subscription on CDC TEsting database and having the problem . Apology if im not following your instruction properly.
October 28, 2009 at 3:17 am
David,
On running select * from Syssubscriptions following result is obatained
1-1virtual21Name00x000000000000000000000000000000000x000000000000ABFA0110
10CDCTesting21name00x85D7E4D0E819314A8DAE6B6AC8CEB2190x000000000000ABFC5100MSVRNET01
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply