I have setup an Extended Events session in SQL 2008 R2 to track the deprecation_final_support and deprecation_announcement events. I noticed some "deprecation_announcement" events in my log with "feature" of "XP_API", which appear to all be SQL statements that use SP_EXECUTESQL
.
The event message says:
Extended stored procedure API will be removed in a future version of
SQL Server. Avoid using this feature in new development work, and plan
to modify applications that currently use it.
One example of the sql_text
action from an event is:
exec sp_executesql N'DECLARE @c varbinary(128);SET @c=cast(@userName as varbinary(128));SET CONTEXT_INFO @c;', N'@userName varchar(128)', @userName = 'MYDOMAIN\myuser';
The master.sys.SP_EXECUTESQL
proc is listed under "Databases > System Databases > master > Programmability > Extended Stored Procedures > System Extended Stored Procedures" in Object Explorer.
Does this mean that sp_executesql
will not be supported in a future version of SQL Server?
Best Answer
The Deprecated Database Engine Feature List for SQL Server 2017 shows
XP_API
as the "feature name" for a list of now-unsupported features. The list of deprecated features consists of:These items are all part of the Extended Stored Procedure API, which is deprecated.
The documentation for
sp_executesql
makes no mention of it being deprecated. Although that might be an error of ommission, it seems very unlikely that a core piece of functionality will be deprecated. Imagine all the code world-wide that would need to be retrofitted.The deprecation-related Extended Events items are likely flagging some internal use by sp_executesql of one or more of the
xp_api
items listed above. Since sp_executesql is an extended stored procedure implemented in the Engine, Microsoft will either re-factor the sp_executesql code to avoid calls to those deprecated events, or will not deprecate them for internal use.This question is tagged with SQL Server 2008 R2; if you run an Extended Events session in SQL Server 2017 looking at the
deprecation_announcement
anddeprecation_final_support
events, you don't seesp_executesql
firing events. To test that, I used this session definition:Then ran this code, which uses
sp_executesql
:The ring buffer output for the
[deprecated events]
session shows no messages as a result of executing the code. However, if you run this code, which containssp_helpremotelogin
, which is on the deprecated list, you do see items in the ring-buffer output: