| Author |
Topic |
|
FreeZey
Starting Member
5 Posts |
 Posted - 2009-12-25 : 15:29:07
|
| BackgroundI recently extended a couple of tables using a standard ALTER TABLE statement but the changes haven’t taken properly. The new columns aren’t retaining values after being updated.SQL Update via QueryThe SQL executes without error, but only the original columns are updated, the newly added ones remain NULL.Direct Update in StudioThe cell can be clicked on and the values entered but as soon as the focus leaves the cell it returns to NULL. No error or warning messages is raised.Also triedDropping the columns and adding them again but I’m still encountering the same problem.Dropping the table and recreating it. This works but there’s a much bigger overhead since I’d need to drop all of the dependent FKs involved.So any ideas? Has anyone come across this issue before? |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-12-25 : 17:51:50
|
Sounds like magic - but I don't believe in magic  Maybe there is a trigger involved?
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
FreeZey
Starting Member
5 Posts |
Posted - 2009-12-26 : 15:48:57
|
| I doubt creating the tables and running the script will replicate the problem, just because it looks like a deeper issue with the server environment or configuration. The behaviour is just so bizarre it looks like it could only be a bug.Is it possible that the transaction it was wrapped in began but didn’t commit or roll back leaving the change in limbo? But it’s been days so surely the transaction would time out. Plus dropping and recreating the columns should get around it.Finally I could can’t post the script without sanitising it and manually transcribing it across the security air gap.--------------------------------------------------Thanks for the quick replys and hope you all had a good christmas :) |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-12-26 : 16:37:26
|
again: any triggers?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
FreeZey
Starting Member
5 Posts |
Posted - 2009-12-27 : 03:43:19
|
quote:
Originally posted by webfred
again: any triggers?
Sorry I missed this. I’m not very familiar with trigger implementation in SQL 2005 so I’m not sure where I can look this up on Tuesday to find it out.But from the design documentation I’ve read I think the only triggers we have are used to prevent rows being deleted and instead set an “is_deleted” column flag. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-12-27 : 11:54:57
|
In SSMS you can expand the table in the database (in Object Explorer on the left).There you can expand "triggers" to see if there are any triggers on that table.If there is a trigger you can right click on it and choose edit to see what the trigger is doing.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
FreeZey
Starting Member
5 Posts |
Posted - 2009-12-27 : 14:18:25
|
| Thanks Fred, I shall take a look when I get in on Tuesday. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-12-27 : 18:39:45
|
quote:
Originally posted by FreeZey
Is it possible that the transaction it was wrapped in began but didn’t commit or roll back leaving the change in limbo? But it’s been days so surely the transaction would time out. Plus dropping and recreating the columns should get around it.
It doesn't timeout unless the application you are using has a timeout value set. SSMS by default will run indefinitely. To see if you have any open transactions in a database you can run this:USE dbNameGODBCC OPENTRANWhat does @@VERSION show just to verify CU and SP levels?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
FreeZey
Starting Member
5 Posts |
Posted - 2010-01-04 : 15:04:10
|
| THANK YOU ALLIt was indeed a trigger firing on any update statement...which the DB developer completely forgot to mention.Cheers for your help. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-04 : 16:52:34
|
quote:
Originally posted by tkizer
Good catch webfred!
Thank you
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
Newly added table columns not accepting values