Unable to modify table. Timeout expired. The timeout period elapsed prior
-
I'm trying to add a column to a table that has over 5 million rows, using Micrisoft SQL Server Management Studio. I would write a query to alter the table and add the column but then the column would automatically by added to the end of the table. I need the column in the middle of the table. When trying to add the column manually using Mangement Studio I receive this error message:
- Unable to modify table. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Can someone please tell me how to add this column in the middle using a query or what settings to change on the server/database to add this column using Management Studio. Like I said, the table has over 5 milions of rows of data already. 🙁
-
Hello,
just create a change script and run it - then the timeout won't occure.
-
How do you write a change script to place a column in the middle of a table that already has data (over 5K of rows) and not the end of the table?
-
SMS can generate a change script for you if you like.. Basically the script will generate the DDL for your "new" table (with the new column) and then it will name it temp_tablename and then it will dump the data from your original table into it, then drop the existing table and rename the "temp" table to the old name
just go into modify table, add your column then right click and select "generate change scripts".. you can then choose to save the script to a text file so you can peruse it... Hope this helps!
Thanks
Rich
-
WOW!!! That did the trick. Thanks so much! I really appreciate it. 🙂
-
Middle of the table? Column order has no meaning in SQL, much like row order. If you wnat columns in a specific order, specify that in the select. The 'order' of columns in a table design has little meaning.
The reason the scripts generated by management studio take so long is because they create a new table, copy all the data over, copy the constraints then drop the old table. That's a hell of an impact to your tran log. Alter table add is much faster and much lighter on the log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Yes of course, but some DBAs care about e.g the alphabetical order or order their columns by column type...
-
I got a question regarding this situation, because on SQL2000 I can add a column in the middle of a table,why I can do it in SQL 2005?
-
Both 2000 and 2005 do it like Gail said... both make a copy of the table with the new column added, then it moves the data, drops the old table, and renames the new table.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Rich Script is not working in my Case it is taking too much in the DROP TABLE dbo.tableName
I have backup for the table before deleting main table
Thanks
Shyam
-
shyam-792781 (12/7/2010)
Hi Rich Script is not working in my Case it is taking too much in the DROP TABLE dbo.tableNameI have backup for the table before deleting main table
Thanks
Shyam
What do you mean by "Rich Script" because, I've got to tell you, dropping even a very large table takes virtually no time at all.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks.
My Problem have been resolved
Shyam
-
I believe Shyam's post should have read "Hi Rich, Script is not working in my..." An earlier poster named "Rich" gave a solution about running a script.
The comma, although small, made all the difference in the world.
Just like in the following example, I do not believe cannibalism was the goal:
"Let's eat grandma" vs "Let's eat, grandma"
😀
-
shyam-792781 (12/13/2010)
Thanks.My Problem have been resolved
Shyam
That's nice. Would you mind telling us how you resolved the problem please? Two way street here, ya know. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thomas Hilzendegen (11/28/2006)
Yes of course, but some DBAs care about e.g the alphabetical order or order their columns by column type... <img src='images/emotions/confused.gif' height='20' width='20' border='0' title='Confused' align='absmiddle'>What DBA ? and if so of what system. Oh you mean the anal DBA who start procedures with 'proc' and triggers with 'trg'. Give me a break.:w00t:
These types of Myths need debunking before some other poor 'accidental' developer or DBA is damaged.
sorry .... I have a chill pill ... taking a deep breath ...
Ahhh.... Just ask this particular what on earth an alphabetical list actually achieves and post it here please. This year has been tough and I think we can all do with a good giggle before Christmas.
Breathing easier now ... going to my dark room with teddy and have a little nappy nap nap..... ZZZZZZZZzzzzzzzzz
😛
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply