>Hi everybody,
>
>I have application called DataDict. In this application I have table - DataDict, which contains definitions of all fields in tables from our databases. I compare existing structure with DataDict structure and if something was changed, I perform alter table command.
>
>I have a field called ModiDate - datetime. I also have an index ModiDate with expression ttod(ModiDate). ModiDate has a default value datetime().
>
>Now, I delete default value from my database. DataDict catches the differences and tries to do ALTER table. It gives an error 1531:
The table changes cannot be performed as the index or filter expression for index name would be invalid (Error 1531)
>You have attempted to use the ALTER TABLE command where the result would render an existing index or filter expression invalid, even if it not explicitly modified by the ALTER TABLE command.>
>Ok, I changed my algorithm using Trey's Walpole idea. Now I create a special program called ChangeTable with this code:
>
>********************************************************************
>* Description.......: ChangeTable - run alter command
>* Calling Samples...:
>* Parameter List....: tcTableName, tcCommandStr
>* Created by........: Nadya Nosonovsky 06/06/2001 10:16:59 AM
>* Modified by.......:
>********************************************************************
>lparameters tcTableName, tcCommandStr
>local lcOldError, llError, lcTag
>lcOldError=on('error')
>on error llError=.t.
>* invoke the ALTER TABLE – SQL Command with &-macro expanded tail
>alter table (m.tcTableName) &tcCommandStr novalidate
>do while m.llError and oValid.lContinue
> release laError
> =aerror(laError)
> if laError[1,1] = 1531
> set step on
> lcTag = laError[1,3]
> set message to 'Deleting tag '+m.lcTag
> delete tag (m.lcTag)
> llError = .f.
> alter table (m.tcTableName) &tcCommandStr novalidate
> else
> exit
> endif
>enddo
>if m.llError
>** Exited
> aerror(laError)
> =messagebox("Error: " + transform(laError[1,1])+chr(13)+laError[1,2], 48,"Alter table error")
>endif
>set message to ' '
>on error &lcOldError
>return m.llError
>
>I noticed, that delete tag (m.lcTag) now gives me an error, so I'm in a endless loop (I can terminate it by ESC, though). In table designer I can delete this tag and change modidate default value without problems.
>
>Do you ideas how to solve this mystery? I can make few iterations and exit my loop, but the table would not be updated, which I really want...
Ok, disregard :) Found the problem. In tcCommandStr I have ALTER COLUMN statement twice.
If it's not broken, fix it until it is.
My Blog