When Does sp_prepare Return Metadata
I was running an RML Utilities Suite test pass and encountered varying behavior from our sp_prepare suite. Here is what I uncovered.
The command sp_prepare returns (or does not return) metadata depending on the server version. For the client version, it is only significant whether it is prior to SQL 2012 or it is a later one (i.e. 2012 RTM, SP1, etc.).
1. Prior to SQL 2012, sp_prepare returns metadata to the user. This was implemented by internally setting FMTONLY ON and executing the statement.
2. In SQL 2012 RTM and SP1, sp_prepare does NOT return metadata, if client version is 2012 or greater. FMTONLY ON is deprecated and used only for backward compatibility with the older (i.e. 2008) clients.
3. In SQL 2012 CU6 (build 11.0.2401.0) and later, and SP1 CU3 and later, sp_prepare DOES return metadata to the user, if the batch contains one statement. This is to address a performance issue with some scenarios (see hotfix KB2772525).
The following matrix shows when sp_prepare should return metadata for batches containing one statement.
Client\Server Version |
2008/R2 |
2012 RTM |
2012 CU6 + |
2012 SP1 |
2012 SP1 CU3 + |
SQL 14 |
2008 R2 |
yes |
yes |
yes |
yes |
yes |
yes |
2012 (all versions) |
yes |
no |
yes |
no |
yes |
yes |
SQL 14 CTP |
yes |
no |
yes |
no |
yes |
yes |
yes - sp_prepare returns metadata
no - sp_prepare does NOT return metadata
The following matrix shows when sp_prepare should return metadata for multi-statement batches, such as
declare @p1 int
set @p1=NULL
exec sp_prepare @p1 output,NULL,N'select * from sys.objects; select 1;',1
select @p1
Client\Server Version |
2008/R2 |
2012 RTM |
2012 CU6 + |
2012 SP1 |
2012 SP1 CU3 + |
SQL 14 |
2008 R2 |
yes |
yes |
yes |
yes |
yes |
yes |
2012 (all versions) |
yes |
no |
no |
no |
no |
no |
SQL 14 CTP |
yes |
no |
no |
no |
no |
no |
Bob Dorr - Principal SQL Server Escalation Engineer