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;
xp_*
- and even then I doubt all of those will ever be gone.