Solved

Question when altering columns contents in SQL 2000 & SQL 2005

Posted on 2011-09-26
5
277 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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now