| Author |
Topic |
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
 Posted - 2005-11-23 : 03:08:27
|
Hi.. !!Is there any way i can alter text column to varchar column (Data in the column is less then 8000 chars)When i try out following scripts Alter <TableName>Alter TextColumn Varchar(8000)It gives me error that can not alter text columnif doing my enterprize manager then it drop the the table put all the data in the temp table and then recreating the table.. Is there any other way to do so ????? Thanks..Sucess Comes to those who Believe in Beauty of their Dream.. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-23 : 03:13:18
|
| No. You should do what the script from EM hasMadhivananFailing to plan is Planning to fail |
 |
|
|
surendrakalekar
Posting Yak Master
120 Posts |
Posted - 2005-11-23 : 03:30:52
|
quote:
Originally posted by chiragkhabaria
Is there any other way to do so ?????
Create new field as varchar(8000)Then update that varchar field with the text filed.Update YourTableName set VarcharField = ltrim(rtrim(cast(yourTextFieldName as varchar(8000))))Surendra |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2005-11-23 : 03:51:54
|
quote:
Originally posted by surendrakalekar
quote:
Originally posted by chiragkhabaria
Is there any other way to do so ?????
Create new field as varchar(8000)Then update that varchar field with the text filed.Update YourTableName set VarcharField = ltrim(rtrim(cast(yourTextFieldName as varchar(8000))))Surendra
Thanks for the reply .. but i just want to alter the column datatype.. if i create the new column then ordinal position of the column is lost.. and in the application it will create lots of problem since for the grid manupilation the ordinal position is very useful for us.. Any other solution..???Sucess Comes to those who Believe in Beauty of their Dream.. |
|
|
|
surendrakalekar
Posting Yak Master
120 Posts |
Posted - 2005-11-23 : 03:58:25
|
quote:
Originally posted by chiragkhabaria
quote:
Originally posted by surendrakalekar
quote:
Originally posted by chiragkhabaria
Is there any other way to do so ?????
Create new field as varchar(8000)Then update that varchar field with the text filed.Update YourTableName set VarcharField = ltrim(rtrim(cast(yourTextFieldName as varchar(8000))))Surendra
but i just want to alter the column datatype.. if i create the new column then ordinal position of the column is lost.. and in the application it will create lots of problem since for the grid manupilation the ordinal position is very useful for us..
You can't alter the field datatype from text to varchar(8000)If you are using column number to show in grid it's wrong way. Always use column name. I don't know but there must be some way to fetch the value in grid using columnname.Surendra |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-23 : 04:01:15
|
| >>.. if i create the new column then ordinal position of the column is lost.. Ordinal Position of the column doesnt matter. In your select statement choose the columns in the order that you want. How are you handling this in the presentation layer?MadhivananFailing to plan is Planning to fail |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2005-11-23 : 04:04:40
|
| Well.. about the grid.. which i am using is the stingray objective grid for VC++..And i guess there is no way to acess the grid by column name .. since every where they are using col index .... neways Thanks..Sucess Comes to those who Believe in Beauty of their Dream.. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-23 : 04:08:12
|
| Then you need to change the coding to get value by column name or the select queryMadhivananFailing to plan is Planning to fail |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2005-11-23 : 04:09:48
|
quote:
Originally posted by madhivanan
>>.. if i create the new column then ordinal position of the column is lost.. Ordinal Position of the column doesnt matter. In your select statement choose the columns in the order that you want. How are you handling this in the presentation layer?MadhivananFailing to plan is Planning to fail
Well at the presentation layer.. we are assigning the ordinal position of the col to the grid col index.. means #define MODULEGRIDCOL 1 #define MODULEGRIDCOL 2AND SO ON.. and they are accessed.. for some manupilation.. so the ordinal position is very important .. since its used througout the package in this way..Sucess Comes to those who Believe in Beauty of their Dream.. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-23 : 04:21:22
|
| Then you should change the select query to have the columns on that specified orderOtherwise use EM to create the column at the preferred ordinal positionMadhivananFailing to plan is Planning to fail |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2005-11-23 : 04:25:15
|
| Cant be update the ordinal position of the column .. using sysobjects .. I know it wont be safe.. but is there a way to do somthing like this..????was just wondering.. ???Sucess Comes to those who Believe in Beauty of their Dream.. |
|
|
|
sqlmember
Starting Member
7 Posts |
Posted - 2005-11-23 : 04:33:10
|
| You can change the ordinal position of the column by the following query(before running this query dislocate the target column on some other location with the same query otherwise you will get the PK violation error)update syscolumns set colid = 3 where id = object_id('table_name') and name = 'column_name'But make it sure the column is not PK and FK. Other solution is to make a local temp table consisting of PK and text column. Populate the table with the data. Drop column and recreate column at the same location in EM. And update the table from the temp table where table.PK = temp.PK-Khurram Iqbal |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2005-11-23 : 04:36:17
|
| aha.. let me check .. will get back u soon.. .Sucess Comes to those who Believe in Beauty of their Dream.. |
|
|
|
|
Altering Column DataType