laravel/framework · error · Exception

Unable to retrieve lastInsertID for ODBC.

Error message

Unable to retrieve lastInsertID for ODBC.

What it means

SqlServerProcessor's processInsertGetIdForOdbc is called when insertGetId() is used on a SQL Server ODBC connection. It queries SCOPE_IDENTITY()/@@IDENTITY to retrieve the last inserted ID. If that query returns no result (empty array), it throws a generic Exception — meaning the identity retrieval query itself produced nothing, likely due to an ODBC driver limitation, misconfiguration, or a trigger that resets @@IDENTITY.

Solutions

1. Switch from ODBC to the native sqlsrv or pdo_sqlsrv driver, which use OUTPUT INSERTED.* and do not rely on SCOPE_IDENTITY()
2. Ensure the target table has an IDENTITY column (auto-increment primary key)
3. Check for AFTER INSERT triggers that modify @@IDENTITY and either remove them or use SCOPE_IDENTITY()-safe patterns
4. If stuck on ODBC, use insert() and then retrieve the ID via a follow-up SELECT MAX(id) or a unique column lookup

Example fix

// before — ODBC connection fails on insertGetId
$id = DB::table('logs')->insertGetId(['message' => 'test']);

// after — use native sqlsrv driver in config/database.php
// 'sqlsrv' => [
//     'driver' => 'sqlsrv',
//     'host' => env('DB_HOST'), ...
// ]
// Then:
$id = DB::table('logs')->insertGetId(['message' => 'test']);

// or — insert then query if ODBC is required
DB::table('logs')->insert(['message' => 'test', 'created_at' => now()]);
$id = DB::table('logs')->where('message', 'test')->orderByDesc('id')->value('id');

// Check if using ODBC driver
$config = DB::connection()->getConfig();
$isOdbc = ($config['driver'] ?? '') === 'odbc'
    || str_contains($config['driver'] ?? '', 'odbc');

if ($isOdbc) {
    // Avoid insertGetId; use insert + follow-up query
    DB::table('logs')->insert($values);
    $id = DB::table('logs')->orderByDesc('id')->value('id');
} else {
    $id = DB::table('logs')->insertGetId($values);
}

function isOdbcConnection(): bool {
    $config = DB::connection()->getConfig();
    return ($config['driver'] ?? '') === 'odbc';
}

try {
    $id = DB::table('logs')->insertGetId($values);
} catch (\Exception $e) {
    if (str_contains($e->getMessage(), 'lastInsertID for ODBC')) {
        DB::table('logs')->insert($values);
        $id = DB::table('logs')->orderByDesc('id')->value('id');
    } else {
        throw $e;
    }
}

When it happens

Trigger: Calling ->insertGetId($values) on a query builder whose connection is a SQL Server ODBC connection (driver: 'odbc' with sqlsrv). The SCOPE_IDENTITY()/@@IDENTITY query returns an empty result set.

Common situations: Using the Microsoft ODBC Driver for SQL Server with a misconfigured DSN. Database triggers that reset @@IDENTITY or SCOPE_IDENTITY. ODBC driver version incompatibilities that fail silently on identity queries. Tables without an IDENTITY column where insertGetId is called.

Related errors

• This database engine does not support inserting while…
• Cannot use saveOrIgnore on an existing model.
• Index name contains invalid characters.
• The returning columns must not be empty.
• The unique columns must not be empty.

AI-assisted analysis of laravel/framework@e0f6eb3518 (2026-08-11). Data as JSON: /api/errors/6a7ea879989e1d03. Report an issue: GitHub.

Appendix: 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 e0f6eb3518)