Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Identity field...

2 posts in ,  AdministrationWindows NTUpgrades and Configuration Performance and Tuning Last posting was on 1998-05-08 14:12:39.0Z
Rob Ratterman Posted on 1998-05-08 06:33:21.0Z
From: "Rob Ratterman" <jump@goodnews.net>
Subject: Identity field...
Date: Fri, 8 May 1998 02:33:21 -0400
Lines: 18
Organization: Jump Inc.
X-Newsreader: Microsoft Outlook Express 4.72.2106.4
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.2106.4
Message-ID: <iuPaKGke9GA.196@forums.powersoft.com>
Newsgroups: sybase.public.sqlserver.administration,sybase.public.sqlserver.nt,sybase.public.sqlserver.performance+tuning,sybase.public.sqlserver.upgrades+configuration
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.administration:3418 sybase.public.sqlserver.nt:569 sybase.public.sqlserver.performance+tuning:285 sybase.public.sqlserver.upgrades+configuration:220
Article PK: 1065578

Hello!

We have a table with an identity column. It was created as a "numeric (6)"

We are approaching the "max" value for this field (999999). I know that
when the server tries to insert 1000000 the INSERT will be rejected. Is
there any way to alter the field to allow a smooth transition to a "numeric
(7)" field?

If not, what options do we have?

Thanks a lot!


Rob


Bret Halford Posted on 1998-05-08 14:12:39.0Z
Message-ID: <355312D7.2604@sybase.com>
Date: Fri, 08 May 1998 08:12:39 -0600
From: Bret Halford <bret@sybase.com>
Organization: Customer Service & Support
X-Mailer: Mozilla 3.0 (X11; U; SunOS 5.5.1 sun4m)
MIME-Version: 1.0
To: Rob Ratterman <jump@goodnews.net>
Subject: Re: Identity field...
References: <iuPaKGke9GA.196@forums.powersoft.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.administration,sybase.public.sqlserver.nt,sybase.public.sqlserver.performance+tuning,sybase.public.sqlserver.upgrades+configuration
Lines: 27
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.administration:3417 sybase.public.sqlserver.nt:568 sybase.public.sqlserver.performance+tuning:284 sybase.public.sqlserver.upgrades+configuration:219
Article PK: 1065579


Rob Ratterman wrote:
>
> Hello!
>
> We have a table with an identity column. It was created as a "numeric (6)"
>
> We are approaching the "max" value for this field (999999). I know that
> when the server tries to insert 1000000 the INSERT will be rejected. Is
> there any way to alter the field to allow a smooth transition to a "numeric
> (7)" field?
>

The precision cannot be directly modified.

The usual solutions are to create a new table with an identity field
of the desired precision and either bcp the first table out and
back into the new table (using the bcp option that allows you to
bcp in identity values) or insert newtable select * from oldtable (with
the "set identity_insert on" option). The original table can then be
dropped and the new table renamed with sp_rename (and triggers, indexes,
constraints, etc rebuilt).

--
Bret Halford Imagine my disappointment
Sybase Technical Support in learning the true nature
3665 Discovery Drive of rec.humor.oracle...
Boulder, CO 80303