Duplicate key row error in sql2000 transactional replication

HI All,
We have transactional replication(not updatable) between 2 databses on the
same server (sql server2000).
Our requiremnt is to prevent the deletes to go to subscriber db. So, while
setting up transactional replication entered NONE in article commands
properties tab for DELETE .
Now the deletes are not going to subscriber , but we are getting below error
for one table.
Cannot insert duplicate key row in object 'xx' with unique index 'PK_xx_x_xx'.
The structure of the table1 at the publisher:
Primary Key on xxxID column .
PRIMARY KEY (non-clustered)PK_xx_x_xx on xxxID column
The structure of the table at subscriber:
Index on xxxID
PK_xx_x_xx nonclustered, unique located on secondaryxxxID
The table does not have any clustered indexes defined.
Why we are getting this error? and How to solve this ? Thanks in advance
for your help.
Regards,
Suchi
You are probably getting a deferred update.
Have a look at http://support.microsoft.com/default.aspx/kb/238254
for more info.
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
"Suchi" <Suchi@.discussions.microsoft.com> wrote in message
news:9934FEA4-DBBB-4DF4-B6F2-51D22E25A27E@.microsoft.com...
> HI All,
> We have transactional replication(not updatable) between 2 databses on the
> same server (sql server2000).
>
> Our requiremnt is to prevent the deletes to go to subscriber db. So, while
> setting up transactional replication entered NONE in article commands
> properties tab for DELETE .
> Now the deletes are not going to subscriber , but we are getting below
> error
> for one table.
> Cannot insert duplicate key row in object 'xx' with unique index
> 'PK_xx_x_xx'.
>
> The structure of the table1 at the publisher:
> Primary Key on xxxID column .
> PRIMARY KEY (non-clustered) PK_xx_x_xx on xxxID column
>
> The structure of the table at subscriber:
> Index on xxxID
> PK_xx_x_xx nonclustered, unique located on secondary xxxID
>
> The table does not have any clustered indexes defined.
> Why we are getting this error? and How to solve this ? Thanks in advance
> for your help.
> Regards,
> Suchi
>
>
|||Here is the fix http://support.microsoft.com/kb/302341/EN-US/
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
"Suchi" <Suchi@.discussions.microsoft.com> wrote in message
news:9934FEA4-DBBB-4DF4-B6F2-51D22E25A27E@.microsoft.com...
> HI All,
> We have transactional replication(not updatable) between 2 databses on the
> same server (sql server2000).
>
> Our requiremnt is to prevent the deletes to go to subscriber db. So, while
> setting up transactional replication entered NONE in article commands
> properties tab for DELETE .
> Now the deletes are not going to subscriber , but we are getting below
> error
> for one table.
> Cannot insert duplicate key row in object 'xx' with unique index
> 'PK_xx_x_xx'.
>
> The structure of the table1 at the publisher:
> Primary Key on xxxID column .
> PRIMARY KEY (non-clustered) PK_xx_x_xx on xxxID column
>
> The structure of the table at subscriber:
> Index on xxxID
> PK_xx_x_xx nonclustered, unique located on secondary xxxID
>
> The table does not have any clustered indexes defined.
> Why we are getting this error? and How to solve this ? Thanks in advance
> for your help.
> Regards,
> Suchi
>
>
|||Hi Hilary,
Thanks a ton for your reply. I have gone through the KB article , but that
is for single updtaes only. We are expecting multi row updates also.
Thanks,
Suchi
"Hilary Cotter" wrote:

> Here is the fix http://support.microsoft.com/kb/302341/EN-US/
> --
> 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
> "Suchi" <Suchi@.discussions.microsoft.com> wrote in message
> news:9934FEA4-DBBB-4DF4-B6F2-51D22E25A27E@.microsoft.com...
>
>
|||Multi row updates are decomposed into single row updates. So if you update
100 rows as part of a batch or in a single stored procedure, they will be
replicated as 100 separate singleton updates.
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
"Suchi" <Suchi@.discussions.microsoft.com> wrote in message
news:FCD2AFB5-4B5B-4DC9-BCA4-2C7AF2BB4871@.microsoft.com...[vbcol=seagreen]
> Hi Hilary,
> Thanks a ton for your reply. I have gone through the KB article , but that
> is for single updtaes only. We are expecting multi row updates also.
> Thanks,
> Suchi
> "Hilary Cotter" wrote:

Dumping all SQL queries.

Hi - is it possible to dump the query text of every query run
against a particular database, or all databases, in an SQL Server
2000 installation? We've an issue whereby a 3rd. party application
running queries over ODBC sometimes claims that gobbledegook is
returned, and it would be very useful to identify the query that's
being claimed is the problem. I suspect that it's an application
issue rather than a SQL Server one.

__________________________________________________ ___________
Are you Catholic ?
http://www.CatholicEmail.com

100s of FREE email addresses -->
http://www.UltimateEmail.com

Send an Online Greeting Card http://www.UltimateEcards.com"sqlserver yeahbaby" <Use-Author-Address-Header@.[127.1]> wrote in message
news:20031104161149.53F073965@.sitemail.everyone.ne t...
> Hi - is it possible to dump the query text of every query run
> against a particular database, or all databases, in an SQL Server
> 2000 installation? We've an issue whereby a 3rd. party application
> running queries over ODBC sometimes claims that gobbledegook is
> returned, and it would be very useful to identify the query that's
> being claimed is the problem. I suspect that it's an application
> issue rather than a SQL Server one.

Look at profiler.

> __________________________________________________ ___________
> Are you Catholic ?
> http://www.CatholicEmail.com
> 100s of FREE email addresses -->
> http://www.UltimateEmail.com
> Send an Online Greeting Card http://www.UltimateEcards.com

Dumb Question

This is a dumb question but I need to ask. Does SQL Server
2000 MSDE version comes with 'pubs' and 'Northwind'
databases '
Thanks.Hi Dan,
No, They will not be installed on setup. You can get the install scripts
from C:\Program Files\Microsoft SQL Server\MSSQL\Install.
They file names are:
instpubs.sql
instnwnd.sql
Otherwise get the latest scripts from microsoft site
http://www.microsoft.com/downloads/...&displaylang=en
Use OSQL frommd prompt to execute the script to create database and objects.
Thanks
Hari
SQL Server MVP
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:113c01c53c39$182a3970$a401280a@.phx.gbl...
> This is a dumb question but I need to ask. Does SQL Server
> 2000 MSDE version comes with 'pubs' and 'Northwind'
> databases '
> Thanks.|||Thanks.........

>--Original Message--
>Hi Dan,
>No, They will not be installed on setup. You can get the
install scripts
>from C:\Program Files\Microsoft SQL Server\MSSQL\Install.
>They file names are:
>instpubs.sql
>instnwnd.sql
>Otherwise get the latest scripts from microsoft site
>http://www.microsoft.com/downloads/details.aspx?
FamilyId=06616212-0356-46A0-8DA2-
EEBC53A68034&displaylang=en
>Use OSQL frommd prompt to execute the script to create
database and objects.
>Thanks
>Hari
>SQL Server MVP
>
>"Dan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:113c01c53c39$182a3970$a401280a@.phx.gbl...
Server[vbcol=seagreen]
>
>.
>

dumb question

Hello, I'm an MSDE n00b but I do have a basic understanding of SQL Server
2000. I have used Enterprise manager to create/manage users/permissions and
replication before. I am however not familiar with MSDE. I am using MSDE
because I'm playing with asp.net. Since there is no user interface I am
lost. I created my tables and stored procedures via the Web Matrix asp.net
development tool.
1) I need to configure the MSDE 2000 installation user access. Its currently
setup for integrated windows authentication, which I want to keep. But how
do I add/manage the users and their permissions. For example with Enterprise
Manager, even with Integrated authentication, you have to add the
users/groups from your windows domain before SQL server can use them. Do I
need to do this in MSDE? how?
2) how can I view current user accounts on the system? and their
permissions?
3) while I'm at it here... how can I retrieve the currently logged in user
from my asp.net web application?
any info is appreciated. Thanks.
If you have a copy of Enterprise Manager on a different machine you should be
able to connect to the msde install and see it in the familar enterprise
manager.
"djc" wrote:

> Hello, I'm an MSDE n00b but I do have a basic understanding of SQL Server
> 2000. I have used Enterprise manager to create/manage users/permissions and
> replication before. I am however not familiar with MSDE. I am using MSDE
> because I'm playing with asp.net. Since there is no user interface I am
> lost. I created my tables and stored procedures via the Web Matrix asp.net
> development tool.
> 1) I need to configure the MSDE 2000 installation user access. Its currently
> setup for integrated windows authentication, which I want to keep. But how
> do I add/manage the users and their permissions. For example with Enterprise
> Manager, even with Integrated authentication, you have to add the
> users/groups from your windows domain before SQL server can use them. Do I
> need to do this in MSDE? how?
> 2) how can I view current user accounts on the system? and their
> permissions?
> 3) while I'm at it here... how can I retrieve the currently logged in user
> from my asp.net web application?
> any info is appreciated. Thanks.
>
>
|||hi,
txghia58 wrote:
> If you have a copy of Enterprise Manager on a different machine you
> should be able to connect to the msde install and see it in the
> familar enterprise manager.
bu only in development/test scenario... you are not licensed to use SQL
Server Client Tools in production...
you have to resort on home made tools and/or 3rd party tools to manage MSDE
in production...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Dumb Question

This is a dumb question but I need to ask. Does SQL Server
2000 MSDE version comes with 'pubs' and 'Northwind'
databases ?
Thanks.
Hi Dan,
No, They will not be installed on setup. You can get the install scripts
from C:\Program Files\Microsoft SQL Server\MSSQL\Install.
They file names are:
instpubs.sql
instnwnd.sql
Otherwise get the latest scripts from microsoft site
http://www.microsoft.com/downloads/d...displaylang=en
Use OSQL frommd prompt to execute the script to create database and objects.
Thanks
Hari
SQL Server MVP
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:113c01c53c39$182a3970$a401280a@.phx.gbl...
> This is a dumb question but I need to ask. Does SQL Server
> 2000 MSDE version comes with 'pubs' and 'Northwind'
> databases ?
> Thanks.
|||Thanks.........

>--Original Message--
>Hi Dan,
>No, They will not be installed on setup. You can get the
install scripts
>from C:\Program Files\Microsoft SQL Server\MSSQL\Install.
>They file names are:
>instpubs.sql
>instnwnd.sql
>Otherwise get the latest scripts from microsoft site
>http://www.microsoft.com/downloads/details.aspx?
FamilyId=06616212-0356-46A0-8DA2-
EEBC53A68034&displaylang=en
>Use OSQL frommd prompt to execute the script to create
database and objects.
>Thanks
>Hari
>SQL Server MVP
>
>"Dan" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:113c01c53c39$182a3970$a401280a@.phx.gbl...
Server
>
>.
>