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

by

Troubleshooting Guide: Error 7720 – Data Truncated When Converting Range Values to Partition Function Parameter Type

Why This Error Happens

This error occurs when the data type specified in a CREATE PARTITION FUNCTION or ALTER PARTITION FUNCTION statement is too small or imprecise to hold the boundary range values being supplied, causing SQL Server to reject the implicit conversion. This commonly happens when a partition function is defined using a type like smallint, tinyint, decimal(p,s) with insufficient precision/scale, or a narrow varchar/char length, while the actual boundary values (dates, numbers, or strings) exceed that type’s safe range. It can also occur after a schema migration or upgrade to SQL Server 2025, where stricter type-checking during partition function validation surfaces mismatches that were previously tolerated.

Step-by-Step Resolution Guide

Step 1: Identify the Partition Function and Its Parameter Type

  • Run the following query to inspect the current partition function definition:
SELECT pf.name, pf.fanout, prv.boundary_id, prv.value,
       t.name AS data_type, t.precision, t.scale, t.max_length
FROM sys.partition_functions pf
JOIN sys.partition_range_values prv ON pf.function_id = prv.function_id
JOIN sys.parameters p ON p.object_id = pf.function_id
JOIN sys.types t ON p.user_type_id = t.user_type_id
WHERE pf.name = 'YourPartitionFunctionName';
  • Note the ordinal value mentioned in the error message (%d) and cross-reference it against the boundary_id column to locate the offending value.

Step 2: Compare Boundary Values Against the Declared Data Type

  • Check whether the boundary value at the reported ordinal exceeds the declared type’s range (e.g., a value like 40000 being inserted into a smallint range function, which maxes out at 32767).
  • For decimal/numeric types, verify that precision and scale in the partition function match the precision/scale of the values being passed (e.g., decimal(5,2) cannot hold 123456.78).
  • For varchar/nvarchar boundaries, confirm the string length does not exceed the declared max_length.

Step 3: Recreate the Partition Function with a Compatible Data Type

  • Script out the existing partition function and partition scheme using SSMS (Right-click → Script Partition Function as → CREATE To).
  • Modify the parameter type to a wider or more precise type, for example:
CREATE PARTITION FUNCTION PF_Sales_Range (bigint)
AS RANGE LEFT FOR VALUES (10000, 20000, 40000, 80000);
  • Drop and recreate the function only after confirming no active partition scheme or table depends on it, or use ALTER PARTITION FUNCTION where supported for minor adjustments.

Step 4: Rebuild Dependent Partition Schemes and Tables

  • If the partition function was dropped and recreated, rebuild the associated partition scheme:
CREATE PARTITION SCHEME PS_Sales_Range
AS PARTITION PF_Sales_Range
ALL TO ([PRIMARY]);
  • Re-attach or rebuild any partitioned tables/indexes that referenced the old function to ensure metadata consistency.

Step 5: Validate Source Data Feeding the Partition Logic

  • If boundary values are generated dynamically (e.g., from an ETL job or application config file), audit the source query or script for incorrect casting assumptions.
  • Add explicit CAST/CONVERT statements in the generating script to match the new partition function parameter type exactly.

Alternative or Verification Steps

  • Re-run the original CREATE PARTITION FUNCTION or ALTER PARTITION FUNCTION statement after the data type change — it should complete without Error 7720, and you can confirm by querying sys.partition_range_values again to verify all boundary values display correctly under the new type.
  • If recreating the partition function is not immediately feasible, as a temporary workaround, explicitly cast boundary values in the script to the exact target type (e.g., CAST(40000 AS smallint)) to force early truncation detection and identify every problematic ordinal before applying a permanent fix.