sp_execute, a little help on my output

• BobbyTables

  SSC-Addicted

  Points: 493

  More actions

  September 17, 2019 at 2:21 pm

  #3680716

  I guess I need an extra set of eyes.

  I am trying to get some information about the buffer pool settings.

  i HAVE to use sp_executesql due to an if statement versions less than SQL2014 would not have the DMV i am querying.

  my query below is trying to get two column values, but they always return null.

  I've compared it to examples form MS, and the only difference i see is the number of parameters i am using...two instead of one.

  could someone take a look at this and see what I am missing?

  DECLARE @BufferPoolDescription VARCHAR(60);
  DECLARE @BufferPoolSizeMB VARCHAR(60); --BIGINT?
  
  --SELECT @BufferPoolDescription = cn.state_description, @BufferPoolSizeMB = (current_size_in_kb / 1024) FROM sys.dm_os_buffer_pool_extension_configuration cn
  --IF @SQLversion > 11.00 --12.00 = SQL2014
  EXECUTE [sys].[sp_executesql]  N'SELECT @p1 = cn.state_description,  @p2 = (current_size_in_kb / 1024) FROM sys.dm_os_buffer_pool_extension_configuration cn',
                                 N'@p1 VARCHAR(60) OUTPUT, @p2 VARCHAR(60) OUTPUT ',
                                 N'@p1 = @BufferPoolDescription OUTPUT',
                                 N'@p2 = @BufferPoolSizeMB  OUTPUT';
  
  SELECT @BufferPoolDescription,
         @BufferPoolSizeMB;

   

• rVadim

  Hall of Fame

  Points: 3969

  More actions

  September 17, 2019 at 3:45 pm

  #3680744

  DECLARE @BufferPoolDescription VARCHAR(60);
  DECLARE @BufferPoolSizeMB VARCHAR(60); --BIGINT?
  
  EXECUTE sp_executesql  N'SELECT @p1 = state_description,  @p2 = (current_size_in_kb / 1024) FROM sys.dm_os_buffer_pool_extension_configuration',
                                 N'@p1 VARCHAR(60) OUTPUT, @p2 VARCHAR(60) OUTPUT',
                                 @p1 = @BufferPoolDescription OUTPUT,
                                 @p2 = @BufferPoolSizeMB  OUTPUT;
  
  SELECT @BufferPoolDescription,
         @BufferPoolSizeMB;

  --Vadim R.