automatically expand identity specification node?

subject says it all -- is it possible to automatically expand the identity specification node in table properties for the Management Studio or VS2005 diagram mode? when creating a DB, it seems ridiculous that for EVERY table I have to add an extra click to get to just one more clickable item that ought to be exposed by default.Yeah, that's kind of a pain. If you have a lot of design to do at once, it's easier to use T-SQL than the GUI tools. I don't know of a method to auto-expand that.|||

thx for the reply ... even if it's a year later

sure, T-SQL would be easier for the identity aspect ... but when I'm modelling a DB, it's too organic of a process to do in script. One of SQL Server's strengths has always been the DB modeller. I don't need to create an ERD and work off of that because the SQL Server diagram IS my initial ERD. Implementing ideas visually at the conceptual stage of DB design is critical IMO. Making the identiy attribute more accessible, whether by default, by choice, or by rearranging that portion of the GUI, would be a big value add, again, IMO ...

|||Have you gone to the Microsoft site to make that suggestion? They have a place where they rank them.|||

Buck Woody - MSFT wrote:

Have you gone to the Microsoft site to make that suggestion? They have a place where they rank them.

Where at? Connections?

|||That's right - here is the link:

http://connect.microsoft.com/SQLServer

|||thanks for the link, hadn't used Connect in a while.

automatically expand identity specification node?

subject says it all -- is it possible to automatically expand the identity specification node in table properties for the Management Studio or VS2005 diagram mode? when creating a DB, it seems ridiculous that for EVERY table I have to add an extra click to get to just one more clickable item that ought to be exposed by default.Yeah, that's kind of a pain. If you have a lot of design to do at once, it's easier to use T-SQL than the GUI tools. I don't know of a method to auto-expand that.|||

thx for the reply ... even if it's a year later

sure, T-SQL would be easier for the identity aspect ... but when I'm modelling a DB, it's too organic of a process to do in script. One of SQL Server's strengths has always been the DB modeller. I don't need to create an ERD and work off of that because the SQL Server diagram IS my initial ERD. Implementing ideas visually at the conceptual stage of DB design is critical IMO. Making the identiy attribute more accessible, whether by default, by choice, or by rearranging that portion of the GUI, would be a big value add, again, IMO ...

|||Have you gone to the Microsoft site to make that suggestion? They have a place where they rank them.|||

Buck Woody - MSFT wrote:

Have you gone to the Microsoft site to make that suggestion? They have a place where they rank them.

Where at? Connections?

|||That's right - here is the link:

http://connect.microsoft.com/SQLServer

|||thanks for the link, hadn't used Connect in a while.sql

automatically expand identity specification node?

subject says it all -- is it possible to automatically expand the identity specification node in table properties for the Management Studio or VS2005 diagram mode? when creating a DB, it seems ridiculous that for EVERY table I have to add an extra click to get to just one more clickable item that ought to be exposed by default.Yeah, that's kind of a pain. If you have a lot of design to do at once, it's easier to use T-SQL than the GUI tools. I don't know of a method to auto-expand that.|||

thx for the reply ... even if it's a year later

sure, T-SQL would be easier for the identity aspect ... but when I'm modelling a DB, it's too organic of a process to do in script. One of SQL Server's strengths has always been the DB modeller. I don't need to create an ERD and work off of that because the SQL Server diagram IS my initial ERD. Implementing ideas visually at the conceptual stage of DB design is critical IMO. Making the identiy attribute more accessible, whether by default, by choice, or by rearranging that portion of the GUI, would be a big value add, again, IMO ...

|||Have you gone to the Microsoft site to make that suggestion? They have a place where they rank them.|||

Buck Woody - MSFT wrote:

Have you gone to the Microsoft site to make that suggestion? They have a place where they rank them.

Where at? Connections?

|||That's right - here is the link:

http://connect.microsoft.com/SQLServer

|||thanks for the link, hadn't used Connect in a while.

Automatically Defining Primary keys

Hi,

I am new to SQL Server platform, i want to define primary keys automatically other than using an identity. For a table called indicator i want it primary keys to be like ind_001, ind_002, ind_003 and so on.

Can anybody help me on how to do this? I am new to this platform so i will appreciate it if suggestions are explained very clearly. Thank you

You will either have implement this in your fronent logic or use triggers to reset the values inserted to the appropiate pattern.

Jens K. Suessmeyer

http://www.sqlserver2005.de

automatic transaction - Stored procedure - @@identity

Hi, i am having a hard time understanding the concept maybe .

i am using automatic transaction as such:

[AutoComplete]

public void SaveInvoiceAndCharges()

{

//try

//{

DSPinvoiceandcharges.Tainvoice.Update(DSinvoiceAndcharges.Invoice);

// DSPinvoiceandcharges.Tainvoicecharge.Update(DSinvoiceAndcharges.InvoiceCharge);

//}

//catch (Exception e)

//{

// throw e;

//}

}

and my sp :

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[Invoice_New]

(

@.DateIssued datetime,

@.Reference nvarchar(50),

@.Note nvarchar(50),

@.DocumentType int,

@.CompanyCode int,

@.Valid nchar(1),

@.Fees int,

@.isDistributed nchar(1)

)

AS

SET NOCOUNT OFF;

Begin try

INSERT INTO [Invoice].[Invoice] ([DateIssued], [Reference], [Note],

[DocumentType], [CompanyCode], [Valid], [Fees], [isDistributed])

VALUES (@.DateIssued, @.Reference, @.Note, @.DocumentType, @.CompanyCode,

@.Valid, @.Fees, @.isDistributed);

return @.@.identity

End try

begin catch

raiserror('Test - Error', 16, 1 )

End catch

.. the code being run is not actually throwing any error.

DSPinvoiceandcharges.Tainvoice.Update(DSinvoiceAndcharges.Invoice);

updates but actually just does nothing and i lose the invalid row.

what is happening here please?

thank you ,

hrub

hrubesh:

I am not sure what else is going on but most likely you should be returning SCOPE_IDENTITY() instead of @.@.identity. Also, what are you getting for a return code?

|||

Hi, thanks for your reply,

i did not understand what u mean by what am i getting for a return code.

|||

Hrubesh:

You have this line in your code:

Code Snippet

return @.@.identity

How are you invoking the stored procedure? And after the execution of the stored procedure completes, what is the value returned by the stored procedure? Or are you just ignoring the return value? Finally, what do you mean by "it just does nothing." And if you don't know what the return value is, how do you know that "it is just doing nothing."

|||

hi,

i have been searching on the issue, and this is what i believe now it is not an issue with the sql part of it.

exception unhandled by user code in serviced components is what i am looking into now.

thanks for your help,

btw why the difference between @.@.identity and SCOPE_IDENTITY() ..

i have been using @.@.identity so that it automatically updates my parent row and child row with the new id , i am using

DSPinvoiceandcharges.Tainvoice.Update(DSinvoiceAndcharges.Invoice);

DSPinvoiceandcharges.Tainvoicecharge.Update(DSinvoiceAndcharges.InvoiceCharge);

invoice is the parent table and invoicecharge the child, and it works.

i will try the scope_identity asa i get the exception unhandled workaround. ..

yep so how i am invoking the sp is that Tainvoice is the sql data adapter that i generated using the wizard in my dataset, that created stored procedures on an sql command, and it automatically calls the corresponding insert/upd/del sp.

thanks a lot.

|||

@.@.identity will frequently work but it is not technically the correct choice. I gave an example of how @.@.identity can go wrong in this thread last year:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=880725&SiteID=1

Another thread in which I discussed with my friend Craig was here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=793185&SiteID=1

|||

thanks kent.

Automatic Identity Range Management

Hello,
I've got a question dealing with automatic identity range management.
Currently we have a database setup for publication in which the subscribers
are running SQL Server CE on mobile devices. We've got automatic identity
range management turned on for a few of our tables and we're having some
issues with the publisher identity ranges not getting automatically
reassigned after they are exhausted. I found this statement in the SQL Server
manuals:
"If the Publisher exhausts its identity range after an insert, it can
automatically assign a new range if the insert was performed by a member of
the db_owner fixed database role."
The user that is inserting the rows into the publisher database is a member
of the db_owner fixed database role but when a row is inserted which exhausts
the currenty identity range they are not automatically reassigned. I can
manually adjust the identity ranges using sp_adjustpublisheridentityrange
logged in with the same user, but they are not automatically adjusted after
the insert as the documentation suggests.
What else should we check for?
Thanks in advance,
mike...
You have to size the ranges for the max amount of inserts that would occur
between syncs. Otherwise the range will not be adjusted.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mike..." <Mike@.discussions.microsoft.com> wrote in message
news:5D34048F-05C2-4502-B295-C10AB89AF4BE@.microsoft.com...
> Hello,
> I've got a question dealing with automatic identity range management.
> Currently we have a database setup for publication in which the
> subscribers
> are running SQL Server CE on mobile devices. We've got automatic identity
> range management turned on for a few of our tables and we're having some
> issues with the publisher identity ranges not getting automatically
> reassigned after they are exhausted. I found this statement in the SQL
> Server
> manuals:
> "If the Publisher exhausts its identity range after an insert, it can
> automatically assign a new range if the insert was performed by a member
> of
> the db_owner fixed database role."
> The user that is inserting the rows into the publisher database is a
> member
> of the db_owner fixed database role but when a row is inserted which
> exhausts
> the currenty identity range they are not automatically reassigned. I can
> manually adjust the identity ranges using sp_adjustpublisheridentityrange
> logged in with the same user, but they are not automatically adjusted
> after
> the insert as the documentation suggests.
> What else should we check for?
> Thanks in advance,
> mike...
>
|||Hilary, Thanks for the reply - the problem we've run into is that the user
accounts (Windows Auth) sync'ing the devices are not members of the db_owner
role so the merge agents do not refresh the identity ranges (which is another
issues we've been attempting to work around). From our experiences (and from
reading the SQL Server manuals) the publisher identity ranges should refresh
themselves if the inserts are done at the publisher by users in the db_owner
fixed database role. This isn't occuring at one of our sites and I'm
wondering what other security arrangements need to be made for this to occur.
Any ideas?
Thanks again,
mike...
"Hilary Cotter" wrote:

> You have to size the ranges for the max amount of inserts that would occur
> between syncs. Otherwise the range will not be adjusted.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Mike..." <Mike@.discussions.microsoft.com> wrote in message
> news:5D34048F-05C2-4502-B295-C10AB89AF4BE@.microsoft.com...
>
>

Automatic Identity Range Handling warning message.

Ive implemented merge replication with SQL Server and an Access Database.
SQL Server is the publisher and Access database is the subscriber.
I have ONE table in the schema that can be updated in both the subscriber
side as well as the publisher side. I have an IDENTITY column in this table
and I have set the property "Automatic Identity Range Handling" ON for this
table.
Ive also created a script to create this publisher. Now, heres my problem.
When I create the publisher, I get a warning message thats stated below:
"Warning: only Subscribers running SQL Server 2000 can synchronize with
publication '<database>' because automatic identity ranges are being used."
OK, Ive done some resonable testing on this statement and it SEEMS that
synchronization with Automatic Identity Range Handing enabled DOES work for
Access databases as well even though the warning states otherwise! Im using
JET to connect to access and not the desktop engine. Once the threshold is
reached, and after the merge agent kicks in, the indentity seed is reset to
the appropriate values on both the access and the sql server side.
Appropriate check constraints are also placed.
I just want to make sure if this is correct - and this warning is just
bogus.
Thanks,
Girish
Hi Girish,
From your descriptions, I understood that you would like to know whether it
matters when warning is shown for "automatic identity ranges are being
used". Have I understood you? If there is anything I misunderstood, please
feel free to let me know.
Based on my scope, there are some known issues for us by using automatic
identity ranges. You could have a view on them
BUG: Unable to Change Identity Range of Publisher If You Use Auto Identity
Range
http://support.microsoft.com/default...b;en-us;310540
BUG: Identity Range Not Adjusted on Publisher When Merge Agent Runs
Continuously
http://support.microsoft.com/default...b;en-us;304706
Additionaly, there is a by design issue for automatic identity range based
on the descriptions in the following documents.
PRB: Automatic Identity Range Handling Is Not Correct If the Merge Agent
Runs an Insert Trigger
http://support.microsoft.com/default...b;en-us;324361
I am afraid the above all are why this warning is shown up.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

automatic identity range handling - newbie question

I am having problems when attempting to publish my database. When I try to
set the automatic identity range handler, the checkbox is dimmed out.
I am using SQL Server 2000 and my subscribers are using Windows CE. I have
set all the indentity fields to INT (2). Everything seems to be in order, but
the checkbox is still dimmed out.
All help will be greatly appreciated
Dale
Dale,
do you already have a subscription to this publication? If it is dimmed out
when looking at the publisher properties, you'll have to drop the
subscriptions, drop the article (apply) then readd the article and it will
then be possible to have automatic range management.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the reply.
I am very new to this, so could you please explain how I "drop" the
subscriptions, and "drop" the article.
thanks
Dale
"Paul Ibison" wrote:

> Dale,
> do you already have a subscription to this publication? If it is dimmed out
> when looking at the publisher properties, you'll have to drop the
> subscriptions, drop the article (apply) then readd the article and it will
> then be possible to have automatic range management.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Dale,
you can right-click on the publication and select the publisher properties.
On the subscriptions tab, delete each one. Apply. On hte articles tab,
uncheck the table in question. Apply. Next, check this article (table) and
on the article properties you'll be able to enable automatic range
management. After that, readd the subscriptions and run the snapshot agent
and merge agents.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for your help Paul!
I found the problem, one of the identity fields was accidently set to
cluster. Once fixed the publication went smoothly.
BTW your website is great!
Dale
"Paul Ibison" wrote:

> Dale,
> you can right-click on the publication and select the publisher properties.
> On the subscriptions tab, delete each one. Apply. On hte articles tab,
> uncheck the table in question. Apply. Next, check this article (table) and
> on the article properties you'll be able to enable automatic range
> management. After that, readd the subscriptions and run the snapshot agent
> and merge agents.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Auto-increment my primary key: why 2 instead of 1?

Hi all,

I have a table where I have my ProdPK set up as Primary key, turned on "Is Identity" and set the Identity increment to 1. But each time I add a new item, the number incremented by 2... I have couple of other tables and they are all fine, just this particular table increased twice as it should. I check the setting against other tables and everything seems to be the same.

By the way, this is adding the data to the table inside MS SQL Server Management Studio manually. I haven't done anything in the ASP.NET page yet.

Thank you very much,

Kenny.

That's weird. I would double check the identity column settings, and maybe check for triggers on the table?

|||

Thanks. I did checked the table's setting and the auto-increment was set to "1". Other settings are identical to my other tables. Any suggestions?

Thanks again,

Kenny.

|||

Can you post the table script and the results you observe by executing "dbcc checkident ( YourTableName )" ? Have you made sure that there are no triggers defined on the table ?

|||

Here is the table's script:

1USE [C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL.2\MSSQL\DATA\MTRENZ.MDF]2GO3/****** Object: Table [dbo].[T_PRODUCTS] Script Date: 12/22/2007 11:58:52 ******/4SET ANSI_NULLS ON5GO6SET QUOTED_IDENTIFIER ON7GO8SET ANSI_PADDING ON9GO10CREATE TABLE [dbo].[T_PRODUCTS](11[PROD_ID] [int] IDENTITY(1,1) NOT NULL,12[ACC_TYPE_ID_FK] [int] NOT NULL,13[PROD_NAME] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,14[PROD_DESCR] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,15[PROD_MODEL] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,16[PROD_STATUS] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,17[PROD_PRICE] [smallmoney] NULL,18[PROD_DATE_ADDED] [datetime] NOT NULL CONSTRAINT [DF_T_PRODUCTS_PROD_DATE_ADDED] DEFAULT (getdate()),19[PROD_ADDED_BY] [uniqueidentifier] NULL,20[PROD_NO] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,21[PROD_NOTES] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,22[PROD_PIC_S] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,23[PROD_PIC_L] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,24[PROD_NEW] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,25 CONSTRAINT [PK_T_PRODUCTS] PRIMARY KEY CLUSTERED26(27[PROD_ID] ASC28)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]29) ON [PRIMARY]3031GO32SET ANSI_PADDING OFF33GO34USE [C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL.2\MSSQL\DATA\MTRENZ.MDF]35GO36ALTER TABLE [dbo].[T_PRODUCTS] WITH NOCHECK ADD CONSTRAINT [ACT_TYPE_ID_FK] FOREIGN KEY([ACC_TYPE_ID_FK])37REFERENCES [dbo].[T_ACC_TYPES] ([ACC_TYPE_ID])38NOT FOR REPLICATION

This is what it return when I run the dbcc:

Checking identity information: current identity value '4', current column value '4'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Currently there are only two rows in my database, with the primary key values 2 and 4. It skipped 1 and 3.

Thank you,

Kenny.

|||

I don't know what exactly is the problem, but my database primary key started at 2, then 4, then 8... After 8, everything then become normal, meaning they increased correctly by 1!!!

I still don't know why, but it solved the problem itself I guess!

Thanks all,

Kenny.

Autoincrement

Hello
I've a table of Users with an identity key
Some records are inserted by a replication system which sends records with
key like 2-4-6-8 ...
and put them into the table with a INSERT sql
Other records are inserted via web
I need that the records inserted via web takes a key like 1-3-5-7 ...
I've set the identity seed to 1 and identity increment to 2
I've made a test
1. Inserted some record by replication system
2. If I try to insert a new record manually (by enterprise manager) the new
key is a par number instead of an odd
What's wrong?
Can you help me?Why don't you instead of doing that create another field called Origin
make it a bit when it's from the web give it a value of 1 otherwise 0
Your identity will be Old Key + 2 (that's your increment)
http://sqlservercode.blogspot.com/
"Denis" wrote:

> Hello
> I've a table of Users with an identity key
> Some records are inserted by a replication system which sends records with
> key like 2-4-6-8 ...
> and put them into the table with a INSERT sql
> Other records are inserted via web
> I need that the records inserted via web takes a key like 1-3-5-7 ...
> I've set the identity seed to 1 and identity increment to 2
> I've made a test
> 1. Inserted some record by replication system
> 2. If I try to insert a new record manually (by enterprise manager) the ne
w
> key is a par number instead of an odd
> What's wrong?
> Can you help me?
>
>|||Denis,

> What's wrong?
Is the property "not for replication" set in this identity column?
When the values are inserted from the replication, sql server takes that
number as the last identity value inserted in the table, so if the las value
was 8 then when you insert from the web using "set identity_insert t1 off"
will increment that value with the identity increment 8+2 and this will be
the next value to be inserted.
Example:
create table t1(
c1 int not null identity(1, 2)
)
go
insert into t1 default values
insert into t1 default values
insert into t1 default values
go
select
ident_seed('t1'),
ident_incr('t1'),
ident_current('t1')
go
set identity_insert t1 on
go
insert into t1(c1) values(2)
insert into t1(c1) values(4)
insert into t1(c1) values(6)
insert into t1(c1) values(8)
go
select
ident_seed('t1'),
ident_incr('t1'),
ident_current('t1')
go
set identity_insert t1 off
go
insert into t1 default values
go
select * from t1 order by c1 asc
go
drop table t1
go
AMB
"Denis" wrote:

> Hello
> I've a table of Users with an identity key
> Some records are inserted by a replication system which sends records with
> key like 2-4-6-8 ...
> and put them into the table with a INSERT sql
> Other records are inserted via web
> I need that the records inserted via web takes a key like 1-3-5-7 ...
> I've set the identity seed to 1 and identity increment to 2
> I've made a test
> 1. Inserted some record by replication system
> 2. If I try to insert a new record manually (by enterprise manager) the ne
w
> key is a par number instead of an odd
> What's wrong?
> Can you help me?
>
>

Auto Number PROBLEM

I have a table namely Product with the following identity modifiers:-
Column- "Num"
Identity-Yes
Identity Seed-1
Identity Increment-1

My problem is everytime i delete a row in the table Product, the Num column after the deleted row will not automatically replace the deleted Num column's data.
Let say:

Num Name
30 Apple
31 Orange
32 Pineapple

If I delete row with Num 31, the Num column after the deleted row will not change to 31 but remain 32.

Num Name
30 Apple
32 Pineapple

What can I do to ensure that the Num column (Num 32) will change to Num 31?

Thank you.what you could do is think very hard about what you seem to want to do

suppose you have an Orders table, which contains customer orders for all products you've sold

if you renumber the products in the products table, you must therefore renumber the Orders table too

this could shut your database down for a few hours

and why? what is the benefit?

autonumbers should never be reassigned

it's not as though you're going to run out of numbers

you could realistically set your identity seed at 183,527,426 and your increment at 3,743 and you would still not run out of numbers for several centuries|||you can add a new column to your table

RowNumber

Id Fruit RowNumber
31 Apple 100
32 Banana 101
33 Pineapple 102

If you add a new fruit
RowNumber = Count(*) of the table

If you delete a fruit
you update RowNumber=RowNumber-1 for all the fruits over the
RowNumber of the deleted fruit

If you delete the Banana

Update Table Fruits Set RowNumber=RowNumber-1
Where RowNumber>(Select RowNumber From Fruits Where Id=32)

Delete Fruits Where Id=32|||But some would say this is not a GREAT thing to do
'cause it's not performant.

But it will work|||i would say it is not a great thing to do because of (a) the impact it has on related tables, and (b) the total absence of ROI (http://searchwebservices.techtarget.com/sDefinition/0,,sid26_gci214270,00.html)

also, not only do you have to update the fruits table, you also have to update the desserts table, the pie table, and any other table which has foreign keys to the fruit table

and i repeat: what exactly is the benefit of doing it?

if it is important that there be no gap in numbers, then i strongly suggest that an autonumber is the wrong design choice|||in my case there is no impact on other tables
because I don't touch the ID number
but the new independant column ROWNUMBER !!!

Auto Number or Identity Seed on Oracle Database

Need help on the Auto Number or Identity Seed on the Oracle Database

I got an Access database that need to be converted to Oracle 9i.
Somehow the Trigger we created to simulate the "AUTO NUMBER" on Access
could not create the sequence number as soon as the value has been
inserted. The sequence number can only be created after we go to the
second line. Please see the trigger below.

Is there anyway we could create a trigger that could create the
sequence number as soon as we enter a value? It should be very
similar to the "Auto Number" on Access, or "Identity Seed" on SQL
Server.

------------------
1. sequence SNP.SECTION_ID_SQ:

CREATE SEQUENCE SNP.SECTION_ID_SQ
START WITH 1
INCREMENT BY 1
NOMINVALUE
NOMAXVALUE
NOCYCLE
CACHE 20
NOORDER
/
GRANT SELECT ON SNP.SECTION_ID_SQ TO "PUBLIC"
/

2. Trigger SNP.SNP001_T_I_GET_NEXT_SECTION_ID:

CREATE OR REPLACE TRIGGER SNP.SNP001_T_I_GET_NEXT_SECTION_ID
BEFORE INSERT
ON SNP.SNP001_SECTION
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW WHEN (new.section_id IS NULL)
BEGIN
SELECT section_id_sq.nextval
INTO :new.section_id
FROM dual;
END;Ken wrote:
[snip]

Already answered in c.d.m.a and a shedload of oracle groups, why are you
asking an Access/Oracle question in a SQL Server group? You're going to
annoy Celko in a minute <g
--
This sig left intentionally blank

Auto increment problem

Hi,
I deleted a table which contains 200 rows using delete statement. In that
table i have used a identity column.
Now i inserted more data in to that table, but i am getting the identity
column series from 201........
So how can i reset the identity column to 1 without recreating the table.
Please help me..."Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>
Check out
DBCC CHECKIDENT
in Books Online (online help).
Note however, IDENTITY columns are only guaranteed to be sequential, NOT
contiguous
If you roll back a transaction for example, you will lose the contiguous
nature.
create table test_foo
(
id int identity (1,1),
foo varchar(15)
)
insert into test_foo (foo) values ('first row')
begin tran
insert into test_foo (foo) values ('second row')
commit tran
begin tran
insert into test_foo (foo) values ('third row')
rollback tran
insert into test_foo (foo) values ('fourth row')
select * from test_foo
drop table test_foo
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||You could also script the table, drop the table and recreate
--
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=uk/">http://www.itjobfeed.com">UK IT Jobs</a>
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>|||1) drop FK's if any
2) TRUNCATE TABLENAME
Mex
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>|||2) TRUNCATE TABLE NAME
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:eWEvyTWYHHA.4308@.TK2MSFTNGP05.phx.gbl...
> 1) drop FK's if any
> 2) TRUNCATE TABLENAME
>
> Mex
>
> "Binoy" <binoy.a@.eostek.com> wrote in message
> news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I deleted a table which contains 200 rows using delete statement. In
>> that table i have used a identity column.
>> Now i inserted more data in to that table, but i am getting the identity
>> column series from 201........
>> So how can i reset the identity column to 1 without recreating the table.
>> Please help me...
>|||Thanks very much ofr your reply , i got the correct solution.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uyQtu3UYHHA.3824@.TK2MSFTNGP02.phx.gbl...
> "Binoy" <binoy.a@.eostek.com> wrote in message
> news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I deleted a table which contains 200 rows using delete statement. In
>> that table i have used a identity column.
>> Now i inserted more data in to that table, but i am getting the identity
>> column series from 201........
>> So how can i reset the identity column to 1 without recreating the table.
>> Please help me...
> Check out
> DBCC CHECKIDENT
> in Books Online (online help).
> Note however, IDENTITY columns are only guaranteed to be sequential, NOT
> contiguous
> If you roll back a transaction for example, you will lose the contiguous
> nature.
> create table test_foo
> (
> id int identity (1,1),
> foo varchar(15)
> )
>
> insert into test_foo (foo) values ('first row')
> begin tran
> insert into test_foo (foo) values ('second row')
> commit tran
> begin tran
> insert into test_foo (foo) values ('third row')
> rollback tran
> insert into test_foo (foo) values ('fourth row')
> select * from test_foo
>
> drop table test_foo
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>

Auto increment problem

Hi,
I deleted a table which contains 200 rows using delete statement. In that
table i have used a identity column.
Now i inserted more data in to that table, but i am getting the identity
column series from 201........
So how can i reset the identity column to 1 without recreating the table.
Please help me...
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>
Check out
DBCC CHECKIDENT
in Books Online (online help).
Note however, IDENTITY columns are only guaranteed to be sequential, NOT
contiguous
If you roll back a transaction for example, you will lose the contiguous
nature.
create table test_foo
(
id int identity (1,1),
foo varchar(15)
)
insert into test_foo (foo) values ('first row')
begin tran
insert into test_foo (foo) values ('second row')
commit tran
begin tran
insert into test_foo (foo) values ('third row')
rollback tran
insert into test_foo (foo) values ('fourth row')
select * from test_foo
drop table test_foo
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||You could also script the table, drop the table and recreate
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>
|||1) drop FK's if any
2) TRUNCATE TABLENAME
Mex
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>
|||2) TRUNCATE TABLE NAME
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:eWEvyTWYHHA.4308@.TK2MSFTNGP05.phx.gbl...
> 1) drop FK's if any
> 2) TRUNCATE TABLENAME
>
> Mex
>
> "Binoy" <binoy.a@.eostek.com> wrote in message
> news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
>
|||Thanks very much ofr your reply , i got the correct solution.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uyQtu3UYHHA.3824@.TK2MSFTNGP02.phx.gbl...
> "Binoy" <binoy.a@.eostek.com> wrote in message
> news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Check out
> DBCC CHECKIDENT
> in Books Online (online help).
> Note however, IDENTITY columns are only guaranteed to be sequential, NOT
> contiguous
> If you roll back a transaction for example, you will lose the contiguous
> nature.
> create table test_foo
> (
> id int identity (1,1),
> foo varchar(15)
> )
>
> insert into test_foo (foo) values ('first row')
> begin tran
> insert into test_foo (foo) values ('second row')
> commit tran
> begin tran
> insert into test_foo (foo) values ('third row')
> rollback tran
> insert into test_foo (foo) values ('fourth row')
> select * from test_foo
>
> drop table test_foo
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>

Auto increment problem

Hi,
I deleted a table which contains 200 rows using delete statement. In that
table i have used a identity column.
Now i inserted more data in to that table, but i am getting the identity
column series from 201........
So how can i reset the identity column to 1 without recreating the table.
Please help me..."Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>
Check out
DBCC CHECKIDENT
in Books Online (online help).
Note however, IDENTITY columns are only guaranteed to be sequential, NOT
contiguous
If you roll back a transaction for example, you will lose the contiguous
nature.
create table test_foo
(
id int identity (1,1),
foo varchar(15)
)
insert into test_foo (foo) values ('first row')
begin tran
insert into test_foo (foo) values ('second row')
commit tran
begin tran
insert into test_foo (foo) values ('third row')
rollback tran
insert into test_foo (foo) values ('fourth row')
select * from test_foo
drop table test_foo
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||You could also script the table, drop the table and recreate
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>|||1) drop FK's if any
2) TRUNCATE TABLENAME
Mex
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>|||2) TRUNCATE TABLE NAME
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:eWEvyTWYHHA.4308@.TK2MSFTNGP05.phx.gbl...
> 1) drop FK's if any
> 2) TRUNCATE TABLENAME
>
> Mex
>
> "Binoy" <binoy.a@.eostek.com> wrote in message
> news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
>|||Thanks very much ofr your reply , i got the correct solution.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uyQtu3UYHHA.3824@.TK2MSFTNGP02.phx.gbl...
> "Binoy" <binoy.a@.eostek.com> wrote in message
> news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Check out
> DBCC CHECKIDENT
> in Books Online (online help).
> Note however, IDENTITY columns are only guaranteed to be sequential, NOT
> contiguous
> If you roll back a transaction for example, you will lose the contiguous
> nature.
> create table test_foo
> (
> id int identity (1,1),
> foo varchar(15)
> )
>
> insert into test_foo (foo) values ('first row')
> begin tran
> insert into test_foo (foo) values ('second row')
> commit tran
> begin tran
> insert into test_foo (foo) values ('third row')
> rollback tran
> insert into test_foo (foo) values ('fourth row')
> select * from test_foo
>
> drop table test_foo
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>

Auto identity in republisher scenario

Hi to everyone,
Is auto identity range supported in publisher-subscriber scenario?
In example:
Server1 publish database1 with article Table1 with auto identity range.
Server1 reserve itself the range 1-100.
Server2 subscribe to Server1 and get and identity range 101-200.
Server2 is also a republisher for database1 so it allows Client1 to
subscribe and replicate with it.
Which identity range does Client1 get?
I suppose Client1 will get a 201-300 range from Server2, which will
eventually conflict if the same range is assigned to other clients directly
from Server1.
Am I right or am I wrong?
Kind regards,
Corrado
You are correct that it will eventually conflict. Auto-identity ranges is
something that I've played with and then never used. There are just too
many potential problems with them at this point. You can wind up with range
skipping and run out of values long before you would have. I've also had
cases where you generated duplicate values.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||are you using merge replication? it is unclear from your post.
Server 1 will have a check constraint on the identity column allowing a
range of identity values from 101-200. Server 2 from 201-300. Client 1 will
have a range from 320-330.
The ranges will be maintained between server 2 and client 1 with a different
set of ranges and thresholds than between Server 1 and Server 2.
There are some caveats with automatic identity range management. First you
must use a representative data type that will be good for your range of
values - ie smallint will probably be too small for most applications.
Secondly pick a range which is larger than your largest batch insert. The
automatic range adjustment is run after a transaction is distruted for
transactional publications or covergence is achieved if you are using merge.
Third most dba's will set the maxiumn ranges they expect their replication
solution to use in the life time of thier probject - sort of like set it and
forget it.
"Corrado Labinaz" <corradolab@.virgilio.it> wrote in message
news:uT2xsySMEHA.3668@.TK2MSFTNGP11.phx.gbl...
> Hi to everyone,
> Is auto identity range supported in publisher-subscriber scenario?
> In example:
> Server1 publish database1 with article Table1 with auto identity range.
> Server1 reserve itself the range 1-100.
> Server2 subscribe to Server1 and get and identity range 101-200.
> Server2 is also a republisher for database1 so it allows Client1 to
> subscribe and replicate with it.
> Which identity range does Client1 get?
> I suppose Client1 will get a 201-300 range from Server2, which will
> eventually conflict if the same range is assigned to other clients
directly
> from Server1.
> Am I right or am I wrong?
> Kind regards,
> Corrado
>

auto identity for each Type

Hello,

I am working on an accounting system using VB.NET and sql server 2005 as a database. the application should be used by multiple users.
i have a the following structure:
Voucher: ID (primary), Date,TypeID, ReferenceCode, ....
Type: ID, Code, Name. (the user can add new type anytime!)
(Ex: PV- payment voucher, JV - Journal Voucher ,...)

When adding a voucher the user will choose a type, according to this type (for each year) a counter will be increminted.
for example: PV1, PV2...PV233,... the other type will have its separate counter JV1, JV2 ,...JV4569,..
I am using the sqlTransaction cause i am doing other operations that should be transactional with the insertion of the Voucher.

The question is :
What is the best solution to generate a counter for each type?(With code sample)

Thanks.do you really need to have the 'PV' and 'JV' before each value? if you could use ints, then you could use identity columns. That's the standard way of doing this.

You can always tack on a JV or PV in the front end if that's the way your boss wants it to look in a report or something.

from BOL:

IDENTITY

Indicates that the new column is an identity column. When a new row is added to the table, Microsoft® SQL Server™ provides a unique, incremental value for the column. Identity columns are commonly used in conjunction with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. Only one identity column can be created per table. Bound defaults and DEFAULT constraints cannot be used with an identity column. You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).|||if you were using mysql, this functionality (starting a new auto_increment within each type group) is built in

it's impossible to do this with an IDENTITY column

you will have to generate your own numbers, and i would recommend very strongly against it|||the counter in the question is the ReferenceCode in the Voucher table
Voucher: ID (primary), Date,TypeID, ReferenceCode.
so for each added voucher and according to the TypeID a the reference code will be generated. let say the last counter for the PV type is 230 so the referenceCode will be PV231. if the Type is JV and the last counter is 566 then the ReferenceCode will be JV567 and so on.
We don't have to forget that we are working in a multi user enviroment, and the Reference Code should be unique .|||put the JV or PV in another field and concatenate it in the front end. smart numbers are stupid and loved by the accounting types. this kind of things slow down joins and causes other kinds of pain. i have not seen smart numbers in a project for five years and that was a legacy foxpro app.

Auto identity field with check constraint

We are testing with the CTP Sept. version of SQL 2005 in hopes of
moving to the released version of SQL 2005. We have a table that has
an autoidentity range, with "Not For Replication" set to yes. (Is in a
merge-snapshot publication.) In SQL 2000, you used to be able to
uncheck the constraint "Enforce For inserts and Updates". Does anyone
know why you cannot do this in SQL 2005? This identity field
(location_ID) ss needed so that my users can add their own address
information concerning a prospect company, but I also need to insert
"real" location_IDs from existing companies. I try doing a SET
IDENTITY_INSERT ON, and try to insert but receive the below error:
It conflicted with an identity range check constraint
Does anyone know how I can work around this? (Is it resolved in the
released version of SQL 2005?)
Thanks,
Amy
Have you tried altering the check constraint directly: ALTER TABLE
cnst_example NOCHECK CONSTRAINT salary_cap. Resetting the current identity,
then reenabling the check constraint?
Rgds,
Paul Ibison
|||Paul Ibison wrote:
> Have you tried altering the check constraint directly: ALTER TABLE
> cnst_example NOCHECK CONSTRAINT salary_cap. Resetting the current identity,
> then reenabling the check constraint?
> Rgds,
> Paul Ibison
Paul, That works.. Thanks.. Do you know of any issues with doing this
if a user happens to replicate while this is happening? I insert data
in the morning, so chances are very slim.
Thanks for your help!
Amy
|||It won't change the value of the identity attribute so things can't go wrong
that way. the potential issue with what you have is that the automatic range
management algorithm doesn't take account of your extra values when
allocating a new range once this one has been completed. Also I'd be
concerned that a subscriber may have this range itself and cause a PK
overlap.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
We won't have a primary key problem, because the initial identity
number was set way above any number I will be inputting into the table.
Thanks so much for your help!
Amy

Auto generating numeric column

Hi There,

I want to create a column that usto numbers upto a specified value, and the resets. I've tried using the identity column and then using DBCC CHECKIDENT, but this doesnt doesn't have the desired affect. Here's an example of what I'm after.

AutoRow
1
2
3
4 --- reset
1
2
3
4 ---reset

Any help would be great,

many thanks

Stuart

An indentity field is going to require unique values, so reseting it isnt going to work, your going to throw an SQL Exception.

Are you trying to accomplish this just on the SQL server, or can you do it as part of your application?

|||Unfortuanatley it needs to be done on the server|||

Check this sample to see whether it helps:

DROP TABLE tbl_testIden
go

CREATE TABLE tbl_testIden (id int identity(1,1), name sysname)
GO

CREATE TRIGGER trg_tsetIden ON tbl_testIden FOR INSERT,UPDATE
AS
IF (IDENT_CURRENT('tbl_testIden')>=4)
DBCC CHECKIDENT('tbl_testIden',RESEED,0)
GO

DECLARE @.i INT
SET @.i=1
WHILE (@.i<100)
BEGIN
INSERT INTO tbl_testIden(name) SELECT 'Person#'+CONVERT(VARCHAR(8),@.i)
SET @.i=@.i+1
END
go
SELECT * FROM tbl_testIden

|||

Hi There,

I managed to get it working using the Row_Number function. It has some very useul derivetives also.

Row_Number simply creates and incremental list which is reset when thepartiation by value changes.

ROW_NUMBER()OVER(PARTITIONBY ReqPointIdORDERBY ReqPointIdDESC)AS RowGroup

I'll look at your approach as it alows me to specify and upper limit.

Many thanks for your time

Regards

Stuart

|||

You are looking for a combination of ROW_NUMBER and %, but without more of the query, I can't help you. Generically...

SELECT (RowGroup % 4)+1 As AutoGen, more columns here

FROM (

SELECT ROW_NUMBER() OVER (something) AS RowGroup, more columns here

) t1

You may even be able to combine the two, but I'm not familiar enough with ROW_NUMBER to say that would work, but if it does... Then...

SELECT (ROW_NUMBER() OVER (something) % 4)+1 AS RowGroup, more columns here

FROM somewhere

should work as well. I think most people (myself included) were confused because you said you wanted an autogenerated column. That tends to lead people to believe you are speaking of a table column, not a field in a resultset.