How to fix Error 1707 on Windows Server 2025 / Windows 11

by

Why This Error Happens

Error 1707 occurs when a CREATE TABLE statement attempts to assign a single, standalone filegroup to the TEXTIMAGE_ON clause while the table itself is being partitioned using a partition scheme. SQL Server 2025 requires that large object (LOB) data—such as VARCHAR(MAX), TEXT, IMAGE, or XML columns—either stay unpartitioned on the PRIMARY filegroup or be explicitly aligned with the same partition scheme as the base table. Specifying a single named filegroup instead of a partition scheme breaks this alignment requirement, triggering the error.

Step-by-Step Resolution Guide

Step 1: Review the CREATE TABLE Syntax

  • Open the script in SQL Server Management Studio (SSMS) or Azure Data Studio and locate the TEXTIMAGE_ON clause at the end of your CREATE TABLE statement.
  • Confirm whether the table definition also includes an ON [PartitionScheme](PartitionColumn) clause — this confirms the table is partitioned.
  • Example of the problematic syntax: ) ON PS_Orders(OrderDate) TEXTIMAGE_ON [FG_Archive];

Step 2: Align TEXTIMAGE_ON with the Partition Scheme

  • Replace the standalone filegroup reference with the same partition scheme used by the table.
  • Corrected syntax: ) ON PS_Orders(OrderDate) TEXTIMAGE_ON PS_Orders(OrderDate);
  • If you do not need LOB data partitioned separately, simply omit the TEXTIMAGE_ON clause entirely so it defaults to the PRIMARY filegroup.

Step 3: Validate the Partition Scheme and Function

  • Run SELECT * FROM sys.partition_schemes; in SSMS to confirm PS_Orders (or your scheme name) exists and is correctly mapped to a partition function.
  • Run SELECT * FROM sys.partition_functions; to verify the partitioning column data type matches the table’s partitioning column.
  • Re-execute the CREATE TABLE script after correcting the clause.

Step 4: Rebuild or Recreate the Table if Already Deployed

  • If the table already exists with incorrect settings, script it out using SSMS’s “Generate Scripts” wizard.
  • Drop and recreate the table with the corrected TEXTIMAGE_ON clause, then reload data using BCP, BULK INSERT, or an INSERT INTO ... SELECT statement from a backup table.

Alternative or Verification Steps

  • Verify the fix by running sp_help 'YourTableName' or querying sys.tables joined with sys.partition_schemes to confirm the LOB data filegroup now matches the partition scheme.
  • If aligning TEXTIMAGE_ON to the partition scheme is not desired, as an alternative, remove partitioning from the table design and use a single filegroup for both data and LOB storage, then manage historical data via filtered views instead of native partitioning.