Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

UPDATE ERROR ON TABLE WITH NEW COLUMN Expand / Collapse
Author
Message
Posted Thursday, August 2, 2012 12:10 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 3, 2012 3:39 AM
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
Post #1338958
Posted Thursday, August 2, 2012 12:14 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:05 AM
Points: 40,174, Visits: 36,571
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 2008, MVP
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

Post #1338961
Posted Thursday, August 2, 2012 2:16 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 3, 2012 3:39 AM
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.
Post #1339000
Posted Thursday, August 2, 2012 2:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:47 AM
Points: 126, Visits: 1,084
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]
Post #1339012
Posted Thursday, August 2, 2012 2:54 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:53 AM
Points: 2,667, Visits: 5,881
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



Post #1339013
Posted Thursday, August 2, 2012 3:00 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 3, 2012 3:39 AM
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.
Post #1339017
Posted Thursday, August 2, 2012 3:00 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 3, 2012 3:39 AM
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.
Post #1339018
Posted Friday, August 3, 2012 3:19 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:50 AM
Points: 1,602, Visits: 5,652
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!
Post #1339698
Posted Friday, August 3, 2012 3:54 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 3, 2012 3:39 AM
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.
Post #1339710
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse