| Author |
"Timeout Expired" on large table change
|
|
| The One We Call 'Dave' 2006-03-15, 8:23 pm |
| 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
| |
| The One We Call 'Dave' 2006-03-15, 8:23 pm |
| > "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
| |
| Michael D'Angelo 2006-03-15, 8:23 pm |
| You can have Management Studio generate the script by making the change,
then right clicking the window and selecting "Generate Change Script"
"The One We Call 'Dave'" <ghetto@englewood.com> wrote in message
news:- MOdnXQku9doHYXZnZ2dn
UVZ_t-dnZ2d@giganews.com...
>
> 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
>
| |
| The One We Call 'Dave' 2006-03-15, 8:23 pm |
|
"Michael D'Angelo" < nospamnmdange@phoeni
xworx.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.
|
|
|
|