SQL server over flow error

[manutektips]

HI,
I have a table where the PK of the table is defined as int datatype. It has now reached a stage where the PK value has reached 2147483647 and it is giving overflow error. Can you please suggest a way how to over come this. How to modify the datatype of this field so that it can accomodate more data.

 

[JohnDTampaBay]

Can you just change the datatype to bigint?

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[Catadmin]

Before you follow my suggestion, make sure you do a complete backup of your database!!!!

Go into Enterprise Manager, open up to the database & table in question. Right click the table and go to Design. This will bring up a window that will list all your columns & their datatypes.

Change datatype to BigInt. The values for bigint are Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Storage size is 8 bytes.

NOTE: There can be conversion issues with BigInt. Check it out in Books Online for more details.

Alternatively, if you want to script the procedure, you'll need to use the Alter Table statement to change the column datatype. Look up "Alter Table" in BOL for more details.

Don't do ANY changes until you've done a backup, though. Just in case something freaky happens. I do *not* recommend changing the PK to something with a decimal (numeric, float or double) in it.

Hope this helps.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???