Newbie question

Is it necessary for the table structure on the publisher from whom an article will be updated on the subscriber, have the same structure as the subscriber table?
Wilma,
replication can be set up like this (nosync), but it is not necessary. The
simplest method is to have a snapshot applied on the subscriber - a process
referred to as initialization. In this case nothing needs to be on the
subscriber prior to setting off the publication.
Regards,
Paul Ibison
|||Thankyou Paul

Newbie Needs Help ;)

Hello,
I'm new to replication. I have got a subscriber, publisher
ans distributer working ok, so now does the snapshot
replication, then the transactional ones.
It was working fine for 5 minutes then I had the following
error
Cannot update identity column 'ID'.
{CALL sp_MSupd_tblPerson
(NULL,0,NULL,NULL,NULL,NULL,NULL,' ',1,0,7146,0x82
03)}
ID is a primary key with an Identity setting get to 'Yes
but not for replication'.
Any pointers please
Peter
Peter,
are you using transactional with queued updating subscribers? If it is
standard transactional or transactional with updating subscribers, then on
the subscriber there should be no identity property, as the publisher will
manage the values.
Regards,
Paul Ibison
|||run this script on your subscriber.
sp_configure 'allow updates', 1
GO
reconfigure with override
GO
update syscolumns set colstat = colstat | 0x0008 where colstat & 0x0001 <> 0
and colstat & 0x0008 = 0
GO
sp_configure 'allow updates', 0
GO
reconfigure with override
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eCvcKcGNEHA.2704@.TK2MSFTNGP10.phx.gbl...
> Peter,
> are you using transactional with queued updating subscribers? If it is
> standard transactional or transactional with updating subscribers, then on
> the subscriber there should be no identity property, as the publisher will
> manage the values.
> Regards,
> Paul Ibison
>
|||If you want to do this ( ie: keeping identify columns on the subscribers
)_INS
you can do this:sp_MSupd_tblPerson
edit
this proc:
sp_MSupd_tblPerson
and remove the case where the pk_key column is updated ( ths will never
happen anyway )
and this proc:
sp_MSins_tblPerson
add
SET IDENTITY_INSERT "sp_MSins_tblPerson" on
here, replace the insert statement with something like
insert into "tblPerson" ("col1",... ) VALUES (@.param1, ... )
SET IDENTITY_INSERT "sp_MSins_tblPerson" off
Peter wrote:
> Hello,
> I'm new to replication. I have got a subscriber, publisher
> ans distributer working ok, so now does the snapshot
> replication, then the transactional ones.
> It was working fine for 5 minutes then I had the following
> error
> Cannot update identity column 'ID'.
> {CALL sp_MSupd_tblPerson
> (NULL,0,NULL,NULL,NULL,NULL,NULL,' ',1,0,7146,0x82
> 03)}
> ID is a primary key with an Identity setting get to 'Yes
> but not for replication'.
> Any pointers please
> Peter
|||while I agree that using set identity_insert in your insert procs will solve
this problem, you would be better to partition using different seeds and
probably increments on your publisher and subscriber, and put the Not For
Replication option on your identity columns on the Subscriber.
The primary key's are sometimes updated - normally this is a sign of poor
database design, however it is the default behavior of the datagrid control.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Olivier" <olivierwarez@.netscape.net> wrote in message
news:0Xcnc.43829$zm5.21011@.nntpserver.swip.net...
> If you want to do this ( ie: keeping identify columns on the subscribers
> )_INS
> you can do this:sp_MSupd_tblPerson
> edit
> this proc:
> sp_MSupd_tblPerson
> and remove the case where the pk_key column is updated ( ths will never
> happen anyway )
> and this proc:
> sp_MSins_tblPerson
> add
> SET IDENTITY_INSERT "sp_MSins_tblPerson" on
> here, replace the insert statement with something like
> insert into "tblPerson" ("col1",... ) VALUES (@.param1, ... )
> SET IDENTITY_INSERT "sp_MSins_tblPerson" off
>
>
> Peter wrote:
>
|||
> while I agree that using set identity_insert in your insert procs will solve
> this problem, you would be better to partition using different seeds and
> probably increments on your publisher and subscriber, and put the Not For
> Replication option on your identity columns on the Subscriber.
In this case there is one publisher and one subscriber, and the 'not for
replication' is set

> The primary key's are sometimes updated - normally this is a sign of poor
> database design, however it is the default behavior of the datagrid control.
It is not possible to update the identity column. Usually the identity
column is the primary key one
By the, what does
update syscolumns set colstat = colstat | 0x0008 where colstat & 0x0001 <> 0
and colstat & 0x0008 = 0
do exactly?

newbie can't drop Table as table is in use

newbie to transactional replication (replication in general). I have one
server that is publisher/distributor. Another server as subscriber. both
sql 2000 sp4. I have setup replication and when trying to apply the initial
snapshot it says that it can't drop table as the table is in use. I have
restored the same database on both servers and are now trying to replicate.
Is this correct or do I just have a blank database on the subscriber side and
let the replication create everything?
Unless it is a specific requirement (eg low bandwidth or huge amount of
data) I'd not use a nosync method as a default. In other words for your case
you could have an empty database on the subscriber and just allow the
initialization process to set things up for you.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the help Paul. So I should drop the database that I have
restored, create a snapshot, let that replicate and go from there?
"Paul Ibison" wrote:

> Unless it is a specific requirement (eg low bandwidth or huge amount of
> data) I'd not use a nosync method as a default. In other words for your case
> you could have an empty database on the subscriber and just allow the
> initialization process to set things up for you.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Yes - the easiest thing is to drop the database then create a new one with
the same name. This can be done using the subscription wizard (the creation
of a new database). After that, the initialization process will create the
objects and data you need.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I think you should try to find out how the table is in use. What happens if
you issue a sp_lock and then look at the list of id's returned. Use
sp_helpdb to identify your database, and then use object_name(ObjId) to
identify the tables. Then notice the spid. Use sp_who2 to identify it.
Hilary Cotter
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
"Dgragg" <Dgragg@.discussions.microsoft.com> wrote in message
news:FE9D526C-EE55-44A1-8F58-1B8384E1DF11@.microsoft.com...
> newbie to transactional replication (replication in general). I have one
> server that is publisher/distributor. Another server as subscriber. both
> sql 2000 sp4. I have setup replication and when trying to apply the
> initial
> snapshot it says that it can't drop table as the table is in use. I have
> restored the same database on both servers and are now trying to
> replicate.
> Is this correct or do I just have a blank database on the subscriber side
> and
> let the replication create everything?