Problem renaming fields in 2009 SP1

DenSter · 2009-10-30

This is NAV2009 SP1, build 29626 on SQL Server 2008. Take any field, and rename the field in the table designer. Save/compile and try to modify a record. We're getting an 'invalid column name' SQL Server error on the old field name.

Take for instance the Description field in the Currency table and change it to "Description Test". It saves and compiles properly. Then open the table from the object designer and change any description. We're getting an error saying "207,"42S22",[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'Description'.". Note that at this point, the column name is actually "Description Test", so it is referencing the field name that it used to have.

The field name in NAV is "Description Test", the column name in SQL Server is "Description Test", keys are updated properly, SQL indexes are updated properly, we've looked everywhere we could think of. Somehow, NAV retains the old field name somewhere, but we can't figure out where.

kriki · 2009-10-30

Sounds almost like NAV has some singleinstance codeunit in which the table is used and has it in memory and when you try to change a record, the object hasn't been refreshed.

Is it a problem you have with only that version or also with other versions? If it is only that version, it could be a problem of the build.

matttrax · 2009-10-30

We had a similar error once. I think it was when trying to rebuild indexes for a table. It ended up it was because I was logged into the client using database name ABC, but the name in SQL was Abc. Capitalization mattered.

Don't know if that might be your problem as well. I added the database name to my icon so I never logged in "incorrectly" again.

DenSter · 2009-10-30

kriki wrote:

Sounds almost like NAV has some singleinstance codeunit in which the table is used and has it in memory and when you try to change a record, the object hasn't been refreshed.

That's not it. The error persists when restarting NAV. Initially the error was raised after upgraded objuects were imported. For instance the "Country Code" field in the ILE table was renamed to "Country/Region Code". Update queries use the old "Country Code" field and that's when the error hits.

The problem we're facing is figuring out where the heck it saves the old field name. The query coming out of NAV must get it someplace, but everywhere we look the new field name is used.

DenSter · 2009-10-30

matttrax wrote:

it was when trying to rebuild indexes for a table

We're not rebuilding indexes, we're not seeing any errors when saving or recompiling or reimporting or changing the table in any way. The query coming out of NAV is using the old field name, and we can't find where it is getting it from.

matttrax · 2009-10-30

I know you're not rebuilding indexes. I've only seen the "oldname" error when I logged into the database with an improperly capitalized database name. We had to run a SQL trace on my account while I did it to find anything.

kriki · 2009-10-30

And restarting the SQL server helps?
If it helps, it means the old version is still kept in memory somewhere.

DenSter · 2009-10-30

kriki wrote:

And restarting the SQL server helps?
If it helps, it means the old version is still kept in memory somewhere.

The query coming out of NAV is using the old field name, I don't see how that could be a SQL Server memory problem. We did do DBCC FREEPROCCACHE with no change, a restart might be worth a try, although I don't know if it will be easy to get them to do this.

DenSter · 2009-10-30

matttrax wrote:

I've only seen the "oldname" error when I logged into the database with an improperly capitalized database name

gotcha, I've forwarded that suggestion, thanks

kriki · 2009-10-30

DenSter wrote:

kriki wrote:

And restarting the SQL server helps?
If it helps, it means the old version is still kept in memory somewhere.

The query coming out of NAV is using the old field name, I don't see how that could be a SQL Server memory problem. We did do DBCC FREEPROCCACHE with no change, a restart might be worth a try, although I don't know if it will be easy to get them to do this.

It is not even needed to restart the server. Only the SQL server service.
BTW: can you replicate the problem on another server? Like a VM with that windows and SQL in it.

DenSter · 2009-10-30

kriki wrote:

It is not even needed to restart the server. Only the SQL server service.

Yes I know what restart means :thumbsup: and no we have not put any effort into replicating it on another server, we're trying to focus on fixing the one that we have the issue on

kine · 2009-10-30

Is the problem Classic client or RTC (or both)?

DenSter · 2009-10-30

Classic

ara3n · 2009-10-30

I've tried the same things on
2009 sp1 sql 2008 on Currency table and it worked fine.

Also are you on enhanced security model? And has security been synched?

DenSter · 2009-11-01

It's not a permissions error, so I don't see how that could be relevant.

I haven't been able to reproduce on Cronus, it seems to be a database specific problem. It's a 4.0 database, exe upgrade to 5.0 SP1, SQL restore to development server, exe upgrade to 2009 SP1, imported objects upgraded to 2009 SP1 and that's when the problem began. I reproduced that situation on Cronus and could not recreate the issue. I think there's a MSFT support ticket for it now, if I am updated about the progress I'll follow up.

DenSter · 2009-11-02

As it turns out that the customer had a product that installs triggers on all tables to log database operations on SQL Server, and those triggers reference the old field names.

So, lesson learned: if you run into problems like this, check the triggers on SQL Server :thumbsup:

ara3n · 2009-11-02

I had run into similar problem with sql trigger.

Except in my situation, the users had renamed the company, and suddently could not enter data on one of the req line.

David_Singleton · 2009-11-02

Don't you just love how customers don't see the need to tell you "unimportant" details like this.

matttrax · 2009-11-02

I ran into some code on a SQL trigger that deleted an order during the posting process. Only problem was that when the NAV code came around that does this it couldn't find the order...that's a day of my life I'll never get back.

Problem renaming fields in 2009 SP1

Answers

Categories