SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


UPDATE ERROR ON TABLE WITH NEW COLUMN


UPDATE ERROR ON TABLE WITH NEW COLUMN

Author
Message
justin_heilbron
justin_heilbron
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 6
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217167 Visits: 46278
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
justin_heilbron
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 6
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
DBA328
SSC Eights!
SSC Eights! (844 reputation)SSC Eights! (844 reputation)SSC Eights! (844 reputation)SSC Eights! (844 reputation)SSC Eights! (844 reputation)SSC Eights! (844 reputation)SSC Eights! (844 reputation)SSC Eights! (844 reputation)

Group: General Forum Members
Points: 844 Visits: 1123
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]

[li][/li]
Mike John
Mike John
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3322 Visits: 5983
It is still not clear what error message you are seeing - Can you post the EXACT error message you are getting please?

Plus the entire create table statement for the tables involved.

Thanks

Mike



justin_heilbron
justin_heilbron
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 6
Hi Guys

Thank you for the replies. I need to recreate the error as we had to remove the added column in order to continue production.

If it helps, the programs are written in VB6, the database was on SQL Server 2000 and I upgraded them to SQL Server 2008 about 2 months ago.


I will post up the error message sometime today. Please let me know if there is any other info that may help.
justin_heilbron
justin_heilbron
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 6
Hi Guys

Thank you for the replies. I need to recreate the error as we had to remove the added column in order to continue production.

If it helps, the programs are written in VB6, the database was on SQL Server 2000 and I upgraded them to SQL Server 2008 about 2 months ago.


I will post up the error message sometime today. Please let me know if there is any other info that may help.
paul.knibbs
paul.knibbs
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4126 Visits: 6240
justin_heilbron (8/2/2012)
Hi Guys
Thank you for the replies. I need to recreate the error as we had to remove the added column in order to continue production.


So...you don't have a test environment? You perform modifications direct on the live data? That sounds like a disaster waiting to happen!
justin_heilbron
justin_heilbron
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 6
No, there is a test environment (a database exactly the same as the live db).

Here is the history of my position, there is a programmer on site who does development in VB6 and Turbo Pascal for the existing systems, changes are made daily as the system has never been stable (built from +-25 years of bad foundation).

In the SQL database's used, table names and column name are literally sentences with spaces and most field types are all char. The program that has been developed over the years, pretty much follows the same nature. No standards or rules what so ever. So that is why I am here.

I dont do any development in VB6, as I do not want to get caught up in maintaining the current system as opposed to rewriting it.

So, the other programmer was not able to do what ever testing he needed to do the day he came in, due to that column.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search