Retrieve Identity on insert: what if table has 2 identity columns

I have a table with 2 identity columns:
ID int identity(200100,1) not null,
OrderNo int Identity(550000,1) not null,
OrderName varchar(60) not null,
etc.
How do I get the values set by SQL server for ID and OrderNo when I insert
a new row in the table ?
Thanks in advance,Eve
If you are sitting on SQL Server 2000 then perform after inserting
SELECT SCOPE_IDENTITY()
Otherwise SELECT @.@.IDENTITY
"Eve" <Eve@.discussions.microsoft.com> wrote in message
news:F1824CDF-BC5D-4E89-98DA-6364A036B577@.microsoft.com...
> I have a table with 2 identity columns:
> ID int identity(200100,1) not null,
> OrderNo int Identity(550000,1) not null,
> OrderName varchar(60) not null,
> etc.
> How do I get the values set by SQL server for ID and OrderNo when I
insert
> a new row in the table ?
> Thanks in advance,
>|||It seems that there is a relationship between the two numbers.Seeing as you
can't have two identity columns. Why don't you use a formula for the ordern
o
Alter table <table> add OrderNo As ID + 300000.
"Eve" wrote:

> I have a table with 2 identity columns:
> ID int identity(200100,1) not null,
> OrderNo int Identity(550000,1) not null,
> OrderName varchar(60) not null,
> etc.
> How do I get the values set by SQL server for ID and OrderNo when I inser
t
> a new row in the table ?
> Thanks in advance,
>|||And on another note. I can't see why you need and ID since the OrderNo is
already unique...
Why is this ?
"Eve" wrote:

> I have a table with 2 identity columns:
> ID int identity(200100,1) not null,
> OrderNo int Identity(550000,1) not null,
> OrderName varchar(60) not null,
> etc.
> How do I get the values set by SQL server for ID and OrderNo when I inser
t
> a new row in the table ?
> Thanks in advance,
>|||I think you are mistaken. Only one IDENTITY column per table is permitted.
Please post the CREATE TABLE statement for your table so that we can
understand what you mean.
In SQL Server 2000 use SCOPE_IDENTITY() to retrieve the last inserted
IDENTITY value.
David Portas
SQL Server MVP
--