about transactional replication

Hi,
I have created a transactional replication on SQL Server 2000 with SP3. Only
one database is being published with 70 tables.
But when it begins to replicate data the following errors occured
""The process could not bulk copy into table 'tablename' ""
Violation of PRIMARY KEY constraint 'tablename_PKEY'. Cannot insert
duplicate key in object 'tablename'.
This table has a PKEY and it's not identity and this table also related with
other tables
This question is asked before but i have still no idea to solve this problem
without deleting the relationships of that table.
Please tell the solution step by step,
Best regards...
its hard to say what is going on here. There are a couple of possibilities:
1) data already exists at the subscriber and you are not deleting it before
sending over a new snapshot.
2) you are replicating two or more articles to the same object on the
subscriber.
3) you are getting a different error message than the one posted.
Are you replicating the DRI of these tables, or are you replicating to a
database where the schema already exists? If so you will need to send a
pre-snapshot command which disables all DRI, and a post snapshot command
which re-enables 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
"Ugur Ekinci" <ugur@.argeset.com> wrote in message
news:OUdrkjtKFHA.2688@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have created a transactional replication on SQL Server 2000 with SP3.
Only
> one database is being published with 70 tables.
> But when it begins to replicate data the following errors occured
> ""The process could not bulk copy into table 'tablename' ""
> Violation of PRIMARY KEY constraint 'tablename_PKEY'. Cannot insert
> duplicate key in object 'tablename'.
> This table has a PKEY and it's not identity and this table also related
with
> other tables
> This question is asked before but i have still no idea to solve this
problem
> without deleting the relationships of that table.
> Please tell the solution step by step,
> Best regards...
>
|||Hi Mr. Hilary,
Yes data already exists at the subscriber, how can i provide to delete
record before replicate it ?
how can I disable DRI and reenable it after replicaiton finished ?
It would be great if you give an example,
Best regards...
"Hilary Cotter" <hilary.cotter@.gmail.com>, haber iletisinde unlar
yazd:ulKrEeuKFHA.3616@.TK2MSFTNGP09.phx.gbl...
> its hard to say what is going on here. There are a couple of
> possibilities:
> 1) data already exists at the subscriber and you are not deleting it
> before
> sending over a new snapshot.
> 2) you are replicating two or more articles to the same object on the
> subscriber.
> 3) you are getting a different error message than the one posted.
> Are you replicating the DRI of these tables, or are you replicating to a
> database where the schema already exists? If so you will need to send a
> pre-snapshot command which disables all DRI, and a post snapshot command
> which re-enables 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
> "Ugur Ekinci" <ugur@.argeset.com> wrote in message
> news:OUdrkjtKFHA.2688@.TK2MSFTNGP15.phx.gbl...
> Only
> with
> problem
>
|||It's ok, thanks Mr. Hilary,
Since i checked the "delete data in the existing table..." from publication
properties , that error does not occur ,
But I need to know that can we disable DRI with SP_ commands and enable it
again ?
Best regards...
"Hilary Cotter" <hilary.cotter@.gmail.com>, haber iletisinde unlar
yazd:ulKrEeuKFHA.3616@.TK2MSFTNGP09.phx.gbl...
> its hard to say what is going on here. There are a couple of
> possibilities:
> 1) data already exists at the subscriber and you are not deleting it
> before
> sending over a new snapshot.
> 2) you are replicating two or more articles to the same object on the
> subscriber.
> 3) you are getting a different error message than the one posted.
> Are you replicating the DRI of these tables, or are you replicating to a
> database where the schema already exists? If so you will need to send a
> pre-snapshot command which disables all DRI, and a post snapshot command
> which re-enables 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
> "Ugur Ekinci" <ugur@.argeset.com> wrote in message
> news:OUdrkjtKFHA.2688@.TK2MSFTNGP15.phx.gbl...
> Only
> with
> problem
>

About db reindex

I just read an artice about database reindex published by microsoft
corporation, which states that the performance after doing db reindex is not
better than before defragment in OLTP system. In DSS system, it is faster
than before defragment. I got confused by this article. Do I need to do the
db reindex or defragment in OLTP system? Thanks.
To add on to Gert-Jan's response, defragmenting indexes can improve buffer
efficiency and reduce I/O increasing data density. I have seen noticeable
OLTP performance improvement by reorging clustered indexes.
Hope this helps.
Dan Guzman
SQL Server MVP
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:6CD68521-EE02-4578-97C2-C2926B3D869F@.microsoft.com...
>I just read an artice about database reindex published by microsoft
> corporation, which states that the performance after doing db reindex is
> not
> better than before defragment in OLTP system. In DSS system, it is faster
> than before defragment. I got confused by this article. Do I need to do
> the
> db reindex or defragment in OLTP system? Thanks.
>

About db reindex

I just read an artice about database reindex published by microsoft
corporation, which states that the performance after doing db reindex is not
better than before defragment in OLTP system. In DSS system, it is faster
than before defragment. I got confused by this article. Do I need to do the
db reindex or defragment in OLTP system? Thanks.Iter wrote:
> I just read an artice about database reindex published by microsoft
> corporation, which states that the performance after doing db reindex is not
> better than before defragment in OLTP system. In DSS system, it is faster
> than before defragment. I got confused by this article. Do I need to do the
> db reindex or defragment in OLTP system? Thanks.
Well, it is definitely a generalization. What they probably meant, is
that most access in an OLTP system are seeks for exact matches. In that
case fragmentation is not much of a problem, because the accessed index
pages and data pages aren't adjacent anyway.
If you have an OLAP type system, you are likely to request large
datasets with many consecutive pages. In that case these will be
adjacent after a reindex, which will speed up I/O (sequential I/O is
much faster than random I/O).
But the storey is not entirely fair, because a reindex will not only
remove fragmentation, but it will also remove unused space (depending on
the fill factor) and will rebalance the index tree. This can result in a
shallower index, and a shallower index will improve the performance of
all queries that use the index.
And of course, even in an OLTP system you will not always be doing just
exact match queries that will access only one (consecutive) data page.
So in general the statement is true, but most likely you can still
benefit from reindexing on your specific system.
HTH,
Gert-Jan|||To add on to Gert-Jan's response, defragmenting indexes can improve buffer
efficiency and reduce I/O increasing data density. I have seen noticeable
OLTP performance improvement by reorging clustered indexes.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:6CD68521-EE02-4578-97C2-C2926B3D869F@.microsoft.com...
>I just read an artice about database reindex published by microsoft
> corporation, which states that the performance after doing db reindex is
> not
> better than before defragment in OLTP system. In DSS system, it is faster
> than before defragment. I got confused by this article. Do I need to do
> the
> db reindex or defragment in OLTP system? Thanks.
>

About db reindex

I just read an artice about database reindex published by microsoft
corporation, which states that the performance after doing db reindex is not
better than before defragment in OLTP system. In DSS system, it is faster
than before defragment. I got confused by this article. Do I need to do the
db reindex or defragment in OLTP system? Thanks.Iter wrote:
> I just read an artice about database reindex published by microsoft
> corporation, which states that the performance after doing db reindex is n
ot
> better than before defragment in OLTP system. In DSS system, it is faster
> than before defragment. I got confused by this article. Do I need to do t
he
> db reindex or defragment in OLTP system? Thanks.
Well, it is definitely a generalization. What they probably meant, is
that most access in an OLTP system are seeks for exact matches. In that
case fragmentation is not much of a problem, because the accessed index
pages and data pages aren't adjacent anyway.
If you have an OLAP type system, you are likely to request large
datasets with many consecutive pages. In that case these will be
adjacent after a reindex, which will speed up I/O (sequential I/O is
much faster than random I/O).
But the storey is not entirely fair, because a reindex will not only
remove fragmentation, but it will also remove unused space (depending on
the fill factor) and will rebalance the index tree. This can result in a
shallower index, and a shallower index will improve the performance of
all queries that use the index.
And of course, even in an OLTP system you will not always be doing just
exact match queries that will access only one (consecutive) data page.
So in general the statement is true, but most likely you can still
benefit from reindexing on your specific system.
HTH,
Gert-Jan|||To add on to Gert-Jan's response, defragmenting indexes can improve buffer
efficiency and reduce I/O increasing data density. I have seen noticeable
OLTP performance improvement by reorging clustered indexes.
Hope this helps.
Dan Guzman
SQL Server MVP
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:6CD68521-EE02-4578-97C2-C2926B3D869F@.microsoft.com...
>I just read an artice about database reindex published by microsoft
> corporation, which states that the performance after doing db reindex is
> not
> better than before defragment in OLTP system. In DSS system, it is faster
> than before defragment. I got confused by this article. Do I need to do
> the
> db reindex or defragment in OLTP system? Thanks.
>