Change Table design - TIMEOUT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Change Table design - TIMEOUT

Author

Topic

smithygreg
Starting Member

37 Posts

Posted - 2007-05-21 : 18:55:38

Hi...
I need to alter a table and change the length of one of my fields. I have a couple million rows in the database already and I am getting timeouts when I try to save it after changing the length of the field.
Is there any way to set the database timeout differently for a session in Management Studio.

I have set the Remote Query Timeout property to 0(Unlimited), but my session in SQL Mgmt Studio still gives me the timeout.

If there is no solution to this, can anyone think of something better than this....

SELECT * INTO temp from mytable
DELETE FROM mytable
(Modify Design of mytable)
INSERT INTO mytable SELECT * FROM temp

?

Well, thanks in advance for any help!
-Greg

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-21 : 23:35:42

Did you change that in gui or with 'alter table' statement?

smithygreg
Starting Member

37 Posts

Posted - 2007-05-22 : 11:03:34

I changed it in the GUI.
I am now assuming that if I do it with an ALTER TABLE it won't give me the timeout?
-Greg

Kristen
Test

22859 Posts

Posted - 2007-05-22 : 11:50:17

The SELECT INTO TEMP, Delete rows from MyTable, Insert into MyTable from TEMP, seems like an awful lot of data movement just to change the design ...

Kristen

smithygreg
Starting Member

37 Posts

Posted - 2007-05-22 : 11:53:21

Yeah...
I am going to try
ALTER TABLE
MODIFY columnname columnspec()
Next time I have to do this...
Which is unfortunately fairly often.
Thanks for the help!
-Greg

Kristen
Test

22859 Posts

Posted - 2007-05-22 : 12:14:51

If you come across circumstances where this isn't permitted then the script that SQL Server generates for the change is usually something like:

Drop any FK constraints

Create new TEMP table (with modified structure)

Insert into TEMP table SELECT (Col1, Col2, ...) FROM RealTable

Drop RealTable

Rename TEMP to RealTable

Re-create FK constraints

(which involves quite a lot less data-pushing-around, and the delete-data step is replaced with a drop table!)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources