question regarding replication

[Iter]

I have two questions regarding replication.
1. I have replication that setup as non initial snapshot, and manually
initialize a pull subscription to a transactional publication. When I got
error in subscription and I need to remove the table from replicaiton. But
the error is still there after I removed the table. The transaction in
distribition is still trying to insert that row to the table in subscriber. I
rememeber it was ok when I removed the table when I use initial snapshot
replication. Can anyone help how to remove a table and transaction related to
that table? so the error don't continue?

2. I need to set indentity_insert on. As far as I know it only in the code
level. Does anyone knows how to set it in db level so that I don't need to
add it in many sps.

Thanks in advance.

[Ben Thul]

1. I'm confused by your scenario here. What is "manual"
initialization? Specifically, what value was used for @sync_type in
your call to sp_addsubscription? Further, what error did you
encounter and how did you remove the article from replication? I ask
because every time I've had any sort of an error with a given article
and I've made the choice to remove the article, any commands regarding
that article go away too. Or, at least the practical result is that
they do (i.e. the distribution agent doesn't try to deliver them
anymore).

2. Unless you need it otherwise, use a value of 'manual' for the
@identityrangemanagementoption parameter for your call to
sp_addarticle. This will "do the right thing" with regards to
identity columns at the subscriber.
--
Ben

[Iter]

I am using "replication support only" since I cannot generate snapshot and
initialize the subscription as two db is different.the error I got is related
to identity column since the user hasn't got the solution and cannot fix it
now. the error like
Explicit value must be specified for identity column in table 'Trro' either
when IDENTITY_INSERT is set to ON or when a replication user is inserting
into a NOT FOR REPLICATION identity column

So I tried to remove the table from replication, but the error was still
sent out even though I remove it.

Any idea about it? thanks

"Ben Thul" wrote:

> .
>

[Ben Thul]

So, if you call sp_helparticle on the article in question, does it
return anything? If so, what is the values for the schema_option and
identityrangemanagementoption columns?

But more importantly, 'replication support only' initialization only
applies in very specific circumstances. Specifically, when you can
guarantee that at the time the sp_addsubscriber statement is run that
the data and schema are identical. If they're not, you will encounter
issues where the distribution agent will try to run a statement
against data that doesn't exist (or something similar). If snapshots
aren't an option (and I still don't understand why they're not in your
specific case, but I'm moving past that), then initialization from
backup is a good option if you're in a SQL 2005+ environment.
--
Ben

[Iter]

Hi Ben,
Thank you very much for your reply. I would like to talk with you regarding
this. IN my situation, this is new project and the db in publisher and
db_hist in subsriber are transfered from sybase. db and db_hist are not
same and db_hist is history of db. db_hist is much larger than db. The
db_hist cannot be override by db in the future so I shouldn't use initial
snapshot and generate new snapshot. I considered to use manually initialize a
pull subscription('replication support only' ) to guarentee no new snapshot
generated to overide the db_hist in the future. Do you think it makes sense?
Do you have any sugguestion?
regarding the issue I mentioned, @schema_option = 0x000000000803509F,
@identityrangemanagementoption = N'none', do you think if it caused the
error continuing after I removed the table from replicaiton?
Thanks.

"Ben Thul" wrote:

> > > > error in subscription and I need to remove the table from replicaiton.. But

> > > > the error is still there after I removed the table. The transaction in
> > > > distribition is still trying to insert that row to the table in subscriber. I
> > > > rememeber it was ok when I removed the table when I use initial snapshot
> > > > replication. Can anyone help how to remove a table and transaction related to
> > > > that table? so the error don't continue?
> >
> > > > 2. I need to set indentity_insert on. As far as I know it only in the code
> > > > level. Does anyone knows how to set it in db level so that I don't need to
> > > > add it in many sps.
> >
> > > > Thanks in advance.
> >
> > > .
>

> .
>

[Ben Thul]

The sp_helparticle was a bit of a trick question. So, the fact that
it returned anything at all says that you didn't remove it from
replication. Try this (script your publication out through SSMS
first):

exec sp_removesubscription @publication = 'your pub here', @article =
'your article here'
exec sp_removearticle @publication = 'your pub here', @article = 'your
article here'

Then, when you go to re-add the article, use
@identityrangemanagementoption = 'manual'

Regarding your set up, I understand more where you're at now.
Essentially, you have a skinny OLTP and a fat archive. You want
replication to go between them and keep the parts that they have in
common in sync while leaving the old stuff alone. That's a tricky
setup. For instance, you have to be careful about how you replicate
deletes that happen at the publisher. If it were me, I might run a
slightly different topology: skinny OLTP, skinny subscriber, fat
archive where the archive is populated by a daily batch process (maybe
an SSIS package) from the skinny subscriber. You can get away without
the subscriber too, just running the archive update straight from the
OLTP. But, you can give replication a shot, too The advice above
should get you through your identity column issue.
--
Ben

[Iter]

Thank you Ben and your suguestion. We cannot change the architecture because
it transafered from Sybase and no time to change. we will do it in the future.
what is sp_removesubscription ? I cannot find it. Can you let me know the
exact name?
for @identityrangemanagementoption = 'manual', is this change the identity
column to "not for replication" automate? we cannot use this in our database.
The user emphasized the identity should be identicle in db and db_history. So
I decided to ask then to customize the replication stored procedure where put
identity_insert on there. Do you have any better sugguestion? Thanks.

"Ben Thul" wrote:

> .
>

[Ben Thul]

I mis-remembered the name of the drop subscription stored procedure:
it's sp_dropsubscription (it'd help if MS was more consistent in their
nomenclature...)

As to identity columns, here's the deal: when you insert a row to a
table that has an identity column at the publisher, it'll generate a
value for that column (let's say for the sake of this conversation
that it's "275"). What you care about is that the value 275 makes it
to the subscriber(s). It doesn't matter if that column is an identity
column at the subscriber or not. In fact, it doesn't have to be
unless you have something besides replication inserting data into that
table. If you do have something else inserting into that table, you
will have to figure out what to do about identity ranges. That is,
you'll have to somehow explicitly assign a range of identity values
that will be inserted via replication and those by other means.
However, if replication is the only thing doing DML at the subscriber,
just let it do it how it needs to.

Rather than just trusting that I know what I'm talking about (which
you shouldn't!), I encourage you to set up something that mimics as
closely as possible your situation in a throw-away environment. You
should be able to get away with a schema-only copy of both the
publisher and subscriber. Set up the replication topology as you
would in your prod environment and give it a spin. You'll either see
that the identity columns are handled properly or you won't. If you
do, we both get a gold star. And if you don't, then we can talk about
it a little more and see what happens. Godspeed! :)
--
Ben