Post reply

How to determine metadata on DBCC results

  • May 26, 2015 at 2:17 pm

    #318460

    I can find many examples of loading DBCC results into tables. They all begin with a create table statement defining the results. My question is , other than trial and error, is there a way to determine what data types will be returned. Sure you can say that first element looks like an integer, but is it really a bigint, and that text string can be varchar(max) but will char(2) work.

    I'm not looking for an answer for a specific DBCC function, but rather a generic way I can determine the characteristics of any DBCC result set.

    I tried

    SELECT *

    INTO #tmp

    FROM OPENROWSET('SQLOLEDB',

    'Server=ray;Trusted_Connection=Yes;Database=Ed_sandbox',

    'Set FmtOnly OFF; DBCC loginfo WITH tableresults ')

    but I got back

    Msg 11527, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1

    The metadata could not be determined because statement 'DBCC loginfo WITH tableresults' does not support metadata discovery.

    Thanks

  • May 26, 2015 at 4:58 pm

    #1800720

    It used to be possible using a "LoopBack" linked server but the latest "improvements" in SQL Server 2012 put the kibosh on all that. Now, all you get is the following...

    Msg 11528, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1

    The metadata could not be determined because statement 'DBCC LOGINFO();' in procedure 'loginfo' does not support metadata discovery.

    Don'cha just love progress? Now, duck! Here come all the folks that are going to yell about using undocumented stored procedures. 😀 The thing is, this undocumented procedure didn't change... their other "improvements" broke it. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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