Named subscription is greyed out

The system consists of one SQL 2000 Server (Distributor and Publisher) and
several subscribers running MSDE 2000 and Merge Replication. Every
Subscriber connects to the publisher using Pull Subscription, but the choice
of selecting "No, This is a named subscription" is grayed out when
establishing the subscription. I therefore have to pick Anonymous
subscription... Why?
You'll need to register the subscribers first.
With the MSDE subscriber connected to your publisher, in Enterprise Mgr:
right click Replication and select the Config. Publishers, Subscriber and
Distributors option. On the Subscribers tab, select the box next to the
Subscribers to register them. Enter the connecting account using the ...
button. Then re-create the pull subscription.
Had the same problem myself recently.
Steve Halfacre
Synapsis, UK
<hallvardsc@.hotmail.com> wrote in message
news:%23dPPwIeLEHA.340@.TK2MSFTNGP11.phx.gbl...
> The system consists of one SQL 2000 Server (Distributor and Publisher) and
> several subscribers running MSDE 2000 and Merge Replication. Every
> Subscriber connects to the publisher using Pull Subscription, but the
choice
> of selecting "No, This is a named subscription" is grayed out when
> establishing the subscription. I therefore have to pick Anonymous
> subscription... Why?
>
>
sql

Name Resolution and Dynamic IPs

Hi Guys - This may fall into a connection category, but I was wondering
if you had the same problem specially when dealing with replication.
One of our clients is having the following problems:
1) My client computers can see the server computer by name and/or by IP
address
2) My server computer cannot see my client computers by name, only by IP
address which is dynamic
3) Client computers have a dynamic IP address
4) Client computers have a machine name but this name cannot be resolved by
a DNS server
Is there any way around this problem? How does SQL Server's name get
resolved in this scenario?
Since we are using merge replication and we cannot configure it if the IP on
the client is dynamic, then the name must be resolved somehow, right?
Any help will be appreciated,
Maer
Maer,
AFAIK what you're talking about should be possible using pull subscriptions.
If there are any IP issues during setup you could use a HOSTS file (on the
publisher/distributor) to help out.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||The absolute best way to do this is to
1) configure your publication for anonymous subscribers
2) use ActiveX Controls to pull your subscription. ActiveX Controls have
DistributorNetwork/DistributorAddress and PublisherNetwork/PublisherAddress
properties which allow your Subscriber to connect to your Publisher on the
fly without having to use hosts files or Client Network Utility on each
Subscriber.
"Maer" <maer@.auditleverage.com> wrote in message
news:uZ9xkrS6FHA.3976@.TK2MSFTNGP15.phx.gbl...
> Hi Guys - This may fall into a connection category, but I was wondering
> if you had the same problem specially when dealing with replication.
> One of our clients is having the following problems:
> 1) My client computers can see the server computer by name and/or by IP
> address
> 2) My server computer cannot see my client computers by name, only by IP
> address which is dynamic
> 3) Client computers have a dynamic IP address
> 4) Client computers have a machine name but this name cannot be resolved
> by a DNS server
> Is there any way around this problem? How does SQL Server's name get
> resolved in this scenario?
> Since we are using merge replication and we cannot configure it if the IP
> on the client is dynamic, then the name must be resolved somehow, right?
> Any help will be appreciated,
> Maer
>
>
|||I think you got a DNS problem.
If you are using VPN you can consider changing your VPN properties so it
registers in the DNS server or fixing the IP Address for the dial up user.
Also, using pull anonymous subscriptions your publisher does not need to be
to 'get' to your subscribers.
Jos.
"Maer" <maer@.auditleverage.com> wrote in message
news:uZ9xkrS6FHA.3976@.TK2MSFTNGP15.phx.gbl...
> Hi Guys - This may fall into a connection category, but I was
wondering
> if you had the same problem specially when dealing with replication.
> One of our clients is having the following problems:
> 1) My client computers can see the server computer by name and/or by IP
> address
> 2) My server computer cannot see my client computers by name, only by IP
> address which is dynamic
> 3) Client computers have a dynamic IP address
> 4) Client computers have a machine name but this name cannot be resolved
by
> a DNS server
> Is there any way around this problem? How does SQL Server's name get
> resolved in this scenario?
> Since we are using merge replication and we cannot configure it if the IP
on
> the client is dynamic, then the name must be resolved somehow, right?
> Any help will be appreciated,
> Maer
>
>

name of instance

I just setup a new database machine... in my home environment
and want to test replication and subscribion
I am using windows 2003 server and sql 2000 server,
i have create a instance name while installion... but after around 15
days...
the instance name automatic changed to "local".. and then I can not create a
replication anymore because the sql server said that "local" can as a name
of distributor ...
any anyone tell me what i can/should do?
Many Thanks..
Thanks in advanced.
Hi
If the instance is called (local) this would imply a default instance and
not a named instance! You can refer to a default instance by the machine name.
John
"beachboy" wrote:

> I just setup a new database machine... in my home environment
> and want to test replication and subscribion
> I am using windows 2003 server and sql 2000 server,
> i have create a instance name while installion... but after around 15
> days...
> the instance name automatic changed to "local".. and then I can not create a
> replication anymore because the sql server said that "local" can as a name
> of distributor ...
> any anyone tell me what i can/should do?
> Many Thanks..
> Thanks in advanced.
>
>

name of instance

I just setup a new database machine... in my home environment
and want to test replication and subscribion
I am using windows 2003 server and sql 2000 server,
i have create a instance name while installion... but after around 15
days...
the instance name automatic changed to "local".. and then I can not create a
replication anymore because the sql server said that "local" can as a name
of distributor ...
any anyone tell me what i can/should do?
Many Thanks..
Thanks in advanced.Hi
If the instance is called (local) this would imply a default instance and
not a named instance! You can refer to a default instance by the machine nam
e.
John
"beachboy" wrote:

> I just setup a new database machine... in my home environment
> and want to test replication and subscribion
> I am using windows 2003 server and sql 2000 server,
> i have create a instance name while installion... but after around 15
> days...
> the instance name automatic changed to "local".. and then I can not create
a
> replication anymore because the sql server said that "local" can as a name
> of distributor ...
> any anyone tell me what i can/should do?
> Many Thanks..
> Thanks in advanced.
>
>

name of instance

I just setup a new database machine... in my home environment
and want to test replication and subscribion
I am using windows 2003 server and sql 2000 server,
i have create a instance name while installion... but after around 15
days...
the instance name automatic changed to "local".. and then I can not create a
replication anymore because the sql server said that "local" can as a name
of distributor ...
any anyone tell me what i can/should do?
Many Thanks..
Thanks in advanced.Hi
If the instance is called (local) this would imply a default instance and
not a named instance! You can refer to a default instance by the machine name.
John
"beachboy" wrote:
> I just setup a new database machine... in my home environment
> and want to test replication and subscribion
> I am using windows 2003 server and sql 2000 server,
> i have create a instance name while installion... but after around 15
> days...
> the instance name automatic changed to "local".. and then I can not create a
> replication anymore because the sql server said that "local" can as a name
> of distributor ...
> any anyone tell me what i can/should do?
> Many Thanks..
> Thanks in advanced.
>
>

mysql replication

1 Way trans. replication to a mysql database
SQL Server Env:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Windows NT 5.0 (Build
2195: Service Pack 4)
Distribution Agent:
Error Details:
Invalid cursor state
(Source: ODBC Driver Manager (ODBC); Error number: 24000)
Last Command:
select xactts, subguid from MSrepl7 where pubsrv = ? and pubdb = ? and
indagent = 0 and subtype = 0
I've searched the newsgroups but this doesn't apply to my build ver.
http://support.microsoft.com/default.aspx?kbid=831997
I'm logging in as a mysql super user, the remote tables have been created.
I'm using the newest MySQL ODBC 3.51 Driver
The problem recurs when you stop and start the agent and server.
are you replicating to a heterogeneous subscriber? There was a bug
associated with replicating to mysql where it would work until the
distribution agent failed or stopped. On restart you would get an error.
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
"JD" <test@.test.com> wrote in message
news:%23yDKDHtFFHA.1936@.TK2MSFTNGP14.phx.gbl...
> 1 Way trans. replication to a mysql database
> SQL Server Env:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Windows NT 5.0 (Build
> 2195: Service Pack 4)
> Distribution Agent:
> Error Details:
> Invalid cursor state
> (Source: ODBC Driver Manager (ODBC); Error number: 24000)
> ----
--
> --
> Last Command:
> select xactts, subguid from MSrepl7 where pubsrv = ? and pubdb = ? and
> indagent = 0 and subtype = 0
>
> I've searched the newsgroups but this doesn't apply to my build ver.
> http://support.microsoft.com/default.aspx?kbid=831997
> I'm logging in as a mysql super user, the remote tables have been created.
> I'm using the newest MySQL ODBC 3.51 Driver
> The problem recurs when you stop and start the agent and server.
>
>
|||Yes, I beleive that replication to mysql is only possible via a
heterogeneous subscriber
Is there anything I can tune or workaround to get this going ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OeBrYx2FFHA.1264@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> are you replicating to a heterogeneous subscriber? There was a bug
> associated with replicating to mysql where it would work until the
> distribution agent failed or stopped. On restart you would get an error.
> --
> 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
> "JD" <test@.test.com> wrote in message
> news:%23yDKDHtFFHA.1936@.TK2MSFTNGP14.phx.gbl...
> ----
> --
created.
>
|||I've tried also tried it (just now) with snapshot replication...same deal
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OeBrYx2FFHA.1264@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> are you replicating to a heterogeneous subscriber? There was a bug
> associated with replicating to mysql where it would work until the
> distribution agent failed or stopped. On restart you would get an error.
> --
> 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
> "JD" <test@.test.com> wrote in message
> news:%23yDKDHtFFHA.1936@.TK2MSFTNGP14.phx.gbl...
> ----
> --
created.
>
|||My research indicates that currently there is not a
workaround for this issue. Hilary is correct, there is a
known issue with the distribution agent code. The
product team is looking at fixing this issue in the
future service packs but there is no specific timeline
right now.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>From: "JD" <test@.test.com>
>References: <#yDKDHtFFHA.1936@.TK2MSFTNGP14.phx.gbl>
<OeBrYx2FFHA.1264@.TK2MSFTNGP12.phx.gbl>
>Subject: Re: mysql replication
>Date: Sun, 20 Feb 2005 19:00:30 -0500
>Lines: 57
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
>Message-ID: <ecUB3c6FFHA.3840@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: 69.158.71.17
>Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGP08.
phx.gbl!tk2msftngp13.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl
microsoft.public.sqlserver.replication:59788
>X-Tomcat-NG: microsoft.public.sqlserver.replication
>Yes, I beleive that replication to mysql is only
possible via a
>heterogeneous subscriber
>Is there anything I can tune or workaround to get this
going ?
>
>
>"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in
message[vbcol=seagreen]
>news:OeBrYx2FFHA.1264@.TK2MSFTNGP12.phx.gbl...
There was a bug[vbcol=seagreen]
work until the[vbcol=seagreen]
would get an error.[vbcol=seagreen]
Windows NT 5.0 (Build[vbcol=seagreen]
24000)[vbcol=seagreen]
--[vbcol=seagreen]
= ? and pubdb = ? and[vbcol=seagreen]
to my build ver.[vbcol=seagreen]
http://support.microsoft.com/default.aspx?kbid=831997[vbcol=seagreen]
tables have been[vbcol=seagreen]
>created.
agent and server.
>
>

My merge replication is going haywire!

Hi guys, hoping someone can help me! Im desperate now.
I have a SQL Server 2005 database, configured for merge replication on a 4gb database to a SQL Server 2000 server. All was working fine and then yesterday everything stopped because the distribution database has reached 67Gb (Yes gigabytes) filling up the 80Gb C drive.
Now, Ive tried deleteing the subscription but it wont let me as there isnt enough disk space. Is there a way that I can manually truncate data in the distribution. I know it will prob break replication but I want to rebuild the entire replication set up now and just get it working as soon as possible.

Can someone please help! Im nearly out of hair to pull out!

Shane

I'm confused why the distribution database is so large since merge replication barely touches the distribution database with exception to the history tables. Are you sure it's the distribution database and not the published database? And which part of the database is taking up space - is it the data file or the log file?

If it's the distribution database, and assuming it's the data file, can you tell me what tables are taking up all the space, SSMS should have some reports you can run that will show you this. If it's the published database, that would make sense since there are a bunch of metadata tables. You might be able to change the retention to something smaller and then manually run metadata cleanup jobs to remove metadata. However I would change the retention rate little by little, otherwise the larger it is, the more log space at once it might consume and you'll be back to square one, running out of disk space.

If it's the log file, your best bet would be backup the log to some network share, then shrink the file.|||Thanks for your reply Greg.
Its definately the distribution database. The distribution.LDF file is now 59GB and the distribution.mdf file is 12Gb, I dont understand what is going on!
There is only one table in the databas ewith many records, MSMergeHistory, with about 6 million records, seems a lot but then again maybe not. The database being replicated is our main intranet database used by 140 people daily.

Anyways, what can you suggest I do?
|||I should add that the distribution database recovery model is set to simple, so Im a little confused as to how this could happen. ie the log files growing so much...
|||Is it possible for me to truncate that MSMergeHistory table and then shrink perhaps? Or move the distribution to another 1Tb disk NAS I have attached. At the moment I cant to anything with the machine as this one DB is hogging all the space so Im lost in trying to find a way to find space. Every time I delete something the database expands immediately, Ive now got a bare C drive with only Windows and these databases. So frustrating. Any ideas or thoughts are greatly appreciated, Im desperate to get this working. Im now to the point of considering uninstalling and reinstalling SQL Server which is ridiculous...
|||

I agree it's ridiculous, however i don't believe 6 million rows in the history table would take up so many GB. Can you run those reports in Object Explorer which can tell you what tables take up what space to verify it's just the history table?

Let's assume it is the history table, then you can change the history retention so it's more aggressive to clean it up.

As for why your distribution log isn't truncating, I can't answer that, you'll have to do more investigating. There are articles in msdn that detailing what causes this to happen, log could be continuously growing, corruption ,etc. As I mentioned earlier, do a log backup to your NAS to free of the log space, then shrink the file to free of disk space.

|||The problem I have is that I cant run *anything* because I get Out of disk space errors. Ive tried stopping sql server and manually moving the mdf and LDF files to another disk but thats not helped. I will try moving them back now, doing a recovery but change the location to recover to. Maybe that way I can start running queries to reduce stuff.

|||Then attach your network storage and add another data file or log file to the distribution database.|||OK, Ive got the database currently restoring to a much bigger disk.

The results of sp_helpfile and spaceused are:

name fileid filename filegroup size maxsize growth usage
-- - --
distribution 1 E:\SQLRoot\Data\distribution.MDF PRIMARY 12012160 KB Unlimited 1024 KB data only
distribution_log 2 E:\SQLRoot\Data\distribution.LDF NULL 59653312 KB 2147483648 KB 10% log only

database_name database_size unallocated space
--
distribution 69985.81 MB 20.97 MB

reserved data index_size unused
11990688 KB 11299568 KB 687784 KB 3336 KB

|||i could have sworn I said to back up the distribution log to the NAS, then shrink it. DId that not work?|||

Are you using merge agent in a continuous mode?

can you move the distribution database to the SAN: checkout: http://msdn2.microsoft.com/en-us/library/ms345408.aspx for more info.

|||Backing up the log did nothing to the log on size of the log, once it was backed up I tried to shrink it but still no joy, because I was out of disk space it couldnt execute anything.

I have just now resolved the issue, by backing up the entire Distribution to the SAN, then doing a restore but changing the restoe location to the NAS drive (that had 1 terabyte free), once that was done I had free space on the C, room for the Distribution to expand while I ra\n the scripts to drop all replication.

The scripts I ran in order were then
1, Manually remove the Subscription,
2 Manually remove the Publication
3 Shrink the Distribution database
4 Run Exec sp_removedbreplication, then add it again as a replicated database to repair all the sysmergearticle tables etc
5 Ran my scripts then for recreating and initializing the Pub/Sub and its currently bulk copying.

As yet I still dont know why this happened but it seems to have been corrupted so rebuilding looks to have fixed although will know for sure ina day or 2 after the database has been used in anger.

Anyways, thanks for all the help and advice. This is a great resource and will be sure to return the favour one day :-)