大网数据广告活动
数据库与应用安全

Error 556 insert exec failed

· 52CE 我爱测

This article helps you work around the periodic failure of a stored procedure in a database that is using the Query Data Store feature.

This article helps you work around a problem that occurs because a stored procedure in a database that's using the Query Data Store feature periodically fails.

Original product version:   SQL Server 2016
Original KB number:   4465511

Symptoms

Consider the following scenario:

  • You have a Microsoft SQL Server 2016 database that's using the Query Data Store feature.

  • You have a stored procedure that makes a call to another stored procedure by using the INSERT...EXEC syntax.

  • The Query Data Store feature periodically runs auto-cleanup as it increases to its maximum configured size. Additionally, the Query Data Store state changes from READ_WRITE to READ_ONLY.

In this scenario, parent stored procedure execution periodically fails, and you receive an error message that resembles the following:

Msg 556, Level 16, State 2, Line LineNumber
INSERT EXEC failed because the stored procedure altered the schema of the target table.

Cause

The auto-cleanup process flushes the plan out of Query Data Store. The query encounters a recompile operation because the plan is missing from Query Data Store. However, the plan is still present in the procedure cache. By design, when the recompile operation occurs, SQL Server throws error 556 to prevent duplicate execution of the child procedure. Such a duplicate operation would cause incorrect results to be returned.

Resolution

Service Pack information for SQL Server 2016

This issue is fixed in the following service pack for SQL Server:

Service Pack 3 for SQL Server 2016

About service packs for SQL Server:

Service packs are cumulative. Each new service pack contains all the fixes that are in previous service packs, together with any new fixes. We recommend that you apply the latest service pack and the latest cumulative update for that service pack. You don't have to install a previous service pack before you install the latest service pack. Refer to Table 1 in the following article for more information about the latest service pack and latest cumulative update:

How to determine the version, edition and update level of SQL Server and its components

Workaround

To work around this issue, follow these steps:

  1. Increase the size of the Query Data Store. This will reduce the frequency or likelihood of the Query Data Store clearing out the plan and entering the READ_ONLY operating mode.

  2. Add error handling to your code to catch error 556, and then resubmit the INSERT EXEC query.

  3. Clear the procedure cache when Query Data Store returns to READ_WRITE state from READ_ONLY.

Additional information

Because of the changes that were made to Query Data Store in Microsoft SQL Server 2017, this problem doesn't occur in SQL Server 2017.

Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 09/02/2020
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。