[pdo_sqlsrv] Cannot insert explicit value for identity column in table 'XXX' when IDENTITY_INSERT is set to OFF.

[mmta41]

SQLSTATE[23000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot insert explicit value for identity column in table 'vendor' when IDENTITY_INSERT is set to OFF. The SQL being executed was: INSERT INTO [vendor] ([vendor_parent_id], [vendor_name], [vendor_status], [id_vendor], [vendor_level], [created_at], [updated_at]) VALUES (NULL, 'testes', 0, NULL, 0, 1547902914, 1547902914)

What steps will reproduce the problem?

$model = new ActiveRecordSimple(); $model->save();

What is the expected result?

What do you get instead?

Additional info

Q	A
Yii version	2.0.15.1
PHP version	7.1.7
Operating system	Fedora 28
DB	SQL Server 2017

[yii-bot]

Thanks for posting in our issue tracker. In order to properly assist you, we need additional information:

• When does the issue occur?
• What do you see?
• What was the expected result?
• Can you supply us with a stacktrace? (optional)
• Do you have exact code to reproduce it? Maybe a PHPUnit tests that fails? (optional)

Thanks!

This is an automated comment, triggered by adding the label status:need more info.

[mmta41]

It seems database return error when identity column exists in insert query column list. maybe it is because ActiveRecord doesn't recognize identity column.

Connection string:

'dsn' => 'sqlsrv:Server=192.168.16.34;Database=fund',
            'username' => 'sa',
            'password' => '',

SQL query to create table:

USE [Fund]
GO

/****** Object:  Table [dbo].[broker]    Script Date: 2019-24-27 14:24:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[broker](
    [id_broker] [int] IDENTITY(4,1) NOT NULL,
    [broker_name] [nvarchar](100) NOT NULL,
    [broker_type] [int] NOT NULL,
    [broker_status] [int] NOT NULL,
    [updated_at] [int] NULL,
    [created_at] [int] NULL,
 CONSTRAINT [PK_broker_id_broker] PRIMARY KEY CLUSTERED 
(
    [id_broker] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Error:

Integrity constraint violation – yii\db\IntegrityException
SQLSTATE[23000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot insert explicit value for identity column in table 'broker' when IDENTITY_INSERT is set to OFF.
The SQL being executed was: INSERT INTO [broker] ([broker_type], [broker_name], [broker_status], [id_broker], [updated_at], [created_at]) VALUES (0, 'dada', 1, NULL, 1548587902, 1548587902)

Stack trace:

1. in /home/mmta41/web/fund/vendor/yiisoft/yii2/db/Schema.php at line 664
2. in /home/mmta41/web/fund/vendor/yiisoft/yii2/db/Command.php at line 1263 – yii\db\Schema::convertException(PDOException, 'INSERT INTO [broker] ([broker_ty...')
3. in /home/mmta41/web/fund/vendor/yiisoft/yii2/db/Command.php at line 1075 – yii\db\Command::internalExecute('INSERT INTO [broker] ([broker_ty...')
4. in /home/mmta41/web/fund/vendor/yiisoft/yii2/db/Schema.php at line 433 – yii\db\Command::execute()
5. in /home/mmta41/web/fund/vendor/yiisoft/yii2/db/ActiveRecord.php at line 549 – yii\db\Schema::insert('broker', ['broker_type' => '0', 'broker_name' => 'dada', 'broker_status' => '1', 'id_broker' => '', ...])
6. in /home/mmta41/web/fund/vendor/yiisoft/yii2/db/ActiveRecord.php at line 515 – yii\db\ActiveRecord::insertInternal(null)
7. in /home/mmta41/web/fund/vendor/yiisoft/yii2/db/BaseActiveRecord.php at line 670 – yii\db\ActiveRecord::insert(true, null)
8. in /home/mmta41/web/fund/backend/modules/insur/models/Broker.php at line 26 – yii\db\BaseActiveRecord::save(true, null)

[alexkart]

This code works correct:

$b = new Broker();
$b->broker_name = 'zxc';
$b->broker_type = 3;
$b->broker_status = 3;
$b->save();

But if you set id_broker to null explicitly you will get that error:

$b = new Broker();
$b->id_broker = null; // <--
$b->broker_name = 'zxc';
$b->broker_type = 3;
$b->broker_status = 3;
$b->save();

So, this means that somewhere in your code id_broker is assigned to null. What you need instead is to unset this property before saving:

$b = new Broker();
$b->id_broker = null;
unset($b->id_broker); // <--
$b->broker_name = 'zxc';
$b->broker_type = 3;
$b->broker_status = 3;
$b->save();