SQL Server: comments missing from some columns

kalvaro's profile image kalvaro posted 8 years ago in General Permalink

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.

Code modification/commit 3641a18 from ansgarbecker, 8 years ago, revision 5088
Rewind result set for column comments before iterating over it, as it's an inner loop. See http://www.heidisql.com/forum.php?t=21504
ansgar's profile image ansgar posted 8 years ago Permalink

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

kalvaro's profile image kalvaro posted 8 years ago Permalink

Thank you!

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

ansgar's profile image ansgar posted 8 years ago Permalink

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

kalvaro's profile image kalvaro posted 8 years ago Permalink

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's profile image kalvaro posted 8 years ago Permalink

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

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