SQL Server error during adding a secondary key

Business Central/NAV User Forum
1

Hello, NAV developers!

My name is Snam and I have a quick question about adding a secondary key.

I tried to add a secondary key consists of following fields to “Item Ledger Entry” Table :

Item No.,Open, Variant Code,Positive,Location Code, Expiration Date**,Lot No.,Posting Date,Serial No.**

Below is the error I get.

8015.SQL error.jpg

All the fields used in my new secondary key have been already used in pre-existing keys, so I am not violating the max number of fields, 20.

The system administrator says I have access to everything, so I am assuming it won’t have to do with not having permissions.

Any special configuration necessary to grant the user to modify keys in tables?

Any idea what might be wrong here?

As always, thanks for your help!

Snam

2

looks like you don’t have permission.

By the way that’s a really bad key.[;)]

3

Hello David,

Thanks for reply. I will ask my system administrator to grant me more(?) permission.

Since you mentioned that this key is a bad one, I need to ask you some questions. :slight_smile:

Below is I believe the default key tables of “Item Ledger Entry” table.

3681.current keys.jpg
3681.current keys.jpg947×421 82.5 KB

I am trying to create a key with slight change in the order of fields of an already existing key.

Since similar key was already in the key tables, I just assumed it won’t cost much,

but now I think about it, even though all the fields were already being used, adding another key would means more processing cost.

Right?

Also, since I am planning on using this new key instead of the already existing key, would it be safe to remove the previous one?

I know the most frequent user won’t be needing the previous key for sorting.

Is there a way to check whether a certain key is being used in DataItemTableView? Any other places to check before removing a key?

The reason why I am trying to add a new key is to sort the data items in a report to so that I can do some calculation easily and this was the only way I could think of. I guess it’s better to find a way to do things without adding keys to tables.

Thank you.

4

Never remove anything standard from standard tables, you can add keys not a problem.

5

Your DB admin may have given you db_datareader role, but it’s not enough for changing DB structure. To perform such changes as adding index you need db_owner, which is the highest DB role, normally not needed for Navision users, and DBadmins usually don’t give it away so easily. You may ask him to assign this role for you temporarily, or ask him to create the key for you, if he has access to Navision with his login credentials.

Unfortunately there is no other way to sort data in Navision report as using some of existing indexes…

6

Thanks for reply, Modris.

7

Thanks for reply~