sql server replication: John,

Try:
Use Master
go
Select @@Servername

This should return your current server name but if it
returns NULL then try:

Use Master
go
Sp_DropServer 'OldName'
GO
Use Master
go
Sp_Addserver 'NewName', 'local'
GO
Stop and Start SQL Services

HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

SqlSvr 2000 on WXP Pro

All on a single server, is it possible to setup one database to replicate to
another database ?

SEM > Replication (right-click) > Configure Publishing, Subscribers, and
Distribution ... >

proceed through the wizard accepting the default choices, get the error:

SEM could not configure {machine-name} as the distributor for {machine-name}

Error 18483 : Could not connect to server {machine-name} because
distributor-admin is not defined as a remote login at the server.

Hi Paul, I've ordered the book ...

But you may have misread my question.

For testing purposes, I am trying to setup replication between two databases
on the same server ... Server1.Database1 is the publisher/distributer and
Server2.Database2 is the subscriber.

Is this possible?

[quoted text, click to view]

also, executing in Query Analyzer

Use Master
go
Select @@Servername

hangs indefinitely

John,
to answer your previous question - yes, it is definitely possible.

Something strange with the query you're executing. You may need to execute
the query below. Have a look in sysservers to identify which servername to
use as the 'oldname' part (IIRC, srvstatus = 0).

Use Master
go
Sp_DropServer 'ldName'
GO
Use Master
go
Sp_Addserver 'NewName', 'local'
GO
Stop and Start SQL Services

Rgds,
Paul Ibison

John,
using the subscription wizard, you should be able to select the publication
server and then the subscription database. As far as I know, the publishing
server doesn't need to be enabled as a subscriber - it is by default.
Perhaps it's different with a remote distributor - is that your scenario? In
this case right-click the replication monitor and enable the publisher
server as a subscriber.
Rgds,
Paul Ibison

Paul -- Sorry to be so dense, but (on a single server) I don't see how to
setup up transactional replication from one database to another.

How to do this in SEM ? How to do this in T-SQL ?


[quoted text, click to view]

Yes the server is the distributor, and a publisher, and a subscriber. I
have Database1 configured as a transactional publication database (all
articles), and Database2 configured as a subscription database with the
distributor configured to run continuously.

However, under SEM > Replication > Subscriptions > {subscription name}
(right-click) > Job History > click Refresh

but all the messages say "The initial snapshot for article 'Table1' is not
yet available."

I go to Publications > {publication name} > Properties > Status > click "Run
Agent Now"

and the agent runs fine ("Last Run" now shows a time) but still the
subscriber Job History only shows "The initial snapshot for article 'Table1'
is not yet available."


[quoted text, click to view]

John,
check to see if the job owner is sa.
Also, please check to see if the snapshot files have actually been created.
Finally please check that select @@servername is correct.
Rgds,
Paul Ibison

Hmmm .... interesting.

* Today, I go to SEM > Replication > Subscriptions > {subscription name}
(right-click) > Job History > click Refresh

and there are three series (multiple entries) of jobs listed. Each job in
a series begins at the same time.

1. first series: "Run at" = "5/3/2005 11:13 AM" , "Result" = "In Progress",
"Run Duration" = elapsed time until now

2. second series: "Run at" = "5/3/2005 4:36 PM" , "Result" = "In Progress",
"Run Duration" = elapsed time until now

3. third series: "Run at" = "5/3/2005 4:51 PM" , "Result" = "In Progress",
"Run Duration" = elapsed time until now

I think the queue overran so I can't see the initial jobs for the first
series. But for the second and third series, the"Errors and/or messages
...." are as follows:

"Connecting to Subscriber {machine name}"
"Connecting to Distributor {machine name}"
"Initializing"
"No replicated transactions are available"
"No replicated transactions are available"
etc.

If I go to "Replication" > "Publications" > {publication name} > Explore the
Latest Snapshot Folder, I receive the message "The snapshot files either
have not been generated or have been cleaned up.-"


* In Query Analyzer, "select @@servername" always returns {machine-name}


* As far as Job Owner, I'm not sure what you mean ... here's my best guess:

SEM > Replication > Publications > {publication name} (right-click) >
Properties > Status tab > Agent Properties > General > Owner = {logged-on
domain account}

{logged-on domain account} is a memeber of the Local Administrators group,
and is configured with "Log on as a service" rights.

The only relevant Services I see started are MSSQLSERVER (running under
"Local System"), SQLSVRAGENT (running under the {logged-on domain account}.

In Task Manager, the only process name I see that could possibly be
relevant is the distrib.exe process (running under {logged-on domain
account}.




[quoted text, click to view]

John,
please can you set the snapshot agent job's owner to sa. Check that the sql
server agent service is running under an account that has local admin
rights. Run the snapshot agent, then check the history of the agent and
confirm whether or not snapshot files have been generated.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)