problem with SP to return last @@Identity

I need to retun the last inserted Identity value. - The SP below has a synta
x
error on the Set line. How can I fix it?
create procedure stp_GetIdentity
@.e int output
as
SET NOCOUNT ON
Set @.e = Select @.@.Identity
return
go
Thanks,
Rich"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:E3FF5947-5ED7-4E0A-BCD7-486D27C51375@.microsoft.com...
>I need to retun the last inserted Identity value. - The SP below has a
>syntax
> error on the Set line. How can I fix it?
> create procedure stp_GetIdentity
> @.e int output
> as
> SET NOCOUNT ON
> Set @.e = Select @.@.Identity
> return
> go
> Thanks,
> Rich
Set @.e = @.@.Identity
or
Select @.e = @.@.Identity
Option 1 is preferred for a single assignment.
Also look up scope_identity() in BOL.|||The offending line should be
SELECT @.e = @.@.IDENTITY
however, why create a stored procedure to get @.@.IDENTITY, when you can just
retrieve its value within a batch using SELECT @.@.IDENTITY?
"Rich" wrote:

> I need to retun the last inserted Identity value. - The SP below has a syn
tax
> error on the Set line. How can I fix it?
> create procedure stp_GetIdentity
> @.e int output
> as
> SET NOCOUNT ON
> Set @.e = Select @.@.Identity
> return
> go
> Thanks,
> Rich|||create procedure stp_GetIdentity
@.e int output
as
SET NOCOUNT ON
Select @.e = @.@.Identity
return
go
--This seems to work
"Rich" wrote:

> I need to retun the last inserted Identity value. - The SP below has a syn
tax
> error on the Set line. How can I fix it?
> create procedure stp_GetIdentity
> @.e int output
> as
> SET NOCOUNT ON
> Set @.e = Select @.@.Identity
> return
> go
> Thanks,
> Rich|||Thanks. From what I understand Scope_Identity works within a specified scop
e
which I interpret to mean if you insert a row into tbl1 which contains 10
rows in one procedure and also insert a row into tbl2 which contains 700 row
s
in another procedure and you only want to return the Identity value in tbl1
you could use Scope_Identity.
May I ask how Scope_Identity would be implemented in my SP to return the
Identity value of the last inserted row into tbl1?
?
"Raymond D'Anjou" wrote:

> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:E3FF5947-5ED7-4E0A-BCD7-486D27C51375@.microsoft.com...
> Set @.e = @.@.Identity
> or
> Select @.e = @.@.Identity
> Option 1 is preferred for a single assignment.
> Also look up scope_identity() in BOL.
>
>|||"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:6E159771-9CB3-45ED-95D1-7EF10E72DFFA@.microsoft.com...
> Thanks. From what I understand Scope_Identity works within a specified
> scope
> which I interpret to mean if you insert a row into tbl1 which contains 10
> rows in one procedure and also insert a row into tbl2 which contains 700
> rows
> in another procedure and you only want to return the Identity value in
> tbl1
> you could use Scope_Identity.
> May I ask how Scope_Identity would be implemented in my SP to return the
> Identity value of the last inserted row into tbl1?
>
set @.a = scope_identity()
scope_identity() has another advantage.
If you have a trigger on a table that inserts a row into another table with
an identity column.
@.@.identity in your stored procedure will return the ID of the last insert,
that is, the one in your trigger.
scope_identity() will return the ID you want.sql

Problem with role permissions - supplemental info

I foud a way to fix this, but it's a long process (this DB is huged), and I
would still like to know why this hapened. Here is what I did.
To fix a database (refered as orgDB below) with a role permissions problem
(won't save), do the following
a.. Create a new database (refered as tmpDB below)
b.. Import all tables from orgDB into tmpDB
c.. in tmpDB, run scrip to add users and set permissions
d.. Detach both database (right-click on db name, All Tasks, Detach
database). The DBs will disapear from the list.
e.. In Windows explorer, rename the original data (mdf) and log (ldf)
files: orgDB.mfd >> xx_orgDB.mdf and orgDB.lfd >> xx_orgDB.ldf
f.. In Windows explorer, rename the temporary data (mdf) and log (ldf)
filesto the original name: tmpDB.mfd >> orgDB.mdf and tmpDB.lfd >>
orgDB.ldf
g.. In Enterprise Manager, right-click on Databases / All Tasks / Attach
Database and follow the wizzard and select the orgDB.mdf file.
If there is a simplier (shorter) way to do this, I would like to know it.
C.R.
"C.R." <Cmdr_W_Riker@.HotMail.com> wrote in message
news:eYGmqGBwDHA.1744@.TK2MSFTNGP12.phx.gbl...

quote:

---

> We are experiencing a problems setting permissions on roles on SQL Server
> 2000. Our SQL server is holding several databases (60+) and on some of

---

them

quote:

---

> (not all), when we change permissions on tables for a role (Public for
> example), they do not seam to be saved.We tried through Enterprise Manager
> and SQL Query Analyser with the same result. We change the permission,
> click on OK, go out of EM and when we go back in, the permissions are back
> the way they were before we changed them What is puzzling us is that it
> works fine on some databases and not on other. We do not know of any
> database options that would prevent changing the permissions, is there

---

one?

quote:

---

> Anyone as any taught about this?
> MS SQL Server 2000 (sp3a)
> Windows 2000 (sp4)
> --
> C.R.
>

---

I've never heard of something like this before - it'd be great if you could
call product support and help them reproduce this so we can see if there's a
problem with SQL Server or something really really strange about the
database...
-Richard
Richard Waymire, MCSE, MCDBA
This posting is provided "AS IS" with no warranties, and confers no rights.
"C.R." <Cmdr_W_Riker@.HotMail.com> wrote in message
news:e3vpYRDwDHA.1872@.TK2MSFTNGP09.phx.gbl...

quote:

---

> I foud a way to fix this, but it's a long process (this DB is huged), and

---

I

quote:

---

> would still like to know why this hapened. Here is what I did.
> To fix a database (refered as orgDB below) with a role permissions problem
> (won't save), do the following
> a.. Create a new database (refered as tmpDB below)
> b.. Import all tables from orgDB into tmpDB
> c.. in tmpDB, run scrip to add users and set permissions
> d.. Detach both database (right-click on db name, All Tasks, Detach
> database). The DBs will disapear from the list.
> e.. In Windows explorer, rename the original data (mdf) and log (ldf)
> files: orgDB.mfd >> xx_orgDB.mdf and orgDB.lfd >> xx_orgDB.ldf
> f.. In Windows explorer, rename the temporary data (mdf) and log (ldf)
> filesto the original name: tmpDB.mfd >> orgDB.mdf and tmpDB.lfd >>
> orgDB.ldf
> g.. In Enterprise Manager, right-click on Databases / All Tasks / Attach
> Database and follow the wizzard and select the orgDB.mdf file.
> If there is a simplier (shorter) way to do this, I would like to know it.
>
> C.R.
>
> "C.R." <Cmdr_W_Riker@.HotMail.com> wrote in message
> news:eYGmqGBwDHA.1744@.TK2MSFTNGP12.phx.gbl...
Server[QUOTE]
> them
Manager[QUOTE]
back[QUOTE]
> one?
>

---

|||I have noted this too.
We have a SQL 2000 SP3 that is having databases of its own.
We recently upgraded (other) databases from a SQL 7.0 server to this s2k
server.
The problem of not saving the permisssions is happening only on the upgraded
dbs not the ones created in SQL 7.0.
WE have managed to replicate this problem on at least 3 different ugpraded
servers.
Although it seems that databases backed up in sQL 7.0 and restore in SQL
2000 does not have this problem but databases upgraded using the copy db
wizards - all have the problem.
Interestingly, checkint the tables (syspermissions) indicate that the role
does actually have the permissions, but they are not shown on EM or scripted
out of the objects if u script them.
Hope that helps in replication of the problem.
MHJ
"Richard Waymire [MSFT]" <rwaymi@.online.microsoft.com> wrote in message
news:eFRavfEwDHA.2492@.TK2MSFTNGP12.phx.gbl...

quote:

---

> I've never heard of something like this before - it'd be great if you

---

could

quote:

---

> call product support and help them reproduce this so we can see if there's

---

a

quote:

---

> problem with SQL Server or something really really strange about the
> database...
> -Richard
> --
> Richard Waymire, MCSE, MCDBA
> This posting is provided "AS IS" with no warranties, and confers no

---

rights.

quote:

---

> "C.R." <Cmdr_W_Riker@.HotMail.com> wrote in message
> news:e3vpYRDwDHA.1872@.TK2MSFTNGP09.phx.gbl...
and[QUOTE]
> I
problem[QUOTE]
Attach[QUOTE]
it.[QUOTE]
> Server
of[QUOTE]
> Manager
permission,[QUOTE]
> back
it[QUOTE]
>

---