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
Solved

Question when altering columns contents in SQL 2000 & SQL 2005

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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 article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

808 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