ADF Stored Procedure activity passes a string parameter to a DATETIME or INT column. ADF defaults all parameters to String unless the type is set explicitly in the activity, so the stored procedure receives '2026-04-25T00:00:00' as nvarchar and SQL Server fails the implicit cast.. A source system changes its date format — for example from 'yyyy-MM-dd' to 'dd/MM/yyyy' — without a corresponding change in the pipeline. The downstream CONVERT or implicit cast breaks only on rows with the new format, making this a partial-failure bug that is hard to catch in testing.. A value in the source column is outside the range of the target SQL Server type. SMALLDATETIME accepts dates up to 2079-06-06; TINYINT accepts 0–255; SMALLINT accepts −32768 to 32767. A single out-of-range row in a bulk load will abort the entire batch.. A dbt model casts a column using {{ dbt_utils.safe_cast }} or a raw CAST that encounters NULL-adjacent bad data (e.g. empty string '' cast to INT), raising 8114 instead of a NULL. This happens when the source table has loose typing and the warehouse schema expects strict types.. A MERGE statement or INSERT...SELECT joins two tables where one side stores amounts as NVARCHAR and the other expects DECIMAL. SQL Server attempts implicit conversion row-by-row and fails on the first value that contains a comma, currency symbol, or whitespace.. A Synapse or Azure SQL linked server query pushes a predicate down to a remote SQL Server where column collation or type differs. The remote server raises 8114 on the implicit cast, and the error surfaces in the calling pipeline without a clear pointer to the remote table.. A legacy SSIS package or stored procedure uses SET IMPLICIT_TRANSACTIONS ON with a datatype mismatch in a batch — the transaction rolls back on 8114, and the error appears in ADF logs as a generic activity failure with the original 8114 nested inside.

Step 1 — Identify the exact value that fails. Run TRY_CONVERT against the source column to find all rows that cannot be converted: SELECT id, suspect_col FROM source_table WHERE TRY_CONVERT(datetime, suspect_col) IS NULL AND suspect_col IS NOT NULL; This returns only the offending rows without raising an error.. Step 2 — Check the error message for the type pair. The message reads 'Error converting data type X to Y'. Map X and Y to find the conversion point. If X is nvarchar and Y is datetime, the fix is a format-aware CONVERT: SELECT CONVERT(datetime, '2026-04-25', 120); Style code 120 = ISO yyyy-MM-dd HH:mm:ss. A full list of style codes: https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql. Step 3 — In ADF Stored Procedure activities: open the activity → Parameters tab → change the Type dropdown from String to DateTime (or the matching SQL type). ADF will serialize the value in a SQL Server-compatible format before sending it, bypassing implicit conversion entirely.. Step 4 — In ADF Data Flow or Copy Activity with a Derived Column transformation, add an explicit cast before the sink: toTimestamp(col, 'yyyy-MM-dd HH:mm:ss') for dates, or toInteger(col) for numerics. Place this derived column upstream of any type-sensitive sink mapping.. Step 5 — If the error occurs in a stored procedure called from ADF, add TRY_CONVERT inside the procedure and route bad rows to a quarantine table instead of raising 8114: INSERT INTO bad_rows SELECT * FROM @input WHERE TRY_CONVERT(datetime, date_col) IS NULL; This keeps the pipeline running and gives you an audit trail.. Step 6 — For bulk insert or MERGE failures, add a pre-copy SQL in ADF (Copy Activity → Settings → Pre-copy script) to truncate or stage the data first, then cast explicitly in the INSERT...SELECT that moves data into the final table: INSERT INTO final SELECT CONVERT(decimal(18,2), REPLACE(amount_col, ',', '')) FROM staging;. Step 7 — Prevent recurrence: add a data quality check as an ADF pipeline activity before the load step. Use a Lookup activity with the query from Step 1 and a conditional branch: if row count > 0, fail the pipeline and send an alert rather than letting SQL Server raise 8114 mid-load.

Medium severitydata integrity

SQL Server Error:
8114

Impact

An ADF pipeline that processes thousands of rows will abort the entire activity on the first 8114, leaving downstream tables stale or partially loaded. If the error appears after a source-system update, assume all rows with the new format are affected — not just the one SQL Server reported.

Error 8114 is a hard failure — SQL Server does not silently skip the bad row. The ADF activity status becomes Failed and no retry will succeed until the root cause is fixed. If the activity is part of a pipeline with dependent activities (e.g. a Transform → Load → Notify chain), all downstream activities are skipped. Reporting tables that depend on the load remain at their last successful state, which means dashboards show stale data without any visible error unless the pipeline is monitored. SLA impact is proportional to the refresh frequency: hourly pipelines can drift by one hour; daily pipelines by a full day.

What does this error mean?

SQL Server error 8114 is raised when an implicit or explicit data type conversion fails at runtime — either because the value format is incompatible (e.g. a string '25-04-2026' passed to a DATETIME parameter) or the value falls outside the valid range of the target type (e.g. year 9999 into SMALLDATETIME, which only supports up to 2079). In a data pipeline context this typically surfaces as a failed ADF activity, a dbt model error, or a rejected row in a bulk insert. The error message names the source and target type, but not the offending row — which makes diagnosis slower than it should be.

Common causes

1ADF Stored Procedure activity passes a string parameter to a DATETIME or INT column. ADF defaults all parameters to String unless the type is set explicitly in the activity, so the stored procedure receives '2026-04-25T00:00:00' as nvarchar and SQL Server fails the implicit cast.
2A source system changes its date format — for example from 'yyyy-MM-dd' to 'dd/MM/yyyy' — without a corresponding change in the pipeline. The downstream CONVERT or implicit cast breaks only on rows with the new format, making this a partial-failure bug that is hard to catch in testing.
3A value in the source column is outside the range of the target SQL Server type. SMALLDATETIME accepts dates up to 2079-06-06; TINYINT accepts 0–255; SMALLINT accepts −32768 to 32767. A single out-of-range row in a bulk load will abort the entire batch.
4A dbt model casts a column using {{ dbt_utils.safe_cast }} or a raw CAST that encounters NULL-adjacent bad data (e.g. empty string '' cast to INT), raising 8114 instead of a NULL. This happens when the source table has loose typing and the warehouse schema expects strict types.
5A MERGE statement or INSERT...SELECT joins two tables where one side stores amounts as NVARCHAR and the other expects DECIMAL. SQL Server attempts implicit conversion row-by-row and fails on the first value that contains a comma, currency symbol, or whitespace.
6A Synapse or Azure SQL linked server query pushes a predicate down to a remote SQL Server where column collation or type differs. The remote server raises 8114 on the implicit cast, and the error surfaces in the calling pipeline without a clear pointer to the remote table.
7A legacy SSIS package or stored procedure uses SET IMPLICIT_TRANSACTIONS ON with a datatype mismatch in a batch — the transaction rolls back on 8114, and the error appears in ADF logs as a generic activity failure with the original 8114 nested inside.

How to fix it

1Step 1 — Identify the exact value that fails. Run TRY_CONVERT against the source column to find all rows that cannot be converted: SELECT id, suspect_col FROM source_table WHERE TRY_CONVERT(datetime, suspect_col) IS NULL AND suspect_col IS NOT NULL; This returns only the offending rows without raising an error.
2Step 2 — Check the error message for the type pair. The message reads 'Error converting data type X to Y'. Map X and Y to find the conversion point. If X is nvarchar and Y is datetime, the fix is a format-aware CONVERT: SELECT CONVERT(datetime, '2026-04-25', 120); Style code 120 = ISO yyyy-MM-dd HH:mm:ss. A full list of style codes: https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql
3Step 3 — In ADF Stored Procedure activities: open the activity → Parameters tab → change the Type dropdown from String to DateTime (or the matching SQL type). ADF will serialize the value in a SQL Server-compatible format before sending it, bypassing implicit conversion entirely.
4Step 4 — In ADF Data Flow or Copy Activity with a Derived Column transformation, add an explicit cast before the sink: toTimestamp(col, 'yyyy-MM-dd HH:mm:ss') for dates, or toInteger(col) for numerics. Place this derived column upstream of any type-sensitive sink mapping.
5Step 5 — If the error occurs in a stored procedure called from ADF, add TRY_CONVERT inside the procedure and route bad rows to a quarantine table instead of raising 8114: INSERT INTO bad_rows SELECT * FROM @input WHERE TRY_CONVERT(datetime, date_col) IS NULL; This keeps the pipeline running and gives you an audit trail.
6Step 6 — For bulk insert or MERGE failures, add a pre-copy SQL in ADF (Copy Activity → Settings → Pre-copy script) to truncate or stage the data first, then cast explicitly in the INSERT...SELECT that moves data into the final table: INSERT INTO final SELECT CONVERT(decimal(18,2), REPLACE(amount_col, ',', '')) FROM staging;
7Step 7 — Prevent recurrence: add a data quality check as an ADF pipeline activity before the load step. Use a Lookup activity with the query from Step 1 and a conditional branch: if row count > 0, fail the pipeline and send an alert rather than letting SQL Server raise 8114 mid-load.

Example log output

ErrorCode=UserErrorExecuteCommandFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Error happened when trying to execute the command. Error: Error converting data type nvarchar to datetime.,Source=Microsoft.DataTransfer.ClientLibrary'
SQL Server Error 8114: Error converting data type nvarchar to datetime.
Statement: EXEC dbo.usp_load_sales @load_date = N'25-04-2026', @region = N'NL'

Frequently asked questions

How do I find which row causes SQL Server error 8114?

Use TRY_CONVERT to scan the column without raising an error: SELECT id, suspect_col FROM source_table WHERE TRY_CONVERT(datetime, suspect_col) IS NULL AND suspect_col IS NOT NULL; This returns all unconvertible rows. If you are inside a stored procedure, wrap the CONVERT in a TRY/CATCH and log @@ROWCOUNT and ERROR_MESSAGE() to a diagnostics table.

Why does SQL Server 8114 only appear after an ADF pipeline change or source update?

8114 is triggered at runtime, not at schema definition time. A format change in the source (e.g. date format, decimal separator, or numeric range) passes validation but fails the first time a value is actually cast. Because SQL Server stops at the first bad row in a batch, earlier runs with clean data succeed and the error only appears when the first non-conforming row arrives.

Can I make ADF retry after a 8114 error?

Retrying the same activity will always reproduce 8114 because the bad data is still in the source. Configure a retry policy only after adding a pre-filter step that skips or quarantines unconvertible rows. Without the fix, a retry wastes time and may lock staging tables during the failed transaction.

What is the difference between CAST, CONVERT, and TRY_CONVERT for handling 8114?

CAST and CONVERT both raise 8114 on failure. CONVERT additionally accepts a style code (third argument) that tells SQL Server the exact format of the input string — critical for dates. TRY_CONVERT returns NULL on failure instead of an error, making it safe for scanning bad rows. Use TRY_CONVERT to diagnose, CONVERT with a style code to fix, and never rely on SQL Server's implicit conversion for date or numeric strings in a pipeline.

Source · learn.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-events-and-errors