sp_execute, a little help on my output

  • 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;

     

  • 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.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply