"Timeout Expired" on large table change

15 Mar 2006 8:05 PM

The One We Call 'Dave'

I have a table containing several hundred thousand rows. Once of the
fields in the table is named EODFeedDate. The field is nullable. I've
decided that I do NOT want that field to be nullable. I've unchecked the
"allow nulls" checkbox for the field in Management Studio. I then tried to
save my schema change. I'm getting the following error from Management
Studio:

"Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding."

    I can't figure out what's causing the problem. It's worth nothing that:

    - Of the current entries in this table, none of them have EODFeedDate
set to null so making the field non-nullable should cause a conflict.
    - No one else is connected to the database. It's a private development
machine -- so it isn't a contention problem.

     Any ideas?

David

15 Mar 2006 9:02 PM

The One We Call 'Dave'

> "Timeout expired. The timeout period elapsed prior to completion of the
> operation or the server is not responding."
>
> I can't figure out what's causing the problem.

I did a google search on this. You have to write a query to workaround this
bug. I've never written query code to modify a table before; I've always
used the UI, so I had some learning to do.

I learned about the ALTER TABLE statement but my execution of that statement
failed. The error message stated that the column could not be made
non-nullable because there is an index on the table that made use of the
column in question. That's when I learned about the DROP INDEX statement. By
using a DROP INDEX in tandem with an ALTER TABLE, I was able to make the
field non-nullable. I then re-created the index using the UI.

I've never spent so much time trying to do something so simple. :(

David

15 Mar 2006 9:10 PM

Michael D'Angelo

You can have Management Studio generate the script by making the change,
then right clicking the window and selecting "Generate Change Script"

Show quoteHide quote

"The One We Call 'Dave'" <ghe***@englewood.com> wrote in message
news:-MOdnXQku9doHYXZnZ2dnUVZ_t-dnZ2d@giganews.com...
>> "Timeout expired. The timeout period elapsed prior to completion of the
>> operation or the server is not responding."
>>
>> I can't figure out what's causing the problem.
>
> I did a google search on this. You have to write a query to workaround
> this bug. I've never written query code to modify a table before; I've
> always used the UI, so I had some learning to do.
>
> I learned about the ALTER TABLE statement but my execution of that
> statement failed. The error message stated that the column could not be
> made non-nullable because there is an index on the table that made use of
> the column in question. That's when I learned about the DROP INDEX
> statement. By using a DROP INDEX in tandem with an ALTER TABLE, I was able
> to make the field non-nullable. I then re-created the index using the UI.
>
> I've never spent so much time trying to do something so simple. :(
>
> David
>

15 Mar 2006 10:38 PM

The One We Call 'Dave'

"Michael D'Angelo" <nospamnmdange@phoenixworx.org> wrote in message
news:uIYUXTHSGHA.4456@TK2MSFTNGP14.phx.gbl...
> You can have Management Studio generate the script by making the change,
> then right clicking the window and selecting "Generate Change Script"

Doh! I didn't realize that!! Thanks for the tip. I'll be sure to make use of
that in the future.