laravel/framework · error · Exception

Unable to retrieve lastInsertID for ODBC.

Error message

Unable to retrieve lastInsertID for ODBC.

What it means

SqlServerProcessor::processInsertGetIdForOdbc() throws a generic Exception when the SCOPE_IDENTITY()/@@IDENTITY query returns an empty result set. Laravel uses this fallback path to retrieve the last inserted ID when the connection is configured with odbc=true, because PDO::lastInsertId() is unreliable over ODBC. An empty result means the identity retrieval query itself failed to return a row.

Source

Thrown at src/Illuminate/Database/Query/Processors/SqlServerProcessor.php:50

        return is_numeric($id) ? (int) $id : $id;
    }

    /**
     * Process an "insert get ID" query for ODBC.
     *
     * @param  \Illuminate\Database\Connection  $connection
     * @return int
     *
     * @throws \Exception
     */
    protected function processInsertGetIdForOdbc(Connection $connection)
    {
        $result = $connection->selectFromWriteConnection(
            'SELECT CAST(COALESCE(SCOPE_IDENTITY(), @@IDENTITY) AS int) AS insertid'
        );

        if (! $result) {
            throw new Exception('Unable to retrieve lastInsertID for ODBC.');
        }

        $row = $result[0];

        return is_object($row) ? $row->insertid : $row['insertid'];
    }

    /** @inheritDoc */
    public function processColumns($results)
    {
        return array_map(function ($result) {
            $result = (object) $result;

            $type = match ($typeName = $result->type_name) {
                'binary', 'varbinary', 'char', 'varchar', 'nchar', 'nvarchar' => $result->length == -1 ? $typeName.'(max)' : $typeName."($result->length)",
                'decimal', 'numeric' => $typeName."($result->precision,$result->places)",
                'float', 'datetime2', 'datetimeoffset', 'time' => $typeName."($result->precision)",
                default => $typeName,

View on GitHub (pinned to bd6b5437e6)

Solutions

1. Ensure the target table has an IDENTITY (auto-increment) primary key column.
2. Avoid the ODBC path by setting the connection 'odbc' config to false so PDO::lastInsertId() is used.
3. Use an explicit OUTPUT INSERTED.ID clause via a raw statement instead of insertGetId under ODBC.
4. Update/replace the ODBC driver with one that reliably supports SCOPE_IDENTITY.

Example fix

// before
$id = DB::table('audit_log')->insertGetId($row);
// odbc=true, table has no IDENTITY column

// after (use OUTPUT or add IDENTITY column)
$id = DB::selectOne('INSERT INTO audit_log (col) OUTPUT INSERTED.id VALUES (?)', [$val])->id;

// Before insertGetId, confirm the table has an IDENTITY column on ODBC
if ($connection->getConfig('odbc') === true) {
    $hasIdentity = $connection->selectOne(
        "SELECT 1 FROM sys.identity_columns ic JOIN sys.tables t ON ic.object_id = t.object_id WHERE t.name = ?",
        [$table]
    );
    if (! $hasIdentity) {
        throw new \LogicException("Table '{$table}' has no IDENTITY column; insertGetId unavailable over ODBC.");
    }
}

try {
    $id = DB::table('audit_log')->insertGetId($row);
} catch (\Exception $e) {
    if (str_contains($e->getMessage(), 'Unable to retrieve lastInsertID for ODBC')) {
        // Fallback: re-select the row by a unique business key, or use OUTPUT INSERTED.id
        $id = DB::selectOne('INSERT INTO audit_log (col) OUTPUT INSERTED.id VALUES (?)', [$row['col']])->id;
    } else {
        throw $e;
    }
}

When it happens

Trigger: Calling DB::table(..)->insertGetId($values) (or Model::create / insertAndGetId) on a SQL Server connection where the 'odbc' config option is true, and the subsequent SELECT CAST(COALESCE(SCOPE_IDENTITY(), @@IDENTITY) AS int) query returns no rows.

Common situations: Inserting into a table without an IDENTITY column (so SCOPE_IDENTITY returns NULL/empty) over an ODBC connection. ODBC driver version issues that drop the scope-identity context. Triggers firing after insert that reset @@IDENTITY.

Related errors

• This database engine does not support case sensitive like op
• This database engine does not support fulltext search operat
• This database engine does not support inserting while ignori
• This database engine does not support insert or ignore with
• Index name contains invalid characters.

AI-assisted analysis of laravel/framework@bd6b5437e6 (2026-08-06). Data as JSON: /data/errors/6a7ea879989e1d03.json. Report an issue: GitHub.