>Can the following be a factor?
>
>When I create the field, I create it with the DEFAULT WITH VALUES syntax. But, when I need to enlarge its column, I am creating a temporary field without those clauses because I do an update to replace all the fields of the temporary fields with the original field values and renaming it after.
>
>
> IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyTable' AND COLUMN_NAME = 'FirstName')
> BEGIN
> SET @Command = N'ALTER TABLE [MyTable] ADD [FirstName] char(25) DEFAULT '''' WITH VALUES'
> EXEC sp_executesql @Command
> END
> ELSE
> BEGIN
> SET @Command = N'ALTER TABLE [MyTable] ADD [FirstName-Temporary] char(25)'
> EXEC sp_executesql @Command
> SET @Command = N'UPDATE [MyTable] SET [FirstName-Temporary]=FirstName'
> EXEC sp_executesql @Command
> SET @Command = N'ALTER TABLE [MyTable] DROP COLUMN [FirstName]'
> EXEC sp_executesql @Command
> EXEC sp_RENAME 'MyTable.FirstName-Temporary', 'FirstName' , 'COLUMN'
> END
>
>
>This never fails when the field does not exist. But, when it exists, I am using the alternative route in order to avoid a timeout. On some isolated situations, when the table is big, this is when I have a problem.
May be we need to add some wait in between each command. The else case looks OK to me except that I would not risk using - Temporary and rather use _Temporary as prefix. - requires using of [] and I'm not 100% sure about your last sp_rename command. So, just to be 100% safe I'd change - to _
Also, could it be a problem with case-sensitive collation of the Server? In that case all field names should match exactly how they are defined in the database.
If it's not broken, fix it until it is.
My Blog