Attribute Rule Generate ID - Create Sequence Error

[doronwen]

I'm trying to create an attribute rule that would generate the next ID in a sequence every time a user add a new record to a feature class. I keep getting an error and warning messaged related to "Sequence". I'm assuming it's related to the fact my data is housed in a SQL DB.

The data is published to an ArcGIS Portal, so I stopped the service to remove any locks before attempting to add the attribute rule. I also tried using two different accounts- GISOWNER and GDO, but that did not make any difference.

Can someone explain to me what is the "Create Sequence" check box for and what settings/properties I need to change on the SQL Server side of things to enable me to create an attribute rule?

Side note - I do not have permissions to make changes to the database settings on the SQL Server, but I will be meeting with the IT person who does. So I need to explain to them what need to be changed. 

[RPGIS]

Hi @doronwen,

We are currently using a sequencer in all of our attribute rules for our records and typically you would want to follow the instructions below.

1. Open and Configure the Generate Sequence Tool
   1. *Be sure to copy the name of the sequence since the attribute rule will look for that particular name.
2. Once you have that sequence created then, in the attribute rules create an immediate calculation rule.
3. In the rule expression use:

var N = $feature.IncrementID
if( IsEmpty(N) ){ N = NextSequenceValue('<name of sequence>') }
return N

// Or Simply iif( IsEmpty(N), NextSequenceValue('<name of sequence>'), N )
// Note: You may need to specify the schema owner if in an sde database

• Once established and conditions set then the rule will populate accordingly.

You can also run the same rule as a standalone field calculation as well.

[doronwen]

I think one of my problems is that I cannot create a sequence. Is that something I need to enable on the SQL Server?

[Joshua-Young · Accepted solution]

For SQL Server you need to use a database account in the database connection that has the CREATE SEQUENCE and ALTER OR CONTROL permissions in the database you are trying to create the sequence in. The database owner should be able to grant those permissions, but they have to be granted through something like SQL Server Management Studio.

The "Create Sequence" checkbox you highlighted will create a new database sequence when the Generate ID attribute rule template is run. That sequence will have a random name, start at 1, and increment by 1. If you already have a database sequence you want to use, then you can uncheck that box. The Generate ID template is great for creating custom IDs. You can pick the prefix, suffix, padding, separators between those things, and vary things based on an attribute field.