Recently, one of our clients reported a puzzling problem when running the DatabaseManager UpdateSchema command. An obscure SqlException stared back at us from the Windows Event Viewer: "Alter table failed because unique column IDs have been exhausted for table 'tblRECORDINDEXEDFIELDS'.".

Intrigued as ever, we started digging and after a few minutes we discovered that SQL Server assigns a unique column ID to each and every column in a table. This column ID is a number between 0 and 32,000 (inclusive) on SQL Server 2005 and between 0 and 500,000 (inclusive) on SQL Server 2008. However, when removing a column from the table, it's unique ID is not released back to the pool, which means that, it is possible to reach the limit (particularly on SQL Server 2005) by running a lot of ALTER TABLE ADD COLUMN and ALTER TABLE DROP COLUMN statements.

So why did this happen?

Well, the UpdateSchema command from the Adam.Core.DatabaseManager commandline will add and remove columns in the ADAM database. When you have lots of indexed fields, and modify their definitions often to change their indexed flag (requiring you to run the UpdateSchema command), you may hit this limit.

Additionally, if you have scheduled the UpdateSchema command to be executed automatically, especially when specifying the -force parameter, you will hit this limitation sooner than you'd expect.

Conclusion?

Do not schedule the UpdateSchema command. We will include a fix in a future version of ADAM that will not be affected by this limitation.

If you already have the problem, please do not hesitate to launch a support ticket so we can help you resolve the issue.