Does XP_API deprecation announcement mean that sp_executesql will not be supported in the future?

Question

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?

Answer 1

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:

srv_alloc
srv_convert
srv_describe
srv_getbindtoken
srv_got_attention
srv_message_handler
srv_paramdata
srv_paraminfo
srv_paramlen
srv_parammaxlen
srv_paramname
srv_paramnumber
srv_paramset
srv_paramsetoutput
srv_paramstatus
srv_paramtype
srv_pfield
srv_pfieldex
srv_rpcdb
srv_rpcname
srv_rpcnumber
srv_rpcoptions
srv_rpcowner
srv_rpcparams
srv_senddone
srv_sendmsg
srv_sendrow
srv_setcoldata
srv_setcollen
srv_setutype
srv_willconvert
srv_wsendmsg

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 and deprecation_final_support events, you don't see sp_executesql firing events. To test that, I used this session definition:

CREATE EVENT SESSION [deprecated events] ON SERVER 
ADD EVENT sqlserver.deprecation_announcement
(
    ACTION (
        package0.event_sequence
        ,sqlserver.client_app_name
        ,sqlserver.database_name
        ,sqlserver.is_system,sqlserver.sql_text
        )
)
, ADD EVENT sqlserver.deprecation_final_support
    (
        ACTION
        (
            package0.event_sequence
            ,sqlserver.client_app_name
            ,sqlserver.database_name
            ,sqlserver.is_system
            ,sqlserver.sql_text
        )
    )
ADD TARGET package0.ring_buffer (SET max_memory=(10240))
WITH (STARTUP_STATE=OFF);
ALTER EVENT SESSION [deprecated events] ON SERVER STATE=START;
GO

Then ran this code, which uses sp_executesql:

DECLARE @cmd nvarchar(max);
SET @cmd = 'SELECT CurrentDate = GETDATE();';
EXEC sys.sp_executesql @cmd;

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 contains sp_helpremotelogin, which is on the deprecated list, you do see items in the ring-buffer output:

EXEC sp_helpremotelogin @remoteserver = 'test', @remotename = NULL;