• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

Question when altering columns contents in SQL 2000 & SQL 2005

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
  • 3
2 Solutions
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,
rayluvsAuthor Commented:
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).
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
       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>)
rayluvsAuthor Commented:
Thank you, yes!!!  We'll do!!
rayluvsAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now