{"record":{"id":"6a7ea879989e1d03","repo":"laravel/framework","slug":"unable-to-retrieve-lastinsertid-for-odbc","errorCode":null,"errorMessage":"Unable to retrieve lastInsertID for ODBC.","messagePattern":"Unable to retrieve lastInsertID for ODBC\\.","errorType":"exception","errorClass":"Exception","httpStatus":null,"severity":"error","filePath":"src/Illuminate/Database/Query/Processors/SqlServerProcessor.php","lineNumber":50,"sourceCode":"        return is_numeric($id) ? (int) $id : $id;\n    }\n\n    /**\n     * Process an \"insert get ID\" query for ODBC.\n     *\n     * @param  \\Illuminate\\Database\\Connection  $connection\n     * @return int\n     *\n     * @throws \\Exception\n     */\n    protected function processInsertGetIdForOdbc(Connection $connection)\n    {\n        $result = $connection->selectFromWriteConnection(\n            'SELECT CAST(COALESCE(SCOPE_IDENTITY(), @@IDENTITY) AS int) AS insertid'\n        );\n\n        if (! $result) {\n            throw new Exception('Unable to retrieve lastInsertID for ODBC.');\n        }\n\n        $row = $result[0];\n\n        return is_object($row) ? $row->insertid : $row['insertid'];\n    }\n\n    /** @inheritDoc */\n    public function processColumns($results)\n    {\n        return array_map(function ($result) {\n            $result = (object) $result;\n\n            $type = match ($typeName = $result->type_name) {\n                'binary', 'varbinary', 'char', 'varchar', 'nchar', 'nvarchar' => $result->length == -1 ? $typeName.'(max)' : $typeName.\"($result->length)\",\n                'decimal', 'numeric' => $typeName.\"($result->precision,$result->places)\",\n                'float', 'datetime2', 'datetimeoffset', 'time' => $typeName.\"($result->precision)\",\n                default => $typeName,","sourceCodeStart":32,"sourceCodeEnd":68,"githubUrl":"https://github.com/laravel/framework/blob/e0f6eb3518ac29fbbca8529e97d0df7fc9f24481/src/Illuminate/Database/Query/Processors/SqlServerProcessor.php#L32-L68","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Switch from ODBC to the native sqlsrv or pdo_sqlsrv driver, which use OUTPUT INSERTED.* and do not rely on SCOPE_IDENTITY()","Ensure the target table has an IDENTITY column (auto-increment primary key)","Check for AFTER INSERT triggers that modify @@IDENTITY and either remove them or use SCOPE_IDENTITY()-safe patterns","If stuck on ODBC, use insert() and then retrieve the ID via a follow-up SELECT MAX(id) or a unique column lookup"],"exampleFix":"// before — ODBC connection fails on insertGetId\n$id = DB::table('logs')->insertGetId(['message' => 'test']);\n\n// after — use native sqlsrv driver in config/database.php\n// 'sqlsrv' => [\n//     'driver' => 'sqlsrv',\n//     'host' => env('DB_HOST'), ...\n// ]\n// Then:\n$id = DB::table('logs')->insertGetId(['message' => 'test']);\n\n// or — insert then query if ODBC is required\nDB::table('logs')->insert(['message' => 'test', 'created_at' => now()]);\n$id = DB::table('logs')->where('message', 'test')->orderByDesc('id')->value('id');","handlingStrategy":"fallback","validationCode":"// Check if using ODBC driver\n$config = DB::connection()->getConfig();\n$isOdbc = ($config['driver'] ?? '') === 'odbc'\n    || str_contains($config['driver'] ?? '', 'odbc');\n\nif ($isOdbc) {\n    // Avoid insertGetId; use insert + follow-up query\n    DB::table('logs')->insert($values);\n    $id = DB::table('logs')->orderByDesc('id')->value('id');\n} else {\n    $id = DB::table('logs')->insertGetId($values);\n}","typeGuard":"function isOdbcConnection(): bool {\n    $config = DB::connection()->getConfig();\n    return ($config['driver'] ?? '') === 'odbc';\n}","tryCatchPattern":"try {\n    $id = DB::table('logs')->insertGetId($values);\n} catch (\\Exception $e) {\n    if (str_contains($e->getMessage(), 'lastInsertID for ODBC')) {\n        DB::table('logs')->insert($values);\n        $id = DB::table('logs')->orderByDesc('id')->value('id');\n    } else {\n        throw $e;\n    }\n}","preventionTips":["Prefer the native sqlsrv or pdo_sqlsrv driver over ODBC for SQL Server connections","Ensure target tables have an IDENTITY column when using insertGetId","Audit AFTER INSERT triggers that could reset SCOPE_IDENTITY()/@@IDENTITY","For ODBC connections, use insert() + SELECT MAX(id) or a unique column lookup as a fallback"],"tags":["odbc","sqlserver","insert","identity","processor"],"backgroundTag":null,"analyzedSha":"e0f6eb3518ac29fbbca8529e97d0df7fc9f24481","analyzedAt":"2026-08-11T20:52:37.562Z","contentChangedAt":null,"schemaVersion":2},"datasetVersion":"2026-09-23T08:17:48.524Z"}