Yesterday, I encountered the MS SQL 2005 error. I transferred the SQL 2005 Express edition database from server to my PC. The database is having 75000 records.
I have a J2EE application, which communicates with MS SQL 2005 database. For testing purpose, I tried to insert some data into database using JSP/HTML form of my J2EE application.
During testing, I got an error by J2EE application indicating that the ID field of database is not accepting the "null". As the application is running file on server, I guessed that something wrong with my PC’s database, which I have just ported.
I open the table design in MS SQL server management Studio. I checked the database of my PC. As ID column of my database's Main table was remained unchecked for "Allow null" it was imperative to give some data to this column. I noticed that the ID column was also not set as "auto- increment" (old terminology) that’s why this problem. So I decided to set it right.
I open the table design in MS SQL server management Studio. I selected the Main table of my database and choose "Modify". In Column Properties of ID, I selected Identity Specification. I changed the value of "IS Identity "from "No" to "yes". I kept identity increment "1".That was sufficient for me. I tried to save these setting in the anticipation so that it will serve the purpose for my J2EE application testing. When I tried to save the changes in Main table structure, I greeted 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.
--------------------------------------------------------------------------------
Surprised! i have not encountered this error so far. I tried again, 4-5 times and every time I got the same error. I conclude that somewhere there is a time out setting.
I started exploring various option of MS SQL Management Studio Express options. Under tool menu I discovered one option named as "Table and Database designers" in "Designer" option. In this ,there is a option called "Transaction time-out after:".
Eureka!! That’s what I wanted. I noticed that the initial value was 30 sec. without wasting a time; I put on leading zero and made it 300 sec. I tried again the table saving and this time Voila!! It worked. It saved my settings.
Useful information.
Whenever you try to make changes in large table , the MSSQL first create the temporary table and create the schema of table , which includes user's changes. Later it copies all the data from source table to this temporary table. Once MS SQL creates temporary table successfully with data and user's changes, MS SQL drops original table and rename the temporary table to original one's name.
This process is not only takes time but also takes disk space too. Hence if you are trying to make changes in large database table, make sure.
1, There is enough free disk space, ideally 20-25% more than db size
2. Donot forget to change time-out setting in MS SQL Server Management Studio Express. Keep it double it till it permit saving the changes. Once your job is done, set it back to 30 sec as it is good for most of the operations.
Happy Database Porting
Post your Views
No comments:
Post a Comment