Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

CarrahaG (Programmer)
4 Feb 12 4:48
Hello

I have a problem with a MS SQL Server 2005 SQL database.  

If I run the command

    DBCC CHECKDB(BHT, REPAIR_REBUILD)


I get the following result:

Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'pduser.PDCPS_DOC_LIN' and the index name 'pkPDCPS_DOC_LIN'. The duplicate key value is (101321731360, 1).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'pduser.PDCPS_DOC_LIN' and the index name 'cttPDCPS_DOC_LIN'. The duplicate key value is (Aug 10 2011  6:23PM, 101321739271, 1).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'pduser.PDCPS_DOC_LIN' and the index name 'ctwsPDCPS_DOC_LIN'. The duplicate key value is (1, 101321731360, 1).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'pduser.PDCPS_DOC_LIN' and the index name 'cttsPDCPS_DOC_LIN'. The duplicate key value is (1007035, 101321739271, 1).
DBCC results for 'BHT'.


If run the command with the REPAIR_ALLOW_DATA_LOSS, the same error occurs:
    DBCC CHECKDB(BHT, REPAIR_ALLOW_DATA_LOSS)



Running the command

select *
from pduser.PDCPS_DOC_LIN
where CDOC_ID in (101321732044, 101321732866, 101321734503, 101321735658)


gives the result:


CDOC_ID          CLIN_SEQ_NO     PDCCTime                 PDCDel     PDCRepl     PDCTSID     PDCWSID     PDC1                     PDC2        PDC1F1                   PDC2F1      
------------     -----------     --------------------     ------     -------     -------     -------     --------------------     -------     --------------------     -------     
101321732044     1               1/21/2012 5:30:44 PM     D          R           1018571     1           2/3/2012 10:08:04 PM     1000001     2/3/2012 10:08:04 PM     1000001     
101321732866     1               1/21/2012 5:31:55 PM     D          R           1018572     1           2/3/2012 10:08:04 PM     1000001     2/3/2012 10:08:04 PM     1000001     
101321732866     2               1/21/2012 5:31:55 PM     D          R           1018572     1           2/3/2012 10:08:04 PM     1000001     2/3/2012 10:08:04 PM     1000001     
101321732866     3               1/21/2012 5:31:55 PM     D          R           1018572     1           2/3/2012 10:08:04 PM     1000001     2/3/2012 10:08:04 PM     1000001     
101321734503     1               1/21/2012 5:34:16 PM     D          R           1018573     1           2/3/2012 10:08:04 PM     1000001     2/3/2012 10:08:04 PM     1000001     
101321735658     1               1/21/2012 5:35:56 PM     D          R           1018574     1           2/3/2012 10:08:05 PM     1000001     2/3/2012 10:08:05 PM     1000001     

6 records selected.




However, if I try to remove the records with the command:

delete  pduser.PDCPS_DOC_LIN
where CDOC_ID in (101321732044, 101321732866, 101321734503, 101321735658)



I get the following error:

Error in statement # 1

Internal Query Processor Error: The query processor encountered an
unexpected error during execution

[delete  pduser.PDCPS_DOC_LIN
where CDOC_ID in (101321732044, 101321732866, 101321734503, 101321735658)]


The primary key is a clusterred primary key.  


My question is how can I remove the records in question?   Restoring from a backup is not a good option because there has been data added to the table (and to other tables on the database) after the problem began.


Is there a way to drop the primary key, locate and remove the records in question and then put back the primary key?



Regards,
Georges



 
gmmastros (Programmer)
4 Feb 12 8:16
Perhaps you could try copying the data in that table to another one.  Then drop the table and rename the copied table to the correct name.

Before you do this, you should make a backup of the database and also make sure that you script everything about the table, including indexes, triggers, etc...

 

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close