MSSQL table with an IDENTITY field issue

augusto · 2026-08-19 09:47:05

Hi All,

Please help.

I'm having trouble to get mORmot(2.4) ORM insert working with an existing MSSQL table with an IDENTITY field.

when I run

NewID := Server.Add(Rec, true);

It runs

"select max(ID) from dbo.RemoteProcessTest"

...and insists on sending the ID column in the insert statement - "insert into dbo.RemoteProcessTest (ID,ProcessId,Title) values (?,?,?))"
- then get an error :

Cannot insert explicit value for identity column in table 'RemoteProcessTest' when IDENTITY_INSERT is set to OFF

I'm not sure if this is a bug or if I'm missing something in my configuration.

Table:

CREATE TABLE dbo.RemoteProcessTest
(
    ID int IDENTITY(10000,1) NOT NULL,
    ProcessId varchar(40) NOT NULL,
    Title varchar(40) NOT NULL,

    CONSTRAINT PK_RemoteProcessTest
    PRIMARY KEY(ID)
);

ab · 2026-08-19 14:43:32

Please follow the forum rules and put your code in external links like gist.
Especially when the code is not self-containing (no table init).

In a quick look, I would say that MSSQL table with an IDENTITY field is not supported by the ORM.

augusto · 2026-08-19 15:25:48

Thanks ab
Sorry for this, here is the link:
https://gist.github.com/MRIAG2026/dd69f … e0b2165465

in this case, I guess that if the MSSQL table with IDENTITY field is not supported by the ORM, I will always have to call Execute/ExecuteInlined directly from TSQLDBConnectionProperties for inserting records.
Is this feature planned to be added in the future?

ab · 2026-08-19 15:48:55

Yes, you are right: use direct SQL for such tables.

This feature is clearly not planned at all from our side.
But you are free to contribute and send some Pull Request.

koraycayiroglu · 2026-08-20 06:19:21

Hi, in my humble experience, for old tables i had to add another field as OrmId and set mormot's ID fieldname to this. So, sql and other softwares can keep running as they were and mormot can do it's job as intented.

ab · 2026-08-20 06:21:05

Good workaround.

augusto · 2026-08-20 14:54:00

Thank both for your help.

Yes, that's a good workaround if you are able to change the table structure.

Also found that setting the EngineAddForcedID property on the TRestStorageExternal instance, does the trick. That way, resulting insert statement doesn't include the ID field.
see example : https://gist.github.com/MRIAG2026/fe2aa … 14fa0dfa68

mORMot Open Source

#1 2026-08-19 09:47:05

MSSQL table with an IDENTITY field issue

#2 2026-08-19 14:43:32

Re: MSSQL table with an IDENTITY field issue

#3 2026-08-19 15:25:48

Re: MSSQL table with an IDENTITY field issue

#4 2026-08-19 15:48:55

Re: MSSQL table with an IDENTITY field issue

#5 2026-08-20 06:19:21

Re: MSSQL table with an IDENTITY field issue

#6 2026-08-20 06:21:05

Re: MSSQL table with an IDENTITY field issue

#7 2026-08-20 14:54:00

Re: MSSQL table with an IDENTITY field issue

Board footer