5

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?

1
  • 5
    I really doubt it. The deprecation announcement is likely just around the ones that are explicitly named xp_* - and even then I doubt all of those will ever be gone. Feb 12, 2015 at 22:32

1 Answer 1

4

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.