distal-attribute
distal-attribute
distal-attribute
distal-attribute

SQL Server: comments missing from some columns

kalvaro posted 12 months ago in General

I've just noticed that grid in table editor only displays comments for some of the columns that actually have one:

However, if I run manually the query that shows in the log pane all comments are retrieved (though they're formatted as if they were... numbers?):

SELECT c.name AS "column", prop.value AS "comment"
FROM sys.extended_properties AS prop
INNER JOIN sys.all_objects o ON prop.major_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.columns AS c ON prop.major_id = c.object_id AND prop.minor_id = c.column_id
WHERE prop.name='MS_Description' AND s.name='dbo' AND o.name='IMPORTACION';

Any idea about this? My Server runs SQL Server 2008.

ansgar posted 12 months ago

Should be fixed in r5088. Forgot to reset the separate result set for comments in an inner loop.

kalvaro posted 12 months ago

Thank you!

Now I can see the comment for FECHA_ORIGINAL but the comment for FECHA is still missing...

ansgar posted 12 months ago

Odd. Can you post some CREATE code for that table here so I can try to reproduce that?

kalvaro posted 11 months ago

Sorry, I rely on RSS to get notifications and I didn't see this.

Is this enough to reproduce it?

CREATE TABLE IMPORTACION (
    IMPORTACION_ID INT IDENTITY(1, 1) NOT NULL,
    EDIFICIOS_ID INT,
    FECHA DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FECHA_ORIGINAL DATETIME,
    CODIGO_ITE VARCHAR(19),
    NUM_EXP VARCHAR(50),
    PRESUPUESTO_OBRA DECIMAL(18,2),
    EXPEDIENTE_OBRA VARCHAR(50),
    FORMATO_IMPORTACION_ID TINYINT DEFAULT 1 NOT NULL,

    CONSTRAINT IMPORTACION_PK PRIMARY KEY (IMPORTACION_ID)
);

EXEC sys.sp_addextendedproperty
    @name=N'MS_Description', @value=N'Importacion: Control de importaciones de inspecciones',
    @level0type=N'SCHEMA', @level0name=N'dbo',
    @level1type=N'TABLE', @level1name=N'IMPORTACION';

EXEC sys.sp_addextendedproperty
    @name=N'MS_Description', @value=N'Secuencia',
    @level0type=N'SCHEMA', @level0name=N'dbo',
    @level1type=N'TABLE', @level1name=N'IMPORTACION',
    @level2type=N'COLUMN', @level2name=N'IMPORTACION_ID';

EXEC sys.sp_addextendedproperty
    @name=N'MS_Description', @value=N'Edificio de Itcweb al que pertenece; las filas sin edificio son inspecciones temporales que no se han confirmado (subido) todavía',
    @level0type=N'SCHEMA', @level0name=N'dbo',
    @level1type=N'TABLE', @level1name=N'IMPORTACION',
    @level2type=N'COLUMN', @level2name=N'EDIFICIOS_ID';

EXEC sys.sp_addextendedproperty
    @name=N'MS_Description', @value=N'Fecha de carga en este Recite',
    @level0type=N'SCHEMA', @level0name=N'dbo',
    @level1type=N'TABLE', @level1name=N'IMPORTACION',
    @level2type=N'COLUMN', @level2name=N'FECHA';

EXEC sys.sp_addextendedproperty
    @name=N'MS_Description', @value=N'Si se ha recibido a través del API, fecha de carga en la aplicación original (p.ej. Remite)',
    @level0type=N'SCHEMA', @level0name=N'dbo',
    @level1type=N'TABLE', @level1name=N'IMPORTACION',
    @level2type=N'COLUMN', @level2name=N'FECHA_ORIGINAL';

EXEC sys.sp_addextendedproperty
    @name=N'MS_Description', @value=N'Código ITE único para ITEs confirmadas; debe estar normalizado',
    @level0type=N'SCHEMA', @level0name=N'dbo',
    @level1type=N'TABLE', @level1name=N'IMPORTACION',
    @level2type=N'COLUMN', @level2name=N'CODIGO_ITE';
kalvaro posted 11 months ago

BTW, this app uses Windows-1252 everywhere (from source files to databases).

Please login to leave a reply, or register at first.