replication system stored procedures parameter defaults ?

Hi there

This is a pretty straight forward question.

When using sp_droparticle or sp_changepublication etc, basically any replication system stored procedure.

There are many parameters for these sp's basically all i want to know is if i provide the relavant paramaters,that is publication name, subscriber name , specific parameter i wish to change etc, are all the other paramters defaulted to the current publication/subscriber properties.

In other words sometimes i really dont know what to provide for all the parameters i am pretty sure as long as i provide the necessary ones the other ones are defaulted correctly, BOL is not 100% clear on all the parameters ?

ThanxHi Sean,

You are right. If you dont specify a parameter value in a stored procedure, it will get set to the default value.

But when you call a sp_change*** procedure, only parameters you have specified will be changed. Others will continue to be set to what they were (They will not be reset to the default values)

For Eg: you call:

sp_addpublication @.publication='testPub', @.allow_push='true', @.allow_pull='true', @.allow_anonymous='true'

[Note that defaults for @.allow_pull and @.allow_anonymous are false]

And now if you call:

sp_changepublication @.publication='testPub', @.allow_pull='false'

this call will only set @.allow_pull='false' and will not touch @.allow_anonymous. It has already been set to 'true' and it will continue to be true.|||Hi Mahesh

Yes that helps thanx.
I had a problem with sp_addarticle i did not specify push or pull because i thought it would look at the subscriber and stay at pull, but it automatically defaulted to push, which i found strange as the subscriber/publication relationship was pull ?

Thanx|||Hi Sean, subscription-type has nothing to do with sp_addarticle, did you reference the wrong stored procedure name in your post?

Assuming you meant to say sp_addsubscription, if you look it up in Books Online, for parameter @.subscription_type, the default is PUSH. This is the only place where you can assign a subscription to be push or pull.
|||Hi Greg

Yes sorry i meant sp_addsubscription.

replication question

Hi,
We basically want to have a second hot copy of our database and keep it
up-to-date via transactional replication (non-updating subsrcriber) We are
using SQL Server 7.0.
If we restore the latest backup of the published database onto the
subscriber so we have everything in sync to start, manual inserts on the
publisher database fail to replicate because of identity column insert
problems. When it trys to replicate the insert to our subsriber database, it
seems to blowup because the table on the subsriber has identity ids as well.
How do we get around this? We only want the identity columns on the
subsriber to function when we make the subscriber the live database in the
case that the publisher has problems. Otherwise during replication, we just
want the IDs origianlly issued on the publisher to replicate to the
subscriber as intended.
thanksRead BOL for details on this topic:
Managing Identity Values
You can manage identity values by:
a.. Allowing Microsoft® SQL ServerT 2000 replication to automatically
manage identity columns by dynamically allocating ranges of identity values
to the Publisher and all the Subscribers.
b.. Using the Transact-SQL NOT FOR REPLICATION option when defining the
identity column.
c.. Using a primary key other than the identity column (for example, a
composite key or a rowguid column), if an identity column is not necessary.
This strategy eliminates the overhead of managing identity columns on the
replicated data.
You have a few options as stated above. I'd suggest to use rowguid column if
you can, avoiding identity column in general, if possible, when it comes to
any type of replication. Otherwise, "NOT FOR REPLICATION" identity column
would be my 2nd pick. This will preserve the identity values coming from
Publisher, and Subscriber's indentity values won't get incremented via
replication.
However, once your production server goes down and your backup server goes
live, the Subscriber's ID values will increment because of INSERTS. Now, if
this column is your primary key, then there's a potential that ID values
from Subscriber's and Publisher's will collide at some point, unless you
plan ahead. Also, you have to think about sync this data back to the
Publisher once the machine is back online.
Hope it helps.
HH
"aaz" <aaz@.webcapacity.com> wrote in message
news:edCboTVjDHA.1740@.TK2MSFTNGP12.phx.gbl...
> Hi,
> We basically want to have a second hot copy of our database and keep it
> up-to-date via transactional replication (non-updating subsrcriber) We are
> using SQL Server 7.0.
> If we restore the latest backup of the published database onto the
> subscriber so we have everything in sync to start, manual inserts on the
> publisher database fail to replicate because of identity column insert
> problems. When it trys to replicate the insert to our subsriber database,
it
> seems to blowup because the table on the subsriber has identity ids as
well.
> How do we get around this? We only want the identity columns on the
> subsriber to function when we make the subscriber the live database in the
> case that the publisher has problems. Otherwise during replication, we
just
> want the IDs origianlly issued on the publisher to replicate to the
> subscriber as intended.
> thanks
>
>|||On the other hand, you could use log shipping instead,
problem solved.
Regards
John