can't "allow peer to peer" - SQL Server 2005

I'm trying to set up the first distribution server for a peer to peer transactional replication (updateable subscription) between two SQL Server 2005 instances on one machine (for testing). Can this be done without "virtual servers", and if so how do I enable the "Allow peer-to-peer subscriptions" option in the Subscription Options folder of the Publication Properties dialog box?

I've got:

Independent Distribution Agent True (not selectable)

Allow anonymous subscriptions False

Attachable subscription database False

Allow pull subscriptions False

Allow initialization from backup files True

Allow non-SQL Server Subscribers False

Allow data transformations False (not selectable)

Replicate schema changes True

Allow peer-to-peer subscriptions False (not selectable)

Allow immediate updating subscriptions True (not selectable)

Allow queued updating subscriptions True (not selectable)

Report conflicts centrally True

Conflict resolution policy Keep the Publisher

I've tried various combinations of options, but think these are correct. Also, the snapshot database is in a local (not network) directory; I got a warning on this, although in this special case the snapshot should be available to both instances.

Thanks...

I applied SQL Server 2005 SP1 and the problem went away!

Liston

Cannot update replication once live - have to recreate!

I have set up a transactional replication from SQL2000 (W2K) to SQL2005
(W2K3). Once it's running, if I add a new field to the publisher, it doesn't
appear in the subscriber tables. I have tried everything including
re-inititalising the job, stopping and starting agents - nothing works. All I
am left with is deleting then recreating the entire replication job which is
time consuming and annoying.
Somebody out there must have the answer to me inadequacy!
Thanks in advance,
Will, Leeds UK
Will,
I need to understand how you did this. How did you add the new field? Did
you use sp_repladdcolumn?
Also, when you say reinitializing the job I'm not sure what you mean. Do you
mean restart the snapshot agent? If so, this is not reinitialization. If the
subscription is marked for reinitialization, this is something different.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi, basically we replicate live databases onto a separate server for
reporting. Occasionally we modify the live database (add a new field say) and
need the replication job to reflect this change. At the moment, every time we
make a change we have to remove replication, delete the reporting database
copy, then re-add replication. The live databases are on SQL 2000 and
reporting copies on SQL2005.
I haven't tried sp_repladdcolumn yet. I have just been using various actions
from SQL Management Studio.
I am a little confused as to the 'official method' to achieve what I want!
What I do know is that our current method works BUT is very tedious...
Thanks
Will
"Paul Ibison" wrote:

> Will,
> I need to understand how you did this. How did you add the new field? Did
> you use sp_repladdcolumn?
> Also, when you say reinitializing the job I'm not sure what you mean. Do you
> mean restart the snapshot agent? If so, this is not reinitialization. If the
> subscription is marked for reinitialization, this is something different.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>

Cannot run snapshot agent from command line

I have a transactional replication running and it's running well.
But now i'm writing a script so I can start the Snapshot Agent from
distance. therefore I want to run it from the command prompt.
I'm using this command: C:\Program Files\Microsoft SQL Server\80\COM
\snapshot.exe -Publisher <server> -PublisherDB <pubDB> -Publication
<pub> -DistributorLogin <login> -DistributorPassword <pwd>
And this is my output:
==== Output start ======
Microsoft SQL Server Snapshot Agent 8.00.2039
Copyright (c) 2000 Microsoft Corporation
The process could not create file '\\<server>\ReplData\unc'.
Finished with exit code 2
==== Output end ======
When I start the agent from SQL Server itself it works fine.
the location of the snapshot folder is defined as '\\<server>
\ReplData' and (for testing now) this folder has full access.
Can somebody tell mee what;s going wrong and what I can do about it?
Can you create your command as text in a batch file then use RunAs (or log on
again if RunAs is disabled). The user to select is the sql server agent
login...
HTH,
Paul Ibison
|||You need to check to ensure that the share permissions are full for the
account which the SQL Server Agent runs under, or the account the snapshot
agent is proxied under, and the underlying security is full for the path and
all child objects.
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
"acsnaterse" <snaterse@.gmail.com> wrote in message
news:1184079506.481076.126000@.22g2000hsm.googlegro ups.com...
>I have a transactional replication running and it's running well.
> But now i'm writing a script so I can start the Snapshot Agent from
> distance. therefore I want to run it from the command prompt.
> I'm using this command: C:\Program Files\Microsoft SQL Server\80\COM
> \snapshot.exe -Publisher <server> -PublisherDB <pubDB> -Publication
> <pub> -DistributorLogin <login> -DistributorPassword <pwd>
> And this is my output:
> ==== Output start ======
> Microsoft SQL Server Snapshot Agent 8.00.2039
> Copyright (c) 2000 Microsoft Corporation
> The process could not create file '\\<server>\ReplData\unc'.
> Finished with exit code 2
> ==== Output end ======
> When I start the agent from SQL Server itself it works fine.
> the location of the snapshot folder is defined as '\\<server>
> \ReplData' and (for testing now) this folder has full access.
> Can somebody tell mee what;s going wrong and what I can do about it?
>
|||On 10 jul, 17:58, Paul Ibison <Paul.Ibi...@.Pygmalion.Com> wrote:
> Can you create your command as text in a batch file then use RunAs (or log on
> again if RunAs is disabled). The user to select is the sql server agent
> login...
> HTH,
> Paul Ibison
Hi,
Thanks! With the runas commando it works great!
Hint: in combination with Sanur (http://www.commandline.co.uk/
sanur_unsupported/index3.html) you can automate the password fill-in.