error while adding new column to a table

[jbombardo]

Hi all,

I've created a table that contains a dynamic number of columns. This columns are used as a flag by different DTS, i.e. if the column exists the DTS is executed otherwise waits the next trial. This columns are added and dropped by other applications.

I'm using SQL Server 7.0 and it reports me a message saying that i've arrived to the limit of 1024 columns even though the table has only 15 columns.

The specific error is: "- Table can not be modified.
Error ODBC: [Microsoft][ODBC SQL Server Driver][SQL Server]
CREATE TABLE failed because column 'x' in
table 'CTRL_FLAGS' exceeds the maximum of 1024 columns."

Is it possible to create/drop as many times as you want a column in a table? Or there is a maximum?Any ideas?

Thanks on advance.

 

[nigelrivett]

When you drop a column (or alter it) syscolumns is updated and the data pages potentialy changed but the space used by the previous columns is not reused. This means that you can vio;ate bothh the number of columns and the max rowsize while having a table that apparently fulfills both requirements.

It is a bad idea to change table database structure while on-line - it is better to accompany any change with a backup and system restart.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

I've run some tests on this and it seems that you can drop and add columns until your hearts content. You may have problems with the 8060 byte limit as any fixe length cols may take up additional space and not reuse that vacated.
I can't get it to fail due to the number of cols though - this is for v2000, it may be different for v7.
I'll post the tests so you can try it yourself.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

The tests I ran are here

http://www.nigelrivett.net/AlterTableProblems.html

and this is how to see the data pages

http://www.nigelrivett.net/PageStructure.html

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.