Monday, March 19, 2012

Bug with SQL Server 2005 Management studio

Hi,

I'm having a really annoying problem with SQL Server 2005 Management studio. I've just imported a very large database with 1,175,966 records in org_details table. During the import primary didn't transfer, so I thought I'd use management studio to alter the table and reset a primary key. however I get the following error. I want to restart the identity at 2213364 but I get the following error, also if its only 1 by 1. I've read it can be related to log file however I've increase the size to 8 GB.

ERROR:

'org_details' table
- Unable to create index 'PK_org_details'.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

This is really frustrating as I don't remember having issue like this with SQL Server 2000.

Thanks help or assistance.

Unfortunately, the error message doesn't let you know which timeout setting expired.

My guess is the table designer timeout. In Management Studio, go to

Tools|Options|Designers|Table and Database Designers

Uncheck "Override connection string time-out value for table designer updates." (The default is checked, with an override value of 30 seconds. That could be too short for you.)

Two other timeout settings that might be issues are mentioned in the answer to this similar question:

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

Steve Kass

Drew University

http://www.stevekass.com

|||

The root cause of this problem is you have choosen Import/Export wizard to transfer data which will only transfer data not the exact schema like PK,FK,Check Constraints etc. So when you want exact schema in the target database , script the source database and run the script in target db and then use IMport/Export to transfer data to the newly created tables. I would suggest you to use Script to create PK when the table is huge.

Madhu

|||

Thank you, you make a very valid point. The problem is related to a client of mine who has an access database, yes I said access with over 1 million records. In my opinion SQL management studio should support the manipulation of large databases.Yes it’s a large database however database’s these-days are large, lets be realistic a 1 million plus database isn’t uncommon.

I'm looking for a solution to SQL Server 2005 management studio problem not a scripting alternative, although thank you anyway.

|||thankyou I changed the timeout to 240 seconds now the transaction works.

No comments:

Post a Comment