UPDATE ERROR ON TABLE WITH NEW COLUMN

• justin_heilbron

  Valued Member

  Points: 61

  More actions

  August 2, 2012 at 12:10 am

  #261975

  Hi All

  I have a problem I cant work out. Yesterday, I added a column to the end of a table. This new column is not a primary/foreign key nor is it indexed and it allows nulls. It is however a computed field (on an insert or update, two fields are added to make up the new field i.e.: NewField = [AccountNo]+[SerialNo])

  I have added the column at the end of the table as I have before without any problems.

  But when an update is run within the program, an error occurs which seems to be cause by the new field.

  I suspect that it is due to the computed field being persisted, but I thought that wouldnt have been a problem because the column allows nulls.

  I hope to hear from you soon and appreciate any advice.

  J

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  August 2, 2012 at 12:14 am

  #1520650

  Post the code that the app is running and the actual error.

  You can't update a computed column (persisted or not), it's calculated. Hence if the app tries to update that column, it will cause an error.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• justin_heilbron

  Valued Member

  Points: 61

  More actions

  August 2, 2012 at 2:16 am

  #1520683

  Hi Gila

  Thank you for the prompt response.

  That is correct, but the computed field is not being updated via the program, but the fields used to compute that field may be updated.

  I originally thought the error was caused by a "SELECT *" statement within the program, which is used throughout.

  I am outsourced by this company to fix the database and programs, as you will see by the sql code below, the state of the database and programs is scary. (Note the column names... some of the column and table names literally tell a story).

  PubCon.Execute "update autoorders set [Personalisation Department Authorisation] = 'Y'," _

  + "[Audit and Security Department Authorisation] = 'Y', " _

  + "[Personalisation Department Authorisation clock card No] = 'AutoRel'," _

  + "[Audit and Security Department Authorisation clock card no] = 'AutoRel'" _

  + "from autoorders inner join products on product = [product code] " _

  + "where autoorders.bankname like 'absa%' and autoorders.scanned = 'N' And " _

  + "autoorders.reprint ='Y' and products.[print reguarity] = 'A'"

  When doing any updates which include the "AutoOrders" table, the error refers to the number of columns within the table.

• DBA328

  Hall of Fame

  Points: 3952

  More actions

  August 2, 2012 at 2:53 am

  #1520693

  i did a testing, the script should work fine. So it may related with your table configuration, can you post your really error message?

  create table autoorders

  (

  [AccountNo] varchar(10),

  [SerialNo] varchar(10),

  bankname VARCHAR(10),

  [product code] VARCHAR(10),

  [Personalisation Department Authorisation] varchar(10),

  [Audit and Security Department Authorisation] varchar(10),

  [Personalisation Department Authorisation clock card No] varchar(10),

  [Audit and Security Department Authorisation clock card no] varchar(10),

  NewField AS ( [AccountNo]+[SerialNo])PERSISTED

  )

  update autoorders set [Personalisation Department Authorisation] = 'Y',

  [Audit and Security Department Authorisation] = 'Y',

  [Personalisation Department Authorisation clock card No] = 'AutoRel',

  [Audit and Security Department Authorisation clock card no] = 'AutoRel'

  from autoorders inner join products on product = [product code]