Solved

Question when altering columns contents in SQL 2000 & SQL 2005

Posted on 2011-09-26
5
281 Views
Last Modified: 2012-05-12
We are changing master ID codes for security purposes.  However, some tables are giving the errors below:

   Msg 4406, Level 16, State 1, Line 1
   Update or insert of view or function 'DS10123' failed because it contains a derived or constant field.

   (519 row(s) affected)
   Msg 2601, Level 14, State 1, Line 1
   Cannot insert duplicate key row in object 'dbo.xx_acctcode' with unique index 'XAKxx_acctcode'.
   The statement has been terminated.

We would like to know what do they refer to

Note:  we running SQL 2000/2005
0
Comment
Question by:rayluvs
  • 3
5 Comments
 
LVL 6

Accepted Solution

by:
dan_mason earned 250 total points
ID: 36598170
Update or insert of view or function 'DS10123' failed because it contains a derived or constant field.
This implies that DS10123 has a column expressed like 'abc' AS [letters] (a constant value) or [lettersRev] AS REVERSE([letters]) - a column computed from another column. You might well be able to get around the issue by doing a DROP/CREATE rather than an UPDATE.

Incidentally, the error might not be caused by the view/function itself, but a column in one of the underlying tables; even so I would think a DROP/CREATE should enable you to do what you need.

Cannot insert duplicate key row in object 'dbo.xx_acctcode' with unique index 'XAKxx_acctcode'
There's a unique index attached to dbo.xx_acctcode, which means you can only mention each account code once in there. Your statement is probably trying to pass that account number twice (or insert a new row instead of updating the existing one. I suggest the first thing you should do is try a SELECT DISTINCT instead. Then you need to make sure you are only updating existing records, rather than trying to re-insert them,
0
 

Author Comment

by:rayluvs
ID: 36598718
In the" ' function 'DS10123' failed'", when you DROP/CREATE, are referring to the actual View or Function?

In the" 'dbo.xx_acctcode' with unique index", it could be that the statement is trying top pass it twice.  We are using CHECKSUM to change the Master Account ID and it may be that the hash value length is greater than the Master Account ID length; hence, by saving it, it's saving a truncated value upto the columns lenghth, which may be giving the error (since another truncated hash value my be causing it).
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 250 total points
ID: 36598858
1... you are trying to update a non-updateable column (or a causing an invalid attempt to do so)
      examine the table/view and remove the attempt to modify the non updateable column from your code...

2... if the length is invalid thenn you need to correct that first...
      you must ensure that youre new "keys" are unique and don't clash with any existing values...

        you may find it a better approach to initially generate the new keys into a temporary mapping table
          (oldkey,newkey)
       and then use that to apply the new value sto your tables... probably having dropped all indexes and recreating them afterwards... (or even better unload the table with the new values substituted and then reload the data into a newly created table recreating indexes after the initial load <via BCP>)
 
0
 

Author Comment

by:rayluvs
ID: 36599026
Thank you, yes!!!  We'll do!!
0
 

Author Closing Comment

by:rayluvs
ID: 36718471
Thanx
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question