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.
Nicholas:
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’.
Jack:
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.
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.
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.
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.