Re: Inactive Publisher??????

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: 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)
  • Re: Upgrading merge replication to 2005
    ... Subscriber to a merge publication can be any version no later than the ... So it would work with a SQL 2000 publisher and SQL 2000 ... Subscriber, but not a SQL 2005 Subscribers. ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
Loading