Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm trying to add a column to a table with much data in SQL Server 2005, using SSMS.

So I browse to the table, select Modify, and add the new column. Then, when I press Save, I get the following warning:

Saving Definition Changes to tables with large amounts of data could take a considerable amount of time. While changes are being saved, table data will not be accessible

I'm OK with that, the DB is offline and I have all the time in the world, so I press Yes.

However, the operation then proceeds to time out after about 30 seconds with this message:

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

Then, when I press OK:

User canceled out of save dialog (MS Visual Database Tools)

I don't get that. I have set the execution timeout to 0 (infinite) both in the SSMS connection dialog and under Tools -> Options -> Query Execution -> SQL Server. What is the point of setting an execution timeout if it's just ignored?

Does anyone know what timeout-value is being used here, and how I can set it?

share|improve this question
add comment

4 Answers

up vote 9 down vote accepted

Sounds like a timeout setting. So your SSMS thinks it takes too long and cancels the connection for you. The SQL server roles back. But there is help. You are not the first person to encounter this.

See here.

For everybody who doesn't want to click the link. Here is the price winning answer:

After hitting the same error, I stumbled upon the corrent setting.

In the Management Studio, from the Tools menu, select Options, then click "Designers". There is an option called "Override connection string time-out value for table designer updates:" In the "Transaction time-out after:" box, you will see the magic 30 seconds

share|improve this answer
    
That did it. Thanks. –  Tor Haugen Jan 5 '10 at 13:43
add comment

//DO THE FOLLOWING:

Open SQL Server management studio--> Tools Menu--> Options--> Designers Tab--> Table and Database Designer--> Increase the timeout from 30seconds to 65535 seconds.--> OK

//Now you can save your table changes. //Hope that helps, and check out my blog @: //www.ogolla.blogspot.com

share|improve this answer
add comment

SSMS shouldn't be used for general data modifications. Get yourself comfortable with T-SQL and add the column as suggested below.

ALTER TABLE TableName ADD NewColumn INT

SSMS is actually creating a new table with all the columns, importing data to the new table and dropping the old table. Even if you have all the time in the world, if there is an efficient way of doing things, you should use the right method. To avoid this mess, in SQL Server 2008 will not allow the GUI method IIRC.

share|improve this answer
2  
I see your point. But I prefer to have my columns in a specific order. Silly, you might think, but there you have it. Using ALTER TABLE, the new column is added to the end. I guess that's why SSMS must drop and recreate the table, in fact. –  Tor Haugen Jan 5 '10 at 13:45
add comment

In 2005 Managment Studio Options->Environment Options->Timeouts set 0

samasdelala.ru

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.