Fieldproperty: AutoIncrement = Yes, and SQL-server

Hi all,

I seem to have a problem with inserting data into a table, where I have set a part of the primary key to AutoIncrement = Yes.

(Well, I don’t since I’m the ‘dbo’, but the other user have [:D])

Whenever a user tries to insert a record into the table in question, they’ll get a SQL-error, basically stating that they have to be either DB- og object-owner, in order to perform the SET IDENTITY_INSERT x.y.z ON.

My question is whether or not I can still use the AutoIncrement = Yes, and NOT have to make everyone ‘dbo’…



I too am using this Autoincrement on one of my table. I noticed this error today as well while I was trying to insert into the table via object designer and typing in the next no. HOWEVER, the users of the system all in VIA active Directory are happily inserting records to this table indirectly (via Form/Code) and no problems there. Perhaps its the way you are inserting. I have not looked at the settings for DB-Object owner yet but don’t expect that is the problem.

See for my observations on this. I have raised this issue with Microsoft and their response was basically that’s the way it works.

Hi Guys,

Thanks for the replies.

I’m inserting through code, not assigning value to the field first.
I assumed that it was related to ownership of the object/database, since that’s what the error-message states.
It only works if the user is member of ‘db-owner’.

Well, seems like Microsoft’s reply to You gives the answer to my question…

So I guess that I have to increment the key my self, and setting the AutoIncrement = No.


Hi Alexander,

Take this example: Table with 2 fields: No, Descripton where No. is Autonumber.

If I try type in a value for No. like the next number in the series (e.g. 51) and a desription the system and then press enter to insert the record the system will give the SQL DBOwner error you mention.

If I try just type in the Description and press enter to insert the record it works! I.e. the Number will show “0” until you insert and the SQL automatically assigns the next Number.

This works in code too.

NOTE: This is without setting SQL permissions to the Object. If the SQL permissions were granted then it allows you to type in a value for the No field.


Hi Nic,

So it seems that I have f***** up somewhere.

Apparently, from what You write, somewhere in my code, a value actually is assigned for the field, before the insert.

I’ll dig into that



My Two Cents …

Assuming, field “No.” is the AutoIncrement thing, if you insert a record via C/AL code, you have to assign a 0 (zero) to “No.” first!


MyRec.“No.” := 0;


Just the INIT doesn’t do the job … should work … without beeing dbo … please try …

Don’t know how to respond to this [:|] , anyway if you can’t get it to work then stick with you original plan and bother using Autonumber. It works for me.


Like Stryk says: You must be sure to reset any value in the Auto-INcrement field. And remember that INIT does not reset primary key fields.

So either reset the field manually ( Field := 0), or use CLEAR(Rec) to clear all fields.

The only thing the AutoIncrement property does is, to set the type to “Identity” in the SQL table. The fact that only dbo’s can assign values to such a field, is a SQL-feature, and documented in SQL-Books Online.

Don’t give up using it. It is a very useful property for hot tables, since it reduces locking to a very minimum of time. You can of course replace AutoIncrement with C/AL code, but then you need to place table locks and/or have a seperate table with numbers and lock that table. On small tables, that is not a probem. But on busy tables it can cause blocking. That’s the main advantage of AutoIncrement.

Hope this helps

Hi Guys,

Sorry that I have not responded earlier.

I’ve been rather busy lately.

I’ll get around to testing Your suggestions on monday.

Again, thanks a lot for the responses.


Hi Guys,

Works like a charm now.

Thank you so much.


Hi Lars,

the implication you are making here, is that on SQL, Navision is not calculating the Autoincrement value, but leaving SQL to do that instead. Has it always done it this way, or is this new in 4.00. I thought in 3.70 that the Auto inicrement value was calculated by Navision on both servers. More specifically the error in 3.60 where the counter was reset after a backup, I saw in bith SQL and Native.

Clearly moving functionality off the client and onto the Server is good news, and I hope this trend continues.