CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Zac M.
Zac M.

Reputation: 53

Unable to update a table after adding a column (SQL 2008R2)

We just added two columns, UserHash NVARCHAR(255) and UserSalt NVARCHAR(8), to 4 tables in our database. For ONE of these tables, I am getting the following error about the UserHash column when I try to update anything on that table.

Error Message:

Msg 207, Level 16, State 1, Line 1 Invalid column name 'UserHash'.

Example Update statement:

UPDATE dbo.Users 
SET UserSalt = 'asdf', UserHash = 'SomeString' 
WHERE ID = 21237

(1 row(s) affected)
(1 row(s) affected)

The tables were edited in SSMS, and 3 of them have had no problem.

When I select, the column is there. Intellisense sees it.

Removing the column restores update capability.

The problem table is only exhibiting the issue on the UserHash column, not on the UserSalt column.

I've tried removing and re-adding. Same problem. The new columns are not indexed. I have rebuilt all the indexes on the table.

Every search I do comes up with.. "Oops.. My bad" for answers about queries and views. Have any of you SQL gurus seen this behavior?

Upvotes: 2

Views: 788

Answers (1)

Zac M.
Zac M.

Reputation: 53

Not an Oops my bad, I'm suffering from legacy code from a really bad vendor.. I had to dig pretty deep to find my answer.

The "Audit" tables referenced in the triggers are field independent, however in the trigger for this particular table they use column ordinal position and some other convoluted field tracking that is not compatible with SQL 2008. (http://technet.microsoft.com/en-us/library/ms186329(v=sql.105).aspx) The audit tables and triggers for the other tables are newer, and do not have these same problems.

Now I've got to review 50+ triggers in my DB to bring them up to date.

Upvotes: 1

Related Questions