Solved

Question when altering columns contents in SQL 2000 & SQL 2005

Posted on 2011-09-26
5
282 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

751 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