none
sql 2005 updating table definition with 'large' amounts of data - timeout

    Question


  • I'm trying to move my current use of an sql 2000 db to sql 2005.

    I need to update a table definition (to change a field to an Identity)

    I'm getting a dialog box (in SQL server management studio) on save saying :

    'xxxx' table
    - 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 press 'Yes' to the dialog box.

    After 35 seconds, I get another dialog box saying:

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

    Well, the server is responding and I can query that talbe and other, I can add/delete rows to other columns.  I can modify other (smaller) tables.

    Any ideas where I can change this timeout?

    Daniel
    Saturday, February 04, 2006 6:07 AM

Answers

  • In Sql Server management studio (2005) go to Tools|Options, then from the tree control select Query Execution->SQL Server, Excution Timeout of 0 seconds indicates unlimited wait time.

     

    Can't remember if it's the same in 2000, and I don't have it installed on my machine anymore.

    Sunday, February 05, 2006 12:37 AM

All replies

  • In Sql Server management studio (2005) go to Tools|Options, then from the tree control select Query Execution->SQL Server, Excution Timeout of 0 seconds indicates unlimited wait time.

     

    Can't remember if it's the same in 2000, and I don't have it installed on my machine anymore.

    Sunday, February 05, 2006 12:37 AM
  • I'm getting the same message trying to create a View against a DB2 Linked Server table.  Another table with 100's of rows works fine.  This table with ~47000 rows gives the timeout error.

    I can query the table and return all the rows, but it takes about 20 seconds to get the first results back, which seems be be a clue.

    Tried all the timeout settings to no avail...

    I'll check next the oledb settings, but

    Thursday, February 16, 2006 10:28 PM
  • Hi Guys

    Have a look at changing the Designer timeout on Tool->Options menu.

    See here :http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=149821&SiteID=1

    Wednesday, February 22, 2006 12:08 PM
  • Also, if you write out a query to alter the column, you won't get this error and the column will be changed to an identity column.
    Monday, March 27, 2006 9:11 PM
  • Hi guys!

    I'm getting this message event with "Override connection string time-out value for table designer updates" check box cleared. Any other idea?
    Friday, June 08, 2007 7:46 PM
  •  Rui Covelo wrote:
    Hi guys!

    I'm getting this message event with "Override connection string time-out value for table designer updates" check box cleared. Any other idea?


    I set the timeout to 65535 seconds and this seems to work. It's stupid though...
    Friday, June 08, 2007 7:49 PM
  • Alter table statement worked like charm for me

     

     

    Wednesday, October 31, 2007 4:08 PM
  • In fact, I thing that it is the Designer timeout that should be set, not the query execution : http://blog.mike-obrien.net/PermaLink,guid,af40b045-1443-4631-bcfa-2b54ead32435.aspx

     

    It works for me.

    Friday, April 18, 2008 10:17 AM
  • This thing worked for me.Not stupid at all !

    Thanks

    Tuesday, October 18, 2011 2:03 PM