Showing posts with label identitycolumn. Show all posts
Showing posts with label identitycolumn. Show all posts

Monday, March 12, 2012

Identity insert doubt

Hello,

How do I get SQL server 2000 to continue the sequence of identity
column values after I perform an insert into the table with the set
identity_insert <table name> on statement? Eg if I insert values into
the identity column with values 1-999, how do I get the next number to
be 1000? Can the identity seed value be changed if there are records in
the table? Can the identity seed value be re-set to start again from 1?
Thanks
IsaacIDENTITY will automatically take the next number after the highest value
inserted when you turn IDENTITY_INSERT off again. Use DBCC CHECKIDENT to
change the IDENTITY seed value at any time.

--
David Portas
SQL Server MVP
--|||DBCC CHECKIDENT(<TableName>, RESEED)|||Thank you David and Dishan. Using DBCC CHECKIDENT worked just fine.
Dishan Fernando wrote:
> DBCC CHECKIDENT(<TableName>, RESEED)

Wednesday, March 7, 2012

identity column problem skipped a range of values

Hi,
This is on SQL Server 2000 database. Inserts to a table with an identity
column on it have suddenly jumped in value. It was going along, 301,
302, ... then all of sudden identity column values jumped to 64221,
64222...
The app inserting data is an ASP.net app (1.1x). No changes were made to
the app. Does anyone have an idea what must be causing this. BTW, this
happened with only one person access the database.
jj"jj" <jj@.no_spam.net> wrote in message news:e7ua3f$5t9$1@.lists.fhcrc.org...
> Hi,
> This is on SQL Server 2000 database. Inserts to a table with an identity
> column on it have suddenly jumped in value. It was going along, 301,
> 302, ... then all of sudden identity column values jumped to 64221,
> 64222...
If you said it was skipping from say 302 to 306 or something reasonable, I'd
say there's some sort of rollback on transactions occurring.
(keep in mind that the IDENTITY column gets incremented regardless of the
success of a transaction.)
If it's happening with only one person, I'd set up profiler to see what
they're doing with the connection.
I mean I suppose it's possible they're starting and then rolling back 64000
transactions, but that sounds weird.

> The app inserting data is an ASP.net app (1.1x). No changes were made to
> the app. Does anyone have an idea what must be causing this. BTW, this
> happened with only one person access the database.
> jj|||"jj" <jj@.no_spam.net> wrote in message news:e7ua3f$5t9$1@.lists.fhcrc.org...
> Hi,
> This is on SQL Server 2000 database. Inserts to a table with an identity
> column on it have suddenly jumped in value. It was going along, 301,
> 302, ... then all of sudden identity column values jumped to 64221,
> 64222...
If you said it was skipping from say 302 to 306 or something reasonable, I'd
say there's some sort of rollback on transactions occurring.
(keep in mind that the IDENTITY column gets incremented regardless of the
success of a transaction.)
If it's happening with only one person, I'd set up profiler to see what
they're doing with the connection.
I mean I suppose it's possible they're starting and then rolling back 64000
transactions, but that sounds weird.

> The app inserting data is an ASP.net app (1.1x). No changes were made to
> the app. Does anyone have an idea what must be causing this. BTW, this
> happened with only one person access the database.
> jj

Friday, February 24, 2012

Identity column exists for a table - how to know programatically

Hi,
Is there any way, that I can determine whether a table has any identity
column, programmatically in SQL Server. In other words, is it stored some
where like syscolumns or sysconstraints or whatever, whether a table has
identity column and what column has the identity property set to on? I am
referring to SQL Server 2000.
Thanks in advance
oursptHi
SELECT IDENT_SEED(OBJECT_NAME(id)) AS seed,
IDENT_INCR(OBJECT_NAME(id)) AS incr,
OBJECT_NAME(id) AS tbl
FROM syscolumns
WHERE (status & 128) = 128
"ourspt" <ourspt@.discussions.microsoft.com> wrote in message
news:D9DFCDEE-B7D6-4E3C-A40F-D352C1DF4308@.microsoft.com...
> Hi,
> Is there any way, that I can determine whether a table has any identity
> column, programmatically in SQL Server. In other words, is it stored some
> where like syscolumns or sysconstraints or whatever, whether a table has
> identity column and what column has the identity property set to on? I am
> referring to SQL Server 2000.
> Thanks in advance
> ourspt|||Hi,
check for sp_help <table_name> in BOL
http://msdn.microsoft.com/library/d... />
p_304w.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"ourspt" wrote:

> Hi,
> Is there any way, that I can determine whether a table has any identity
> column, programmatically in SQL Server. In other words, is it stored some
> where like syscolumns or sysconstraints or whatever, whether a table has
> identity column and what column has the identity property set to on? I am
> referring to SQL Server 2000.
> Thanks in advance
> ourspt|||You can use the OBJECTPROPERTY() function for that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ourspt" <ourspt@.discussions.microsoft.com> wrote in message
news:D9DFCDEE-B7D6-4E3C-A40F-D352C1DF4308@.microsoft.com...
> Hi,
> Is there any way, that I can determine whether a table has any identity
> column, programmatically in SQL Server. In other words, is it stored some
> where like syscolumns or sysconstraints or whatever, whether a table has
> identity column and what column has the identity property set to on? I am
> referring to SQL Server 2000.
> Thanks in advance
> ourspt|||If what you're trying to do is determine which column it is, then You can
refer to it direstly in a Select Statement using the keyword IDENTITYCOL, as
in
Select IDENTITYCOL From TableName
If there is no IdentityColumn in the table this will, howver, throw an
error...
***Invalid column name 'identitycol'.***
"ourspt" wrote:

> Hi,
> Is there any way, that I can determine whether a table has any identity
> column, programmatically in SQL Server. In other words, is it stored some
> where like syscolumns or sysconstraints or whatever, whether a table has
> identity column and what column has the identity property set to on? I am
> referring to SQL Server 2000.
> Thanks in advance
> ourspt