index > Microsoft Codename 'Oslo' > Generation of comments in T-SQL output

Generation of comments in T-SQL output

Hi there,

I'm currently debugging a piece of the T-SQL compiled output. (mSchema -> T-SQL) It doesn't seem to function, i think its a bug (or unfinished feature). The sql output is complex, though i think i know what it should do (as that was defined in M). What might be valuable for debugging the compiled T-SQL is to have comments on its output.

For example: Why is something happening and what actually is happening. As well as the M responsible for creating that code. Also the comments manually typed before extents or types might be useful to transfer to the compiled output.

My 2 cents.

Functions like the following might be correct, but are hard to understand or bugfix:

create function [tests].[Check_firsttypes_Func]

(

  @_firstpartofkey as nvarchar(20)

,   @_secondpartofkey as nvarchar(45)

,   @_somevalue as nvarchar(max)

)

returns bit  as

  begin

    return case

  when not (not (exists ((

    select [$temp].[firstpartofkey] as [Item]

    from [tests].[firsttypes] as [$temp]

except

    select [$temp].[firstpartofkey] as [Item]

    from [tests].[secondtypes] as [$temp]

)



... and another 50 lines of the same select except

Edit: added the stuff i was working on as a bug: https://connect.microsoft.com/oslo/feedback/ViewFeedback.aspx?FeedbackID=478541

Performing my Final Project, looking into codename "Oslo".

Edited byElger [Centric] Thursday, July 30, 2009 1:56 PMadded bug on the content, not on the comment discussion

Elger [Centric]

Fabulous feedback, Elger; I can certainly see the value of your proposal. Thanks for putting it into Connect right away where the product team can see it.

.Kraig

Kraig Brockschmidt

I just wanted to add that in addition to descriptive inline comments in the generated T-SQL it would also be very nice to be able to use comments in M to generate T-sql extended propertymetadata/descriptions. A bit like how XML comments can be used in C# to generate documentation...

E.g.

///<ExtProp type="MS_Description">The RefData schema contains all reference tables and types</ExtProp>

module RefData
{
    ///<ExtProp type="MS_Description">The IdentifyingCode type is used in reference tables with a relatively static small set of values as the primary key</ExtProp>

    type IdentifyingCode: Text#10; //general identifying code
    
    ///<ExtProp type="MS_Description">The Description type holds a short textual description of refdata</ExtProp>

    type Description: Text#255; //descriptive text
    
    ///<ExtProp type="MS_Description>Fields using the ActiveStatus type denotes record status for reference data; active/inactive/deleted</ExtProp>

    type ActiveStatus: (Text#1) where value in {"A", "I", "D"}; //denotes record status, active/inactive/deleted
 
    //4.1.2: Seasons
    ///<ExtProp type="MS_Description">Season ref table containing the definitions (start/end/description) of schedule seasons</ExtProp>

    type SeasonType
    {
        SeasonCode: RefData.IdentifyingCode;
        SeasonDescription: Description;
        Status: ActiveStatus;
        SeasonStartDate: DateTime;
        SeasonEndDate: DateTime;
    } where identity SeasonCode;
    Season: SeasonType*;

}

....and in the T-SQL get matching sp_addextendedproperty / sp_updateextendedproperty calls for the extended properties contained in xml comments (or attributes, or whatever would be the preferred way to include them in the model).

exec sp_addextendedproperty @name = N'MS_Description', @value = 'The RefData schema contains all reference tables and types', @level0type = N'Schema', @level0name = N'RefData'
go



exec sp_addextendedproperty @name = N'MS_Description', @value = 'Season ref table containing the definitions (start/end/description) of schedule seasons', @level0type = N'Schema', @level0name = N'RefData', @level1type = N'Table',  @level1name = N'Season'
go

...but of course not only descriptions, but also other (user defined) SQL Server extended properties...

Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)

KristoferA

You can use google to search for other answers

Custom Search

More Threads

• ParseToken "end" SourcePoint

• Installer problem in OSLO January CTP

• Nullable value in query

• Correct MGrammar projection for lists

• MGraph: References

• Oslo will progress the way Windows 1.0 did

• "M Compiler Visual studio Integration cannot be installed ..."

• CreateRepository.exe failed with error code 0x6e

• MGrammar: nest syntax and line termination

• Quadrant availability for non-PDC attendees?