Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

MS SQL Add Column Error

igitur's profile image igitur posted 5 years ago in General Permalink
When I add a column, e.g. PersistedGeneratedYieldCurves column to the Calculation table, and I press Save, HeidiSQL executes:

ALTER TABLE "dbo"."Calculation"
ALTER COLUMN  "PersistedGeneratedYieldCurves" BIT NOT NULL;
EXECUTE sp_addextendedproperty 'MS_Description', '', 'user', 'root', 'table', 'Calculation', 'column', 'PersistedGeneratedYieldCurves';


The first statement actually succeeds, but the second statement fails:
/* SQL Error (15135): Object is invalid. Extended properties are not permitted on 'root.Calculation.PersistedGeneratedYieldCurves', or the object does not exist. */


I'm not sure whether that second statement is necessary. I never use extended properties, but if it's really necessary, shouldn't it be something like this:

EXECUTE sp_addextendedproperty 'MS_Description', '', 'Schema', 'dbo', 'table', 'Calculation', 'column', 'PersistedGeneratedYieldCurves';

igitur's profile image igitur posted 5 years ago Permalink
Hi ansgar, have you had a chance to look at this?
miquel's profile image miquel posted 4 years ago Permalink
i have similiar error:when i try to add a comment in ACCION.ACCESO
where accion is table name and acceso is field name

ALTER TABLE "dbo"."ACCESOS"
ALTER COLUMN "ACCESO" VARCHAR(15) NOT NULL;
EXECUTE sp_addextendedproperty 'MS_Description', 'asasas', 'user', 'miquel', 'table', 'ACCESOS', 'column', 'ACCESO';
/* Error de SQL (15135): Objeto no vĂ¡lido. 'miquel.ACCESOS.ACCESO' no admite propiedades extendidas o el objeto no existe. */

i need to add comments in all DB and i don't know how to do it....
Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
igitur's profile image igitur posted 4 years ago Permalink
Ansgar, I beg you, please have a look at this. I run into this problem at least twice a day.
Code modification/commit from ansgarbecker, 4 years ago, revision 4998
MSSQL: Fix broken query for altering column comment via table editor. See
* here: http://www.heidisql.com/forum.php?t=16947
* and here: http://www.heidisql.com/forum.php?t=18119
ansgar's profile image ansgar posted 4 years ago Permalink
r4998 now uses that "'schema', 'dbo'" syntax instead of "'user', 'root'".

Hope that doesn't break something else, but I couldn't find the place where I found that sp_addextendedproperty query before. This is the only way I found how to change a column's comment in MSSQL. Is there a more "normal" way than that?
igitur's profile image igitur posted 4 years ago Permalink
r4998 work for me. I'll look into finding a more 'normal' way. Thanks very much!
igitur's profile image igitur posted 4 years ago Permalink
Isn't it simpler to execute sp_addextendedproperty statement only when the comment field has been populated. For most cases when I add a column, I leave the comment field blank, so there is no need to execute the statement.

For editing an existing field, the statement should be executed only if the comment field has changed.

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