THANKS! Need a very SImple Update Script

SQLHELPasap · 2011-12-31

Hi All,
I am not a SQL expert but am very aware of the dangers associated with any update script for NAV. I just need to change ONE field value on ONE table (ONE line) from null to the value of "4". (I expect this to be a very benign change and accept the fact that it may not work but want to try it.) I am able to query the table and see the field without any problem. We are not able to update the table using Object Designer as we get the message that the update is not allowed as there are "ledger entries" associated with the table. This is a custom table but the field is a simple value and the ledger entries all show the value properly.

We are doing this in a back up db and want to move ahead. This is an older Classic client version of NAV (5.0) and we no longer have support on any front.

As an example: table name in NAV is XX$Task Order Detail (this is a "card" in NAV) and the field is, for example Order "Type" with the numeric values on the table of 1 through 6. Right now the "Type" is null and needs to be "2".

If someone could help me with how to write an update script I would be most grateful.

bbrown · 2011-12-31

Since NAV doesn't allow NULL values, how could you have this situation?

bbrown · 2011-12-31

Also, if you are not familiar with T-SQL, then I'd suggest you write a NAV process-only report to handle this situation.

Luc_VanDyck · 2011-12-31

bbrown wrote:

Also, if you are not familiar with T-SQL, then I'd suggest you write a NAV process-only report to handle this situation.

He will need a developers license though to add permissions to the Entry table, in order to modify records.

bbrown · 2011-12-31

He doesn't say this is an Entry table. In fact, he states that it's a custom table. The ledger records are related. So most likely he does not need a developer's license.

kapamarou · 2011-12-31

If you search google you'll see plenty of examples. Isn't it easier to do it through SQL Management studio?

SQLHELPasap · 2011-12-31

I am using SQL Server Management Studio but do not have the background to write the exact update statement. I was just looking for a simple sort of "shell" if you will. I have the necessary permissions. This does not need to be over complicated, my statement is just erroring out as I am not famiiar with using this tool and am obviously missing something.... OnTuesday, I'll include the actual statement I am trying to use and the error I am getting...it is a syntax error so I have something out of order or improperly phrased..

Thanks all, have a happy and safe NEW YEAR!

davmac1 · 2012-01-02

In Management Studio, you can edit a field unless it is non-editable. If it is non-editable, you can change the field definition in NAV then go back to Management Studio and edit it.
For one field in one row - this is a lot simpler way if you don't know SQL.

SQLHELPasap · 2012-01-02

The field is marked as editable in Object Designer. That is the reason I am in this quandry..

bbrown · 2012-01-02

The "editable" settiings in Object Designer has nothing to do with whether or not you can edit data directly from SQL. Niether do a user's permissions defined in NAV.

SQLHELPasap · 2012-01-03

Issue is resolved..thanks,I figured out how to write the update script myself and all is well..processing continues with no errors. Woot! \:D/

THANKS! Need a very SImple Update Script

Answers

Categories