a typical proublem related with auto generated id

hi
I am really stuck up with this problem
here is my problems
I am inserting data in 3 tables in a stored procedure
I have a table A with a auto generated id let ID
and I have updated the table A with new record (with ID)
now I have to make use of this id in the corresponding update in table B & C
in the same stored procedure
now how I can get this ID avail for the tables B and C.
one solution is to use max of the ids generated but this doesnt going to
work in case of multiple updated like a lot of users are making the updates
on the database. I am using SQL Server 2000 as DB.
please suggest me any solution
Regards
BalaHave a look at the @.@.IDENTITY and SCOPE_IDENTITY() functions in Books on
Line.
--
Regards
Barry McAuslin
----
--
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"bala" <bala_at_web@.yahoo.com> wrote in message
news:u7cv2AP3EHA.1408@.TK2MSFTNGP10.phx.gbl...
> hi
> I am really stuck up with this problem
> here is my problems
> I am inserting data in 3 tables in a stored procedure
> I have a table A with a auto generated id let ID
> and I have updated the table A with new record (with ID)
> now I have to make use of this id in the corresponding update in table B &
C
> in the same stored procedure
> now how I can get this ID avail for the tables B and C.
> one solution is to use max of the ids generated but this doesnt going to
> work in case of multiple updated like a lot of users are making the
updates
> on the database. I am using SQL Server 2000 as DB.
> please suggest me any solution
> Regards
> Bala
>|||bala wrote:
> hi
> I am really stuck up with this problem
> here is my problems
> I am inserting data in 3 tables in a stored procedure
> I have a table A with a auto generated id let ID
> and I have updated the table A with new record (with ID)
> now I have to make use of this id in the corresponding update in
> table B & C in the same stored procedure
> now how I can get this ID avail for the tables B and C.
> one solution is to use max of the ids generated but this doesnt going
> to work in case of multiple updated like a lot of users are making
> the updates on the database. I am using SQL Server 2000 as DB.
> please suggest me any solution
> Regards
> Bala
Please don't multi-post. See my comments in the other NG.
--
David Gugick
Imceda Software
www.imceda.com

a typical proublem related with auto generated id

hi
I am really stuck up with this problem
here is my problems
I am inserting data in 3 tables in a stored procedure
I have a table A with a auto generated id let ID
and I have updated the table A with new record (with ID)
now I have to make use of this id in the corresponding update in table B & C
in the same stored procedure
now how I can get this ID avail for the tables B and C.
one solution is to use max of the ids generated but this doesnt going to
work in case of multiple updated like a lot of users are making the updates
on the database. I am using SQL Server 2000 as DB.
please suggest me any solution
Regards
Bala
Have a look at the @.@.IDENTITY and SCOPE_IDENTITY() functions in Books on
Line.
Regards
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"bala" <bala_at_web@.yahoo.com> wrote in message
news:u7cv2AP3EHA.1408@.TK2MSFTNGP10.phx.gbl...
> hi
> I am really stuck up with this problem
> here is my problems
> I am inserting data in 3 tables in a stored procedure
> I have a table A with a auto generated id let ID
> and I have updated the table A with new record (with ID)
> now I have to make use of this id in the corresponding update in table B &
C
> in the same stored procedure
> now how I can get this ID avail for the tables B and C.
> one solution is to use max of the ids generated but this doesnt going to
> work in case of multiple updated like a lot of users are making the
updates
> on the database. I am using SQL Server 2000 as DB.
> please suggest me any solution
> Regards
> Bala
>
|||bala wrote:
> hi
> I am really stuck up with this problem
> here is my problems
> I am inserting data in 3 tables in a stored procedure
> I have a table A with a auto generated id let ID
> and I have updated the table A with new record (with ID)
> now I have to make use of this id in the corresponding update in
> table B & C in the same stored procedure
> now how I can get this ID avail for the tables B and C.
> one solution is to use max of the ids generated but this doesnt going
> to work in case of multiple updated like a lot of users are making
> the updates on the database. I am using SQL Server 2000 as DB.
> please suggest me any solution
> Regards
> Bala
Please don't multi-post. See my comments in the other NG.
David Gugick
Imceda Software
www.imceda.com

A question about sqlxml

Who use sqlxml .net?

How can I write code like this:

select count(1) as count from Orders for xml auto

That's error.

And what is the correct sqlxml code?

Tks.

Hi,

the code works fine with SQL2005 but fails on SQL2K

Eralper

http://www.kodyaz.com

|||

You could write it like below for SQL Server 2000:

select count from (select count(1) as count from Orders) as Orders for xml auto

A list of Auto exec SPs

Hello All,
A SP can be made to automatically execute when SQLServer restarts using the
store procedure "sp_procoption".
But is there a way to find out the list of SPs that have been congifured to
execute automatically. I took over as a DBA for an existing system and I was
wondering if there are any SP configured this way.
Thanks,
rgnIf you look at the definition for sp_procoption, you will discover the
following line of code:
UPDATE sysobjects SET status = (status & ~2) | (2 * @.intOptionValue) WHERE
id = @.tabid
This tells you, along with the rest of the definition, that if you query the
master.dbo.sysobjects table for status values of 2 on xtypes of X or P you
will find you startup procs and extended procs.
Sincerely,
Anthony Thomas
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:3DE27C29-DEDC-4D0B-AEB0-2C34553BB3E7@.microsoft.com...
> Hello All,
> A SP can be made to automatically execute when SQLServer restarts using
the
> store procedure "sp_procoption".
> But is there a way to find out the list of SPs that have been congifured
to
> execute automatically. I took over as a DBA for an existing system and I
was
> wondering if there are any SP configured this way.
> Thanks,
> rgn|||Hi rgn
You can use the OBJECTPROPERTY function.
SELECT name
FROM sysobjects
WHERE type = 'P'
AND OBJECTPROPERTY(id, 'ExecIsStartup') =1
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:3DE27C29-DEDC-4D0B-AEB0-2C34553BB3E7@.microsoft.com...
> Hello All,
> A SP can be made to automatically execute when SQLServer restarts using
> the
> store procedure "sp_procoption".
> But is there a way to find out the list of SPs that have been congifured
> to
> execute automatically. I took over as a DBA for an existing system and I
> was
> wondering if there are any SP configured this way.
> Thanks,
> rgn

A list of Auto exec SPs

Hello All,
A SP can be made to automatically execute when SQLServer restarts using the
store procedure "sp_procoption".
But is there a way to find out the list of SPs that have been congifured to
execute automatically. I took over as a DBA for an existing system and I was
wondering if there are any SP configured this way.
Thanks,
rgn
If you look at the definition for sp_procoption, you will discover the
following line of code:
UPDATE sysobjects SET status = (status & ~2) | (2 * @.intOptionValue) WHERE
id = @.tabid
This tells you, along with the rest of the definition, that if you query the
master.dbo.sysobjects table for status values of 2 on xtypes of X or P you
will find you startup procs and extended procs.
Sincerely,
Anthony Thomas

"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:3DE27C29-DEDC-4D0B-AEB0-2C34553BB3E7@.microsoft.com...
> Hello All,
> A SP can be made to automatically execute when SQLServer restarts using
the
> store procedure "sp_procoption".
> But is there a way to find out the list of SPs that have been congifured
to
> execute automatically. I took over as a DBA for an existing system and I
was
> wondering if there are any SP configured this way.
> Thanks,
> rgn
|||Hi rgn
You can use the OBJECTPROPERTY function.
SELECT name
FROM sysobjects
WHERE type = 'P'
AND OBJECTPROPERTY(id, 'ExecIsStartup') =1
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:3DE27C29-DEDC-4D0B-AEB0-2C34553BB3E7@.microsoft.com...
> Hello All,
> A SP can be made to automatically execute when SQLServer restarts using
> the
> store procedure "sp_procoption".
> But is there a way to find out the list of SPs that have been congifured
> to
> execute automatically. I took over as a DBA for an existing system and I
> was
> wondering if there are any SP configured this way.
> Thanks,
> rgn

A list of Auto exec SPs

Hello All,
A SP can be made to automatically execute when SQLServer restarts using the
store procedure "sp_procoption".
But is there a way to find out the list of SPs that have been congifured to
execute automatically. I took over as a DBA for an existing system and I was
wondering if there are any SP configured this way.
Thanks,
rgnIf you look at the definition for sp_procoption, you will discover the
following line of code:
UPDATE sysobjects SET status = (status & ~2) | (2 * @.intOptionValue) WHERE
id = @.tabid
This tells you, along with the rest of the definition, that if you query the
master.dbo.sysobjects table for status values of 2 on xtypes of X or P you
will find you startup procs and extended procs.
Sincerely,
Anthony Thomas
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:3DE27C29-DEDC-4D0B-AEB0-2C34553BB3E7@.microsoft.com...
> Hello All,
> A SP can be made to automatically execute when SQLServer restarts using
the
> store procedure "sp_procoption".
> But is there a way to find out the list of SPs that have been congifured
to
> execute automatically. I took over as a DBA for an existing system and I
was
> wondering if there are any SP configured this way.
> Thanks,
> rgn|||Hi rgn
You can use the OBJECTPROPERTY function.
SELECT name
FROM sysobjects
WHERE type = 'P'
AND OBJECTPROPERTY(id, 'ExecIsStartup') =1
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:3DE27C29-DEDC-4D0B-AEB0-2C34553BB3E7@.microsoft.com...
> Hello All,
> A SP can be made to automatically execute when SQLServer restarts using
> the
> store procedure "sp_procoption".
> But is there a way to find out the list of SPs that have been congifured
> to
> execute automatically. I took over as a DBA for an existing system and I
> was
> wondering if there are any SP configured this way.
> Thanks,
> rgn