>This script is executed as is for several other maintenances I do on various databases. However, it failed three times since yesterday saying that it cannot drop the column:
>
>
> SET @Command = N'ALTER TABLE [MyTable] ADD [HomePhone-Temporary] numeric(15,0)'
> EXEC sp_executesql @Command
> SET @Command = N'UPDATE [MyTable] SET [HomePhone-Temporary]=HomePhone'
> EXEC sp_executesql @Command
> SET @Command = N'ALTER TABLE [MyTable] DROP COLUMN [HomePhone]'
> EXEC sp_executesql @Command
> EXEC sp_RENAME 'MyTable.HomePhone-Temporary', 'HomePhone' , 'COLUMN'
>
>
>The message is:
>
>"ALTER TABLE DROP COLUMN HomePhone failed because one or more objects access this column."
>
>Anyone would know what could cause this? I would also like to know how to prevent this.
>
>Basically, on big tables, if we change the length of a field, we cannot do it directly on the field as it would time out. So, we have to create a temporary field, move the data into that temporary field, removed the main field and rename the temporary filed to the main field.
>
>Unless there is a better way to do it, I would like to get some info on this on what I can do to make sure that this will always work even on big tables.
I have another idea. Try using HomePhone_Temp instead of -Temporary. I think using - in the name may be causing the problem.
Also, it may be a good idea to test if the execution of each command was successful by querying the INFORMATION_SCHEMA.COLUMNS view after each operation.
If it's not broken, fix it until it is.
My Blog