Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

update to [syscolumns].xtype Expand / Collapse
Author
Message
Posted Wednesday, December 30, 2009 3:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 12:43 AM
Points: 111, Visits: 473
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;
Post #840594
Posted Wednesday, December 30, 2009 3:38 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:26 AM
Points: 4,358, Visits: 9,536
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
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #840611
Posted Thursday, December 31, 2009 12:57 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 39,879, Visits: 36,227
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #840704
Posted Thursday, December 31, 2009 2:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 16, 2011 6:57 AM
Points: 126, Visits: 102
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.
Post #840731
Posted Thursday, December 31, 2009 6:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 12:43 AM
Points: 111, Visits: 473
thanks
Post #840786
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse