December 30, 2009 at 3:01 pm
I need to update [syscolumns].xtype; pls let me know the steps in 2005. I changed INT to BIGINT for a column in a table. Some existing apps fail because of the change;
December 30, 2009 at 3:38 pm
Sorry - but you can no longer make changes to system tables like that. How did you make this change and why is it causing problems?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 31, 2009 at 12:57 am
You should not even be considering updating the system tables unless you are willing to deal with a corrupt and unusable database afterwards. Say you did update the xtype, now the system tables shows it's an int (4 bytes), but the data pages themselves still have 8 bytes (bigint) allocated to the columns. That'd cause one hell of a mess and likely some nasty messages.
If you want to convert the column from bigint to int, you do it the same way as the conversion from int to bigint.
ALTER TABLE <Table Name>
ALTER COLUMN <Column Name> <new data type> <nullability>
As an example
ALTER TABLE MyTable
ALTER COLUMN SomeColumn INT NULL
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 31, 2009 at 2:46 am
What the guys said above is correct.
You said that the existing applications fail, that's probably because somewhere you have a stored procedure or SQL in the application which reads the data from the database and expects it to be of a format Integer. So once you change the table definition in SQL you'll have to make the same changes in the front end.
December 31, 2009 at 6:02 am
thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply