Re: Inactive Publisher??????

Raymond,

No. The publisher name is in ALL CAPS.

Actually, I was able to get everything working by disabling publishing on
the publisher, disabling distribution on the distributor and then rebooting
the distributor. Once back up I set up distribution on the distributor and
made sure the other server was set up for publication.

Then on the publisher, I set up distribution by selecting the other
distributor server. The I created a publication and it all works.

However, I've run into another problem with a view that thinks it's a table.
Or at least the agent thinks so. I've outlined that in another thread
titled "DROP VIEW vs DROP TABLE".

Thanks for all your help.
--
Roger.


"Raymond Mak [MSFT]" wrote:

Roger, do you happen to have lower-case characters for the publisher name in
the msdb.dbo.MSdistpublishers table, I might have found a bug in out code.

-Raymond

"Roger Denison" <RogerDenison@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C59E05C4-3FD0-41AE-8CA5-8AF769D1729A@xxxxxxxxxxxxxxxx
Rayond,

After my last response I thought, "Duh! I can do my own selscts!" I
guess
I'm just lazy.

Anyway, MSdistpublishers table in msdb on the distributor server shows the
following:
name: <my publisher server>
distribution_db : distribution
working_directory: <default path to ReplData>
security_mode: 0
Login: sa (for simplicity I'm using sa. Once I have things nailed down
I
can provide tighter security)
password: <blah blah>
active: 0
trusted: 0
thirdparty_flag: 0
Publisher_type: MSSQLSERVER

The only sys.servers I find in master is a view. I see three records: one
for the distributor server (which will eventually be a subscriber), one
for
repl_distributor, and one for the publishing server.

The data source for the distributor server and repl_distributor is the
same,
as it should be. The only thing that looks weird is the configuration
settings for is_publisher, is_subscriber, and is_distributor. Only
repl_distributor has is_distributor=1. The other records are 0 for all
three
columns. Shouldn't the publisher server have is_publisher=1?

I'm also wondering how to get rid of two publications that don't exist,
i.e., they show up in my Local Publications folder on the publisher server
but when i try to delete them the error msg says they don't exist.

TIA.

--
Roger.


"Raymond Mak [MSFT]" wrote:

Can you run a direct select of those tables from the query window? I am
also
wondering if you initially setup replication under a low-privileged
account
(< admin on the distributor box) Thanks much.

-Raymond

"Roger Denison" <RogerDenison@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8096D091-8CE5-4C7D-96C7-F29EB7C7ABC8@xxxxxxxxxxxxxxxx
Thanks for your input, Raymond. You think things are messed up, I have
two
publications that I can't delete becasue they "don't exist". And I
can't
drop a couple of tables because they are used for replication by the
two
publishers that don't exist. But that's another issue. This has been
a
lot
of fun trying to work in 2005.

For starters, I haven't upgraded anything from 2000 to 2005. With my
latest
project, my IT guy purchased 2005 instead of 2000. I was able to
import
data
from one of my other servers so i wasn't starting from scratch. But
there
was no replication associated with the tables at that point.

I then imported data from the publisher to the subscriber so that the
schema
would already be present on the subscriber.

So I set up my subscriber as the distributor using the wizard. I then
created the publication on the publisher server. As you probably
guessed
there are 2 servers in this scenario. From the publication, I launch
the
New
Subscriber... wizard to create the subscription. I request the
publication
to start the snapshot and start the log reader (this is a trans rep).
The
wizard says that everything completed successfully but RepMon shows
errors.

Now, you ask me to look in some tables in Master or Distribution but I
cannot look at the data. Even though I am logged on as SA I cannot
open
any
of those ("open table" is grayed out) How do I go about getting access
to
the system tables?
--
Roger.


"Raymond Mak [MSFT]" wrote:

Roger, looks like your replication setup may be in a pretty bad state
(specifically the distributor doesn't seem to know anything about your
publisher). You may need to tear down all replication meta-data at
your
publisher (likely by using @ignore_distributor = 1 in various
replication
sp
calls). But before doing that, can you tell me how you upgrade the
distributor in the first place? You may also want to check the
sys.servers
table in the master database and the MSdistpublishers table in msdb on
your
distributor and see if there are any signs that the distributor
actually
knows about your publisher at this point.

-Raymond

"Roger Denison" <RogerDenison@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:FF68B0C8-7B2B-4E70-BAD0-197FC3CDC91C@xxxxxxxxxxxxxxxx
Raymond/Hillary,

I used the wizard that came with SQL2005 to set up the publisher and
subscription. I used the sp that Hillary gave me determine the
status.

Use Distribution
Execute sp_helppublication
Go

But all I saw was a successful completion. No results. Unlike
2000,
which
provided results, I'm still getting used to how 2005 works.

Likewise, I ran sp_changedistpublisher as Raymond specified but it
couldn't
find my publisher 'Autosort081 XRep'. It said to make sure it was
registered
in sysservers in the distributor. I can't find that table (or
anything
resembling) and even if I did I can't open it in Dist or Master.
Just
another thing I don't like about 2005.


I do have publishers in 2000 but they only publish to other 2000
servers,
not 2005.
--
Roger.


"Raymond Mak [MSFT]" wrote:

Hi Roger,

You can set the publisher to active by running
sp_changedistpublisher
@publisher = <your publisher name>, @property = 'active', @value =
'true'
at
the distributor, although this happens often enough
(http://groups.google.com/group/microsoft.public.sqlserver.replication/browse_frm/thread/cb0ab8eb51efbdc0/20d2368928450759?lnk=gst&rnum=41#20d2368928450759)
that I am a bit worried about what kind of subtle problems we
missed
during
replication setup through SSMS. Are you having a SQL2000 publisher
by
any
chance?

-Raymond

"Roger Denison" <RogerDenison@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:0EF738C9-9EE5-43C9-B19C-117280FAA1AB@xxxxxxxxxxxxxxxx
I set up a Tranactional replication configuration and created a
publication
in SQL2005. When I set up the subscriber the wizard stated all
succeeded.
However, when I went to the Replication Monitor there was a error
for
the
publisher. I went to the history for the agent and it stated
the
following:

2007-02-09 17:56:51.44 Connecting to Distributor 'U1ST081SORTS2'
2007-02-09 17:56:51.57 The replication agent had encountered an
exception.
2007-02-09 17:56:51.57 Source: Replication
2007-02-09 17:56:51.57 Exception Type:
Microsoft.SqlServer.Replication.ReplicationAgentException
2007-02-09 17:56:51.57 Exception Message: The snapshot could not
be
generated because the publisher is inactive.
2007-02-09 17:56:51.57 Message Code: 54057

Now, this is not only new to me but also to BOL because I
couldn't
find
anything about inactive "publishers", only subscribers. Also the
Message
code didn't provide any answers. I've found places to change
subscriber
inactivity timeouts but nothing for publishers.

Any ideas? I'm used to setting up replication in SQL2K and
haven't
had
this
many problems before. Any significant changes in how to set up
replication
with 2005?

--
Roger.












.

Relevant Pages

  • Re: SQL2000 - Merge Replication - Timeout error
    ... The timeout is in your publisher and distributor server, ... Then, update the database statistics. ... Run profiler on the subscriber to see what is going on? ...
    (microsoft.public.sqlserver.replication)
  • Re: Inactive Publisher??????
    ... MSdistpublishers table in msdb on the distributor server shows the ... Shouldn't the publisher server have is_publisher=1? ... I then imported data from the publisher to the subscriber so that the ...
    (microsoft.public.sqlserver.replication)
  • Re: Remote Dist - Snpsht / Trans Replication Fails to Create
    ... Has anyone created any previous publications on the publisher or ... Issue the Select @@servername query on the publisher and distributor. ... >> Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • transactional replication-mapping columns
    ... server A (publisher, distributor) ... server B (subscriber) ...
    (microsoft.public.sqlserver.replication)
  • Re: Problems replicating over internet....Still....
    ... to your Publisher to download the snapshot and transactions. ... We can't test connectivity using ping, but you can ftp from the subscriber ... go to Client network utility. ... Server, ...
    (microsoft.public.sqlserver.replication)
Loading