I just noticed the extended properties of the adventureworks DB are not showing up as custom properties in the 3.0 designer catalog explorer. My project file is here: AW.llblgenproj. I've tried all the setting's I can find, on 2 different machines and starting from a blank project

Did you see the custom properties be retrieved in the relational model data? (so click 'show details' on 'tables' to see which tables have custom properties)

Otis wrote:

Did you see the custom properties be retrieved in the relational model data? (so click 'show details' on 'tables' to see which tables have custom properties)

No custom properties in the details of any tables.

Hmm, I see that happen here too. Odd. We'll look into it.

Could you run this query on your database:

SELECT      o.Name AS ObjectName,
            o.type AS ObjectType,
            s.name AS SchemaOwner,
            ep.name AS PropertyName,
            ep.value AS PropertyValue,
            c.name AS ColumnName,
            c.colid AS Ordinal
FROM        sys.objects o INNER JOIN sys.extended_properties ep
            ON o.object_id = ep.major_id
            INNER JOIN sys.schemas s
            ON o.schema_id = s.schema_id
            LEFT JOIN syscolumns c
            ON ep.minor_id = c.colid
            AND ep.major_id = c.id
WHERE       o.type IN ('V', 'U', 'P')
            AND ep.name NOT LIKE 'MS_%'
ORDER BY    SchemaOwner,ObjectName, ObjectType, Ordinal

I tested it on our adventureworks (SQLServer 2005) and it doesn't contain any extended properties, other db's do.

Btw, we do filter out 'MS_*' extended properties, as those are in general a big pain as they're in general large crufty pieces of text/garbage not really usable.

I do see that adventureworks tables have MS_Description extended properties... Are you looking for those, perhaps?

Otis wrote:

Could you run this query on your database:

SELECT      o.Name AS ObjectName,
            o.type AS ObjectType,
            s.name AS SchemaOwner,
            ep.name AS PropertyName,
            ep.value AS PropertyValue,
            c.name AS ColumnName,
            c.colid AS Ordinal
FROM        sys.objects o INNER JOIN sys.extended_properties ep
            ON o.object_id = ep.major_id
            INNER JOIN sys.schemas s
            ON o.schema_id = s.schema_id
            LEFT JOIN syscolumns c
            ON ep.minor_id = c.colid
            AND ep.major_id = c.id
WHERE       o.type IN ('V', 'U', 'P')
            AND ep.name NOT LIKE 'MS_%'
ORDER BY    SchemaOwner,ObjectName, ObjectType, Ordinal

I tested it on our adventureworks (SQLServer 2005) and it doesn't contain any extended properties, other db's do.

Btw, we do filter out 'MS_*' extended properties, as those are in general a big pain as they're in general large crufty pieces of text/garbage not really usable.

Since when? Query returned no results.

Otis wrote:

I do see that adventureworks tables have MS_Description extended properties... Are you looking for those, perhaps?

Yes

TomDog wrote:

Otis wrote:

Could you run this query on your database:

SELECT      o.Name AS ObjectName,
            o.type AS ObjectType,
            s.name AS SchemaOwner,
            ep.name AS PropertyName,
            ep.value AS PropertyValue,
            c.name AS ColumnName,
            c.colid AS Ordinal
FROM        sys.objects o INNER JOIN sys.extended_properties ep
            ON o.object_id = ep.major_id
            INNER JOIN sys.schemas s
            ON o.schema_id = s.schema_id
            LEFT JOIN syscolumns c
            ON ep.minor_id = c.colid
            AND ep.major_id = c.id
WHERE       o.type IN ('V', 'U', 'P')
            AND ep.name NOT LIKE 'MS_%'
ORDER BY    SchemaOwner,ObjectName, ObjectType, Ordinal

I tested it on our adventureworks (SQLServer 2005) and it doesn't contain any extended properties, other db's do.

Btw, we do filter out 'MS_*' extended properties, as those are in general a big pain as they're in general large crufty pieces of text/garbage not really usable.

Since when? Query returned no results.

Since v3, but I think even earlier. I have to check the sourcecode of v2's drivers for that

Otis wrote:

I do see that adventureworks tables have MS_Description extended properties... Are you looking for those, perhaps?

Yes

That's currently not possible as you can see, the query contains a NOT LIKE 'MS_%' predicate. I'll look into allowing MS_Description.

2.6 definitely bought MS_Description through. FTR I've been using http://datadictionary.codeplex.com for maintaining our data dictionary which uses MS_Description by default. An option to include MS_Description would be appreciated.

New Zealand 1 - Italy 1, Unbelievable!!!!!! Go All Whites!

TomDog wrote:

2.6 definitely bought MS_Description through. FTR I've been using http://datadictionary.codeplex.com for maintaining our data dictionary which uses MS_Description by default. An option to include MS_Description would be appreciated.

v2.x indeed didn't filter out anything, which made us actually disable the feature by default. I've added MS_Description as a valuable custom property, the rest of the MS_* stuff is filtered out.

The driver is attached.

New Zealand 1 - Italy 1, Unbelievable!!!!!! Go All Whites!

Heh . The referee was horrible. Every time an italian hit the grass, they whined as if they were hit by a baseball bat and the referee bought it every time. I must say that NZ wasn't that great, but italy wasn't great either (but... we weren't either ). Will be interesting if they both make it to the next round (and perhaps meet us in the next round )

Otis wrote:

TomDog wrote:

2.6 definitely bought MS_Description through. FTR I've been using http://datadictionary.codeplex.com for maintaining our data dictionary which uses MS_Description by default. An option to include MS_Description would be appreciated.

v2.x indeed didn't filter out anything, which made us actually disable the feature by default. I've added MS_Description as a valuable custom property, the rest of the MS_* stuff is filtered out.

The driver is attached.

Yep all good. I've never come across other MS_* custom properties though a quick google indicates access can put some in.

Otis wrote:

New Zealand 1 - Italy 1, Unbelievable!!!!!! Go All Whites!

Heh . The referee was horrible. Every time an Italian hit the grass, they whined as if they were hit by a baseball bat and the referee bought it every time. I must say that NZ wasn't that great, but Italy wasn't great either (but... we weren't either ). Will be interesting if they both make it to the next round (and perhaps meet us in the next round )

Man I hope so but unlike your lot that was about as well as we can play - I can't see our boys route #1 donkey work getting us pass Paraguay(France or England maybe) but you never know...

TomDog wrote:

Otis wrote:

TomDog wrote:

2.6 definitely bought MS_Description through. FTR I've been using http://datadictionary.codeplex.com for maintaining our data dictionary which uses MS_Description by default. An option to include MS_Description would be appreciated.

v2.x indeed didn't filter out anything, which made us actually disable the feature by default. I've added MS_Description as a valuable custom property, the rest of the MS_* stuff is filtered out.

The driver is attached.

Yep all good. I've never come across other MS_* custom properties though a quick google indicates access can put some in.

yeah and pretty nasty ones as well, hence the filter

Otis wrote:

New Zealand 1 - Italy 1, Unbelievable!!!!!! Go All Whites!

Heh . The referee was horrible. Every time an Italian hit the grass, they whined as if they were hit by a baseball bat and the referee bought it every time. I must say that NZ wasn't that great, but Italy wasn't great either (but... we weren't either ). Will be interesting if they both make it to the next round (and perhaps meet us in the next round )

Man I hope so but unlike your lot that was about as well as we can play - I can't see our boys route #1 donkey work getting us pass Paraguay(France or England maybe) but you never know...

with this silly ball they're using, anything can happen .

Correct me if I'm wrong but to get the MS_Description's into the entities I would need to use something like LLBLGen Site -> Customer Area -> Downloads for LLBLGen Pro v2.0 -> 3rd party -> Plug-in for refreshing custom properties.

Also the MS_Description's didn't seem to come through in the 2.6->3.0 conversion, is that expected?

TomDog wrote:

Correct me if I'm wrong but to get the MS_Description's into the entities I would need to use something like LLBLGen Site -> Customer Area -> Downloads for LLBLGen Pro v2.0 -> 3rd party -> Plug-in for refreshing custom properties.

Set UpdateCustomPropertiesAfterRefresh to true (controlled by preference setting, likely this is false)

Also the MS_Description's didn't seem to come through in the 2.6->3.0 conversion, is that expected?

It's very likely they're skipped. Will look into this.

Otis wrote:

TomDog wrote:

Correct me if I'm wrong but to get the MS_Description's into the entities I would need to use something like LLBLGen Site -> Customer Area -> Downloads for LLBLGen Pro v2.0 -> 3rd party -> Plug-in for refreshing custom properties.

Set UpdateCustomPropertiesAfterRefresh to true (controlled by preference setting, likely this is false)

Also the MS_Description's didn't seem to come through in the 2.6->3.0 conversion, is that expected?

It's very likely they're skipped. Will look into this.

They were indeed skipped. Next build won't do that anymore.

Otis wrote:

TomDog wrote:

Correct me if I'm wrong but to get the MS_Description's into the entities I would need to use something like LLBLGen Site -> Customer Area -> Downloads for LLBLGen Pro v2.0 -> 3rd party -> Plug-in for refreshing custom properties.

Set UpdateCustomPropertiesAfterRefresh to true (controlled by preference setting, likely this is false)

Didn't think so but for argument sake say it was false and I now have the situation where the MS_Description's are in the catalog but not the entity how do I then get them into the entity? Turn it on and do a DB refresh? I tried that and didn't work.

I've tried starting the project from scratch and the MS_Description's got into the entities fine.

TomDog wrote:

Otis wrote:

TomDog wrote:

Correct me if I'm wrong but to get the MS_Description's into the entities I would need to use something like LLBLGen Site -> Customer Area -> Downloads for LLBLGen Pro v2.0 -> 3rd party -> Plug-in for refreshing custom properties.

Set UpdateCustomPropertiesAfterRefresh to true (controlled by preference setting, likely this is false)

Didn't think so but for argument sake say it was false and I now have the situation where the MS_Description's are in the catalog but not the entity how do I then get them into the entity? Turn it on and do a DB refresh? I tried that and didn't work.

I've tried starting the project from scratch and the MS_Description's got into the entities fine.

Hmm... I think the code updates only custom properties which are already there (e.g. you have a custom property named "MS_Description", you refresh the catalog, and it refreshes the value of it. We'll look into it.

(edit) indeed, we only update existing custom properties so refresh the existing custom property "MS_Description" with a new value, we don't add new ones.

The reason is that if we'd do that, the set of custom properties would only grow, never shrink, as it's unclear when to remove a custom property (as they don't have to exist in the db, they're a model construct).

Using element search, it's possible to sync the custom properties however. If you want I can formulate a piece of C# code for you which will do that. (so you don't really search, but use the feature to traverse the model and alter elements )

Otis wrote:

Using element search, it's possible to sync the custom properties however. If you want I can formulate a piece of C# code for you which will do that. (so you don't really search, but use the feature to traverse the model and alter elements )

Yeah that would be grand, I could try refresh on a version of the project before the MS_Description's got into the catalog but an example of how to sync custom properties would handy to have - and instructive.

It's really straight forward, see the code below. It merges the target (table, table field etc.) custom properties with the mapped element's custom properties: any custom property which isn't in the model custom properties is added, if it already exists, it's set to the value of the target custom property entry.

Open element search and select 'Entity' in the top combo box. Paste the code below and hit Run query. If you had an entity open in the editor, you have to re-open it in the editor, as the custom properties dialog doesn't refresh itself after this routine.

foreach(var entity in p.EntityModel.Vertices)
{
    var mappings = p.GetAllMappingsForGroupableModelElement(entity);
    foreach(GroupableModelElementMapping mapping in mappings)
    {
        if(mapping.MappedTarget==null)
        {
            continue;
        }
        
        mapping.MappedElement.OutputSettingValues.CustomProperties.MergeCustomProperties(
            mapping.MappedTarget.CustomProperties);
        foreach(FieldMapping fieldMapping in mapping.FieldMappings)
        {
            if(fieldMapping.MappedTarget==null)
            {
                continue;
            }
            fieldMapping.MappedFieldInstance.OutputSettingValues.CustomProperties.MergeCustomProperties(
                            fieldMapping.MappedTarget.CustomProperties);
        }
    }
}
return p.EntityModel.Vertices;

Yep, worked a treat - thanks!