changing column tyoe in live database[self answered]
-
fairly simple question with no clue as to the answer. I have a column in a tabe that was created as nchar(10). Now the powers that be want a little more information in that column (store identifiers) and I need to change the nchar(10) to nchar(50). Did that,saved it with no errors or warnings, but when new information is entered the database is truncating the data to (10) still for that column. How do I fix this? any ideas? It is the database that is doing it as the code that enters the information does not care how many characters are in the string its inserting. Its a SQL 2008 r2 database BTW. edit: I found the issue, it WAS in the code I forgot that my app had to set a scalar variable when doing the insert etc etc... And I never changed it from (10) there... duh.. leaving the post instead of deleting in case some other noob makes the same mistake as me... lol
Programming is a race between programmers trying to build bigger and better idiot proof programs, and the universe trying to build bigger and better idiots, so far... the universe is winning.
-
fairly simple question with no clue as to the answer. I have a column in a tabe that was created as nchar(10). Now the powers that be want a little more information in that column (store identifiers) and I need to change the nchar(10) to nchar(50). Did that,saved it with no errors or warnings, but when new information is entered the database is truncating the data to (10) still for that column. How do I fix this? any ideas? It is the database that is doing it as the code that enters the information does not care how many characters are in the string its inserting. Its a SQL 2008 r2 database BTW. edit: I found the issue, it WAS in the code I forgot that my app had to set a scalar variable when doing the insert etc etc... And I never changed it from (10) there... duh.. leaving the post instead of deleting in case some other noob makes the same mistake as me... lol
Programming is a race between programmers trying to build bigger and better idiot proof programs, and the universe trying to build bigger and better idiots, so far... the universe is winning.
Hi, Good that you found the solution. However, few other things in this post that made me wonder: - do you really need fixed length strings (NCHAR)? Using variable length strings (NVARHCAR) would most likely make it easier to handle the data and probably also save space since the string doesn't need to be padded with empty chars - the question sounded like you're going to concatenate different information pieces into a single column. If this really is the case you should avoid that solution since it's going to cause problems sooner or later (typically sooner) and require more complex logic (using substrings, fixed lengths, possibly delimiters etc etc). If the requirement is given, perhaps it would still be reasonable to talk it over one more time. Of course if you don't have the possibility to modify the application itself, it's a different situation but based on your post I understood that modifications are possible. Best regards.
The need to optimize rises from a bad design.My articles[^]
-
Hi, Good that you found the solution. However, few other things in this post that made me wonder: - do you really need fixed length strings (NCHAR)? Using variable length strings (NVARHCAR) would most likely make it easier to handle the data and probably also save space since the string doesn't need to be padded with empty chars - the question sounded like you're going to concatenate different information pieces into a single column. If this really is the case you should avoid that solution since it's going to cause problems sooner or later (typically sooner) and require more complex logic (using substrings, fixed lengths, possibly delimiters etc etc). If the requirement is given, perhaps it would still be reasonable to talk it over one more time. Of course if you don't have the possibility to modify the application itself, it's a different situation but based on your post I understood that modifications are possible. Best regards.
The need to optimize rises from a bad design.My articles[^]
well, I was using fixed length since this was my first app that I have built using asp or sql as this is my first job out of an associates degree. I originally set them because I am controlling the string length from the app itself. Its not really a concatenation of strings though. We have one table with store locations that were just a string of numbers such as 101. They now want it to show 101-City. This is linked to another table through the information entered in the app. So the string itself is not changed dynamically by any users. it is set by a separate page of the app that is only assessable to the admin. Thanks for the post and I will definitely keep that information in mind on future endeavors as I am still learning and have a long way to go. It will also clear up that pesky white space in my dang columns.. lol As you can probably guess I have not had much database training , just code(c++,Java etc..) I had to teach myself sql and asp so I'm sure it would give a professional a headache but it works and is improving with every day that passes. Thanks again for your assistance and I'm sure you will hear from me some more.
Programming is a race between programmers trying to build bigger and better idiot proof programs, and the universe trying to build bigger and better idiots, so far... the universe is winning.
-
fairly simple question with no clue as to the answer. I have a column in a tabe that was created as nchar(10). Now the powers that be want a little more information in that column (store identifiers) and I need to change the nchar(10) to nchar(50). Did that,saved it with no errors or warnings, but when new information is entered the database is truncating the data to (10) still for that column. How do I fix this? any ideas? It is the database that is doing it as the code that enters the information does not care how many characters are in the string its inserting. Its a SQL 2008 r2 database BTW. edit: I found the issue, it WAS in the code I forgot that my app had to set a scalar variable when doing the insert etc etc... And I never changed it from (10) there... duh.. leaving the post instead of deleting in case some other noob makes the same mistake as me... lol
Programming is a race between programmers trying to build bigger and better idiot proof programs, and the universe trying to build bigger and better idiots, so far... the universe is winning.
Glad you left the original post; it may, as you said, serve others in the future. We learn by doing, and hopefully, in learning we assist others. This site exists to assist others. Ask reasonable questions and you will probably receive reasonable advice. Before long, you may be the one answering rather than asking. Tim
-
Glad you left the original post; it may, as you said, serve others in the future. We learn by doing, and hopefully, in learning we assist others. This site exists to assist others. Ask reasonable questions and you will probably receive reasonable advice. Before long, you may be the one answering rather than asking. Tim
I look forward to the day I have enough knowledge to pitch in and help rather than just ask.
Programming is a race between programmers trying to build bigger and better idiot proof programs, and the universe trying to build bigger and better idiots, so far... the universe is winning.
-
well, I was using fixed length since this was my first app that I have built using asp or sql as this is my first job out of an associates degree. I originally set them because I am controlling the string length from the app itself. Its not really a concatenation of strings though. We have one table with store locations that were just a string of numbers such as 101. They now want it to show 101-City. This is linked to another table through the information entered in the app. So the string itself is not changed dynamically by any users. it is set by a separate page of the app that is only assessable to the admin. Thanks for the post and I will definitely keep that information in mind on future endeavors as I am still learning and have a long way to go. It will also clear up that pesky white space in my dang columns.. lol As you can probably guess I have not had much database training , just code(c++,Java etc..) I had to teach myself sql and asp so I'm sure it would give a professional a headache but it works and is improving with every day that passes. Thanks again for your assistance and I'm sure you will hear from me some more.
Programming is a race between programmers trying to build bigger and better idiot proof programs, and the universe trying to build bigger and better idiots, so far... the universe is winning.
Yes, I understand. Just for the future use, if the code is the key to the city, you should have a lookup table for cities and fetch the name using a join. For example if the city name changes you'll now have to update part of the string field to correct the situation on every row. At some point when you have time, have a look at W3Schools web site. There's lots of basic theory in clearly explained format. Even though it's mainly MySql, the theory is much the same. For example this could be interesting: http://w3schools.in/php-mysql.php?tutorial=Database-Normalization[^] Good luck to the project :)
The need to optimize rises from a bad design.My articles[^]