Script to Set User Permissions to Insert and Update Only

Is there a script built into SQL Server that given a specific user and
database will change the user's permission to only insert and update
tables? (I'd like to avoid the task to manually having to do this using
the GUI.)
Alternatively, the user is part of the "Public" role. Is there a script
to change the Public role such that the condition above is true?
Thanks
O.B. wrote:
> Is there a script built into SQL Server that given a specific user and
> database will change the user's permission to only insert and update
> tables? (I'd like to avoid the task to manually having to do this
> using the GUI.)
> Alternatively, the user is part of the "Public" role. Is there a
> script to change the Public role such that the condition above is
> true?
> Thanks
If the group already has rights then revoke them:
Revoke Delete, Select on <table_name> From Public
If the group does not yet have rights, then grant them:
grant insert, update on whatever to public
However, to run some Update queries, you may need to grant Select
rights. For example:
Update dbo.MyTable Set Col1 = 10 -- works without SELECT grant
Update dbo.MyTable Set Col1 = 10 Where Col2 = 5 -- does not work withotu
SELECT grant
David Gugick
Quest Software
www.imceda.com
www.quest.com

Script to Set User Permissions to Insert and Update Only

Is there a script built into SQL Server that given a specific user and
database will change the user's permission to only insert and update
tables? (I'd like to avoid the task to manually having to do this using
the GUI.)
Alternatively, the user is part of the "Public" role. Is there a script
to change the Public role such that the condition above is true?
ThanksO.B. wrote:
> Is there a script built into SQL Server that given a specific user and
> database will change the user's permission to only insert and update
> tables? (I'd like to avoid the task to manually having to do this
> using the GUI.)
> Alternatively, the user is part of the "Public" role. Is there a
> script to change the Public role such that the condition above is
> true?
> Thanks
If the group already has rights then revoke them:
Revoke Delete, Select on <table_name> From Public
If the group does not yet have rights, then grant them:
grant insert, update on whatever to public
However, to run some Update queries, you may need to grant Select
rights. For example:
Update dbo.MyTable Set Col1 = 10 -- works without SELECT grant
Update dbo.MyTable Set Col1 = 10 Where Col2 = 5 -- does not work withotu
SELECT grant
David Gugick
Quest Software
www.imceda.com
www.quest.com

Script to Set User Permissions to Insert and Update Only

Is there a script built into SQL Server that given a specific user and
database will change the user's permission to only insert and update
tables? (I'd like to avoid the task to manually having to do this using
the GUI.)
Alternatively, the user is part of the "Public" role. Is there a script
to change the Public role such that the condition above is true?
ThanksO.B. wrote:
> Is there a script built into SQL Server that given a specific user and
> database will change the user's permission to only insert and update
> tables? (I'd like to avoid the task to manually having to do this
> using the GUI.)
> Alternatively, the user is part of the "Public" role. Is there a
> script to change the Public role such that the condition above is
> true?
> Thanks
If the group already has rights then revoke them:
Revoke Delete, Select on <table_name> From Public
If the group does not yet have rights, then grant them:
grant insert, update on whatever to public
However, to run some Update queries, you may need to grant Select
rights. For example:
Update dbo.MyTable Set Col1 = 10 -- works without SELECT grant
Update dbo.MyTable Set Col1 = 10 Where Col2 = 5 -- does not work withotu
SELECT grant
David Gugick
Quest Software
www.imceda.com
www.quest.com

script to grant permision to role

Hi,
is there any script (qucik way) to grant all Select, Update, Insert,
Delete to a role which i call it "WebAccess"
I would like not to spend too much time creating a role called "WebAccess"
and go to that role and check on every Update, Select, Insert, and Delete fo
r
each object
Thanks
EdEd,
Try adding the role "WebAccess" to the fixed databse roles db_datareader and
db_datawriter.
EXEC sp_addrolemember 'db_datareader', 'WebAccess'
EXEC sp_addrolemember 'db_datawriter', 'WebAccess'
AMB
"Ed" wrote:

> Hi,
> is there any script (qucik way) to grant all Select, Update, Insert,
> Delete to a role which i call it "WebAccess"
> I would like not to spend too much time creating a role called "WebAcces
s"
> and go to that role and check on every Update, Select, Insert, and Delete
for
> each object
> Thanks
> Ed|||Do Datareader and DataWrite have rights to Select, Insert, Update, Delete an
d
Exec the stored procedure and UDF?
One more question, do i need to grant permission all stored procedure
starting with dt_xxxx
Thanks again
Ed
"Alejandro Mesa" wrote:
> Ed,
> Try adding the role "WebAccess" to the fixed databse roles db_datareader a
nd
> db_datawriter.
> EXEC sp_addrolemember 'db_datareader', 'WebAccess'
> EXEC sp_addrolemember 'db_datawriter', 'WebAccess'
>
> AMB
>
> "Ed" wrote:
>|||Ed,

> Do Datareader and DataWrite have rights to Select, Insert, Update, Delete
and
> Exec the stored procedure and UDF?
db_datareader - Can select all data from any user table in the database.
db - datawriter - Can modify any data in any user table in the database.
For udfs and sps you have to grant EXECUTE (sp and udf) and REFERENCES (udf)
.
AMB
"Ed" wrote:
> Do Datareader and DataWrite have rights to Select, Insert, Update, Delete
and
> Exec the stored procedure and UDF?
> One more question, do i need to grant permission all stored procedure
> starting with dt_xxxx
> Thanks again
> Ed
>
> "Alejandro Mesa" wrote:
>|||is that necessary to grant permission to stored procedure starting with dt_x
xxx
Ed
"Alejandro Mesa" wrote:
> Ed,
>
> db_datareader - Can select all data from any user table in the database.
> db - datawriter - Can modify any data in any user table in the database.
> For udfs and sps you have to grant EXECUTE (sp and udf) and REFERENCES (ud
f) .
>
> AMB
> "Ed" wrote:
>|||No.
AMB
"Ed" wrote:
> is that necessary to grant permission to stored procedure starting with dt
_xxxx
> Ed
> "Alejandro Mesa" wrote:
>

Script to generate INSERT statements on table

I'd I have a problem I'd like to post CREATE TABLE and INSERT statements
that will create my table and insert data into the table.

I can use the scripting feature in Enterprise Manager to generate CREATE
TABLE scripts.

Is there a script I can run that will generate INSERT statements so I can
include sample data.

ThanksYou can download a free T-SQL script to do this:

http://vyaskn.tripod.com/code.htm#inserts

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Terri" <Terri@.spamaway.com> wrote in message
news:c10ufs$v54$1@.reader2.nmix.net...
> I'd I have a problem I'd like to post CREATE TABLE and INSERT statements
> that will create my table and insert data into the table.
> I can use the scripting feature in Enterprise Manager to generate CREATE
> TABLE scripts.
> Is there a script I can run that will generate INSERT statements so I can
> include sample data.
> Thanks

Script out whole SQL SERVER Database including INSERT

Hi All,
I am trying to script out a whole SQL SERVER database with all objects
in it along with INSERT Statments to populate data into the table. My
goal is to have one single file which I can just run and produce the
same structure in a new system.
I do not know if there is a way to do that. If there is, I would really
appreciate if any of you can let me know.
Thanks
-- CCDon=B4t know which SQL Server version you are using but for SQL2k5 the
new namespace from SSIS offers something to do that programmatically.
Anyway, if you don=B4t want the tools and want to do that the old
fashioned way, there is a script from vyaskn which produces the
statement from TSQL:
http://vyaskn.tripod.com/code.htm#inserts
HTH, Jens Suessmeyer.

Script Component - Timeout Expired

Hello,

I have a script component inside one of my packages that performs a calculation on the fields and then does several insert statements on SQL 2005 database for each row inputed.

During the execution, when the data is a bit large ~ 3000 records input that produce around 20,000 insert statement, a "Timeout Expired" message pops up several times from the script component. Although the data enters in the database, but I have to manually click "ok" on each of the messages during run-time.
Any idea as to why I am getting this error or how to fix it?
Below is the code of the script component that does the insert.

Thanks for your help.

Grace

Part of Script Component Code:

Try

connMgr = Me.Connections.Connection

conn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)

cmd.Connection = conn

cmd.CommandText = "INSERT statement .... "

If conn.State = ConnectionState.Closed Then conn.Open()

cmd.ExecuteNonQuery()

Catch ex As Exception

MsgBox(ex.Message)

End Try

Why are you doing this inside a script component? This is incredibly inefficient as it issues an INSERT for each of the rows. If you used the data-flow OLE DB Destination it would do set based inserts.

-Jamie

|||

Hi Jamie,

For each row I have an amount, inception date and expiry date. I am using the script component because I have to divide the amount porportionally over each month between the inception - expiry dates and then issue an insert statement for each month with its amount. What I posted in my earlier question was the part that is doing the connection and insert because I thought there might be something wrong in it. So one row triggers multiple inserts depending on dates. I don't know how this is possible using OLE DB Destination.

Thank You,

Grace

|||

How big is your cmd.CommandTimeout? Have you tried to set it to 0 to see whether that helps?

thanks

wenyang

|||

Yes I tried to set it to 0 but still the same error.

The full error message I get: "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occured because all pooled connections were in use and max pool size was reached."

I tried to use conn.clearPool (conn) after the execution but get the message: "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

I tried to close the connection after the ExecuteNonQuery, it seems it worked, no more timeout expired. However, sometimes i get error on the DataFlow Task but when I run it another time, it works fine. I am still testing this case to know what is happening.

Thanks,

Grace

|||

Hi ,

I'm getting the same error in my script component as mentioned in the first post.

the error is "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding"

I tried all the ways mentioned in the post.

can anybody help me in this regard.

Thanks,

vaishu.

|||

vaishali.mspp wrote:

Hi ,

I'm getting the same error in my script component as mentioned in the first post.

the error is "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding"

I tried all the ways mentioned in the post.

can anybody help me in this regard.

Thanks,

vaishu.

Are you trying to insert into a table that is also the target of an OLE DB Destination? If so, your timeout is probably due to a lock.

For the record, I think the OP would be better served sending the new rows to another output of the script and pushing them into an OLE DB Destination, rather than trying to do inserts in the script. It did not sound as if the original scenario was inserting to different tables, which is really the only reason (off the top of my head) to do it in the script.

Script Component - Timeout Expired

Hello,

I have a script component inside one of my packages that performs a calculation on the fields and then does several insert statements on SQL 2005 database for each row inputed.

During the execution, when the data is a bit large ~ 3000 records input that produce around 20,000 insert statement, a "Timeout Expired" message pops up several times from the script component. Although the data enters in the database, but I have to manually click "ok" on each of the messages during run-time.
Any idea as to why I am getting this error or how to fix it?
Below is the code of the script component that does the insert.

Thanks for your help.

Grace

Part of Script Component Code:

Try

connMgr = Me.Connections.Connection

conn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)

cmd.Connection = conn

cmd.CommandText = "INSERT statement .... "

If conn.State = ConnectionState.Closed Then conn.Open()

cmd.ExecuteNonQuery()

Catch ex As Exception

MsgBox(ex.Message)

End Try

Why are you doing this inside a script component? This is incredibly inefficient as it issues an INSERT for each of the rows. If you used the data-flow OLE DB Destination it would do set based inserts.

-Jamie

|||

Hi Jamie,

For each row I have an amount, inception date and expiry date. I am using the script component because I have to divide the amount porportionally over each month between the inception - expiry dates and then issue an insert statement for each month with its amount. What I posted in my earlier question was the part that is doing the connection and insert because I thought there might be something wrong in it. So one row triggers multiple inserts depending on dates. I don't know how this is possible using OLE DB Destination.

Thank You,

Grace

|||

How big is your cmd.CommandTimeout? Have you tried to set it to 0 to see whether that helps?

thanks

wenyang

|||

Yes I tried to set it to 0 but still the same error.

The full error message I get: "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occured because all pooled connections were in use and max pool size was reached."

I tried to use conn.clearPool (conn) after the execution but get the message: "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

I tried to close the connection after the ExecuteNonQuery, it seems it worked, no more timeout expired. However, sometimes i get error on the DataFlow Task but when I run it another time, it works fine. I am still testing this case to know what is happening.

Thanks,

Grace

|||

Hi ,

I'm getting the same error in my script component as mentioned in the first post.

the error is "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding"

I tried all the ways mentioned in the post.

can anybody help me in this regard.

Thanks,

vaishu.

|||

vaishali.mspp wrote:

Hi ,

I'm getting the same error in my script component as mentioned in the first post.

the error is "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding"

I tried all the ways mentioned in the post.

can anybody help me in this regard.

Thanks,

vaishu.

Are you trying to insert into a table that is also the target of an OLE DB Destination? If so, your timeout is probably due to a lock.

For the record, I think the OP would be better served sending the new rows to another output of the script and pushing them into an OLE DB Destination, rather than trying to do inserts in the script. It did not sound as if the original scenario was inserting to different tables, which is really the only reason (off the top of my head) to do it in the script.

SCRIPT

Hi
I am very familiar with insert statements, but how does a
value get passed for a user name and password from either
a web or windows application i.e
A simple insert statement would be
Insert into Table1
(username,password)values('bob','password')
but if you do not know the values of the username and
password are going to be then what would the query look
like.
So if I plan to make my password banana when I log onto
the application how would the script look like then,
would you leave the '' where banana would be as blank.
Insert into Table1
(username,password)values('bob','')
Thanks
Bob
BOB wrote:
> Hi
> I am very familiar with insert statements, but how does a
> value get passed for a user name and password from either
> a web or windows application i.e
> A simple insert statement would be
> Insert into Table1
> (username,password)values('bob','password')
> but if you do not know the values of the username and
> password are going to be then what would the query look
> like.
> So if I plan to make my password banana when I log onto
> the application how would the script look like then,
> would you leave the '' where banana would be as blank.
> Insert into Table1
> (username,password)values('bob','')
>
> Thanks
> Bob
First, we might enquire why you are storing user names and passwords in
a table. Can you rely on SQL Server's security mechanisms instead?
In general, though, is you have an attribute (column) in a table that
may contain unknown data, you allow NULL values. And you insert a NULL
or leave the column out of the insert operation altogether.
For web-based security, it's best to use Windows Authentication. Then no
passwords need to be sent at all. Some web servers may use hard-coded
connection strings to the server, but it's better to use Windows
Authentication.
Can you provide more detail about what you're trying to accomplish?
David Gugick
Imceda Software
www.imceda.com

SCOPE_INDENTITY() Not Returning

Dear All,

My newly insert GUID is not return from a store procedure. I turned this
around and around and can't understand why. The records are inserted but no
GUID is returned.

I use (newid()) in the table to generate a GUID

**************Store Procedure ***********************
CREATE PROCEDURE heasvase.[usp_insert_address]

@.ADDR_NAME_2 [char](70) = NULL,
@.ADDR_NO_3 [char](10) = NULL,
@.ADDR_ROAD_4 [char](50) = NULL,
@.ADDR_DISTRICT_5 [char](50) = NULL,
@.ADDR_TOWN_6 [char](50) = NULL,
@.ADDR_BOROUGH_7 [char](50) = NULL,
@.ADDR_PCODE_8 [char](12) = NULL,
@.addr_id [int] OUTPUT
AS

INSERT INTO [HEAPADLive].[dbo].[TBL_ADDR]
(
[ADDR_NAME],
[ADDR_NO],
[ADDR_ROAD],
[ADDR_DISTRICT],
[ADDR_TOWN],
[ADDR_BOROUGH],
[ADDR_PCODE])

VALUES
(
@.ADDR_NAME_2,
@.ADDR_NO_3,
@.ADDR_ROAD_4,
@.ADDR_DISTRICT_5,
@.ADDR_TOWN_6,
@.ADDR_BOROUGH_7,
@.ADDR_PCODE_8)

SELECT @.addr_id = scope_identity()
GO

***************** ASP ***********************
'Set connection and command properties
set objConn = Server.CreateObject("ADODB.Connection")
set objComm = Server.CreateObject("ADODB.Command")

objConn.Open "Provider=SQLOLEDB.1; Integrated Security=SSPI; Persist
Security Info=False;User ID=iusr_abc; Initial Catalog=Cat_name; Data
Source=SQLSER7"
objComm.ActiveConnection = objConn
objComm.CommandType = adCmdStoredProc
objComm.CommandText = "heasvase.usp_insert_address"

'Set parameters
set value1 = objComm.CreateParameter("@.val1", adChar, adParamInput, 20 ,
val1)
set value2 = objComm.CreateParameter("@.val2", adChar, adParamInput, 20 ,
val2)
set value3 = objComm.CreateParameter("@.val3", adChar, adParamInput, 20 ,
val3)
set value4 = objComm.CreateParameter("@.val4", adChar, adParamInput, 20 ,
val4)
set value5 = objComm.CreateParameter("@.val5", adChar, adParamInput, 20 ,
val5)
set value6 = objComm.CreateParameter("@.val6", adChar, adParamInput, 20 ,
val6)
set value7 = objComm.CreateParameter("@.val7", adChar, adParamInput, 20 ,
val7)
set value8 = objComm.CreateParameter("@.addr_id", adInteger, adParamOutput )

objComm.Parameters.Append(value1)
objComm.Parameters.Append(value2)
objComm.Parameters.Append(value3)
objComm.Parameters.Append(value4)
objComm.Parameters.Append(value5)
objComm.Parameters.Append(value6)
objComm.Parameters.Append(value7)
objComm.Parameters.Append(value8)

'Run Command and tell ADO no records only potput params 'adExecuteNoRecords'
objComm.Execute , , adExecuteNoRecords

newId = objComm.Parameters.Item("@.addr_id")

response.write("Here ->" & newId)

'Cleanup resources
Set objComm = Nothing

Any help would be greatly appreciated...You seem to be confusing two things - uniqueidentifer and identity.
NEWID() generates a new uniqueidentifier value; SCOPE_IDENTITY()
returns the last value generated by an IDENTITY column, which is
usually an integer.

I'm guessing (since you haven't provided a CREATE TABLE statement) that
you're using NEWID() as a default on a column? If so, there is no
function to retrieve the new value - typically you would use NEWID() in
your proc to generate the value, then INSERT it; you can then return
the new value as an output parameter (of data type uniqueidentifier,
not integer).

If this isn't helpful, or my guess is wrong, I suggest you post a
CREATE TABLE script for your table, so that it's clear what data types,
constraints etc you have. You should also clarify what you expect to
get back from the procedure.

Simon|||Hi Simon,

I've taken your advice and changed the insert SP as below but I'm still not
getting my GUID back?

Any thoughts..

CREATE PROCEDURE heasvase.[usp_insert_address]

@.ADDR_NAME_2 [char](70) = NULL,
@.ADDR_NO_3 [char](10) = NULL,
@.ADDR_ROAD_4 [char](50) = NULL,
@.ADDR_DISTRICT_5 [char](50) = NULL,
@.ADDR_TOWN_6 [char](50) = NULL,
@.ADDR_BOROUGH_7 [char](50) = NULL,
@.ADDR_PCODE_8 [char](12) = NULL,
@.addr_id [char] OUTPUT

AS

INSERT INTO [HEAPADLive].[dbo].[TBL_ADDR]
(
[ADDR_ID],
[ADDR_NAME],
[ADDR_NO],
[ADDR_ROAD],
[ADDR_DISTRICT],
[ADDR_TOWN],
[ADDR_BOROUGH],
[ADDR_PCODE])

VALUES
(
NEWID(),
@.ADDR_NAME_2,
@.ADDR_NO_3,
@.ADDR_ROAD_4,
@.ADDR_DISTRICT_5,
@.ADDR_TOWN_6,
@.ADDR_BOROUGH_7,
@.ADDR_PCODE_8)

SET @.addr_id = scope_identity()
GO

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:1126687906.495874.71190@.g44g2000cwa.googlegro ups.com...
> You seem to be confusing two things - uniqueidentifer and identity.
> NEWID() generates a new uniqueidentifier value; SCOPE_IDENTITY()
> returns the last value generated by an IDENTITY column, which is
> usually an integer.
> I'm guessing (since you haven't provided a CREATE TABLE statement) that
> you're using NEWID() as a default on a column? If so, there is no
> function to retrieve the new value - typically you would use NEWID() in
> your proc to generate the value, then INSERT it; you can then return
> the new value as an output parameter (of data type uniqueidentifier,
> not integer).
> If this isn't helpful, or my guess is wrong, I suggest you post a
> CREATE TABLE script for your table, so that it's clear what data types,
> constraints etc you have. You should also clarify what you expect to
> get back from the procedure.
> Simon|||Try this:

CREATE PROCEDURE heasvase.[usp_insert_address]
@.ADDR_NAME_2 [char](70) = NULL,
@.ADDR_NO_3 [char](10) = NULL,
@.ADDR_ROAD_4 [char](50) = NULL,
@.ADDR_DISTRICT_5 [char](50) = NULL,
@.ADDR_TOWN_6 [char](50) = NULL,
@.ADDR_BOROUGH_7 [char](50) = NULL,
@.ADDR_PCODE_8 [char](12) = NULL,
@.addr_id uniqueidentifier OUTPUT
AS

set @.addr_id = newid()

INSERT INTO [HEAPADLive].[dbo].[TBL_ADDR]
(
[ADDR_ID],
[ADDR_NAME],
[ADDR_NO],
[ADDR_ROAD],
[ADDR_DISTRICT],
[ADDR_TOWN],
[ADDR_BOROUGH],
[ADDR_PCODE])

VALUES
(
@.addr_id,
@.ADDR_NAME_2,
@.ADDR_NO_3,
@.ADDR_ROAD_4,
@.ADDR_DISTRICT_5,
@.ADDR_TOWN_6,
@.ADDR_BOROUGH_7,
@.ADDR_PCODE_8)
GO

As per my previous post, SCOPE_IDENTITY() has nothing to do with GUIDs
and NEWID(). An IDENTITY column is an auto-incrementing numeric value,
which is usually implemented as an integer, and SCOPE_IDENTITY()
returns the last identity value generated in the current scope.

NEWID() on the other hand generates a binary GUID value of data type
uniqueidentifier. See "IDENTITY (Property)", SCOPE_IDENTITY(),
uniqueidentifier, NEWID() and CREATE TABLE in Books Online for more
information.

By the way, char with no length defaults to char(1), so your @.addr_id
parameter wouldn't work correctly. You can use CAST() if you want to
return the new GUID as a character type.

Simon|||Hi Simon,

Yes I get it now and this work fine. Thank you for sharing your knowledge
and for your patience..

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:1126690720.625513.316760@.g44g2000cwa.googlegr oups.com...
> Try this:
> CREATE PROCEDURE heasvase.[usp_insert_address]
> @.ADDR_NAME_2 [char](70) = NULL,
> @.ADDR_NO_3 [char](10) = NULL,
> @.ADDR_ROAD_4 [char](50) = NULL,
> @.ADDR_DISTRICT_5 [char](50) = NULL,
> @.ADDR_TOWN_6 [char](50) = NULL,
> @.ADDR_BOROUGH_7 [char](50) = NULL,
> @.ADDR_PCODE_8 [char](12) = NULL,
> @.addr_id uniqueidentifier OUTPUT
> AS
> set @.addr_id = newid()
> INSERT INTO [HEAPADLive].[dbo].[TBL_ADDR]
> (
> [ADDR_ID],
> [ADDR_NAME],
> [ADDR_NO],
> [ADDR_ROAD],
> [ADDR_DISTRICT],
> [ADDR_TOWN],
> [ADDR_BOROUGH],
> [ADDR_PCODE])
> VALUES
> (
> @.addr_id,
> @.ADDR_NAME_2,
> @.ADDR_NO_3,
> @.ADDR_ROAD_4,
> @.ADDR_DISTRICT_5,
> @.ADDR_TOWN_6,
> @.ADDR_BOROUGH_7,
> @.ADDR_PCODE_8)
> GO
> As per my previous post, SCOPE_IDENTITY() has nothing to do with GUIDs
> and NEWID(). An IDENTITY column is an auto-incrementing numeric value,
> which is usually implemented as an integer, and SCOPE_IDENTITY()
> returns the last identity value generated in the current scope.
> NEWID() on the other hand generates a binary GUID value of data type
> uniqueidentifier. See "IDENTITY (Property)", SCOPE_IDENTITY(),
> uniqueidentifier, NEWID() and CREATE TABLE in Books Online for more
> information.
> By the way, char with no length defaults to char(1), so your @.addr_id
> parameter wouldn't work correctly. You can use CAST() if you want to
> return the new GUID as a character type.
> Simon

SCOPE_IDENTITY()??

Hi All,
I have one doubt, if any one can help-me:
I have one SQL(db) that receive a lot of inserts, and I need get IDENTITY
value of current insert, but the machine have 4 processors.
If I use SCOPE_IDENTITY() I will get the corret value?
Thanks.Yes.
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:OoNsEb$0FHA.3956@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I have one doubt, if any one can help-me:
> I have one SQL(db) that receive a lot of inserts, and I need get IDENTITY
> value of current insert, but the machine have 4 processors.
> If I use SCOPE_IDENTITY() I will get the corret value?
> Thanks.
>|||Might want to try IDENT_CURRENT as SCOPE_IDENTITY is scope specific.
HTH
Jerry
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:OoNsEb$0FHA.3956@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I have one doubt, if any one can help-me:
> I have one SQL(db) that receive a lot of inserts, and I need get IDENTITY
> value of current insert, but the machine have 4 processors.
> If I use SCOPE_IDENTITY() I will get the corret value?
> Thanks.
>|||> Might want to try IDENT_CURRENT as SCOPE_IDENTITY is scope specific.
Actually, that is a lot more dangerous. Unless I read it wrong, the OP
wants the IDENTITY value generated by the current insert (SCOPE_IDENTITY),
not the most current IDENTITY value for the table (which may have changed
since the most recent insert in the current scope).
A

SCOPE_IDENTITY() vs. @@IDENTITY

I have a basic C# application that is trying to INSERT a row and get the ID. Really simple; there are no triggers, no stored procs or functions were involved, the app is single threaded, there is currently only one user. I have a really basic table with a INTEGER IDENTITY PK column. All very standard.

If I do the INSERT followed by a "SELECT @.@.IDENTITY" on the same connection, it works correctly.
If I use SCOPE_IDENTITY() instead, it returns NULL. I use SCOPE_IDENTITY on a variety of other occassions and it works fine. Why would this be? I thought SCOPE_IDENTITY() was the preferred replacement to @.@.IDENTITY.

I guess what I have is satisfactory but this was frustrating and I want to know why.

This is on SQL Server 2000 Standard edition with version SP3a + hot fixeshai roger,

SCOPE_IDENTITY and @.@.IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope. @.@.IDENTITY is not limited to a specific scope.

This is the example given in BOL

Suppose if you have two tables, T1 and T2, and an INSERT trigger defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 as a result of the trigger.

Assuming that both T1 and T2 have IDENTITY columns, @.@.IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1.

@.@.IDENTITY will return the last IDENTITY column value inserted across any scope in the current session, which is the value inserted in T2.

SCOPE_IDENTITY() will return the IDENTITY value inserted in T1, which was the last INSERT that occurred in the same scope. The SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.

Hope this relives u of ur frustration|||Yes, I read BOL and understand the documented theoretical and conceptual differences between the two. However, those differences don't apply to my situation.

There is only one thread, one process, one identity value, and one table involved. There are no triggers or functions or stored procs involved. It's a simple INSERT/get ID situation. And @.@.IDENTITY works and SCOPE_IDENTITY doesn't work which just doesn't make any sense according to what I've read.

SCOPE_IDENTITY() SqlCe problem

I unable to do the SCOPE_IDENTITY on a insert query at SQL Mobile.

I need to perform on this query the insert on Client table and to get the ID.

I am programming on Visual Basic on Visual Studio 2005

My code is:

Dim ClientID as Integer

Dim sql As String = "INSERT INTO Client(Number) VALUES('5'); SELECT scope_identity()"

Dim cmd As New SqlCeCommand(sql, connection)

connection.Open()

ClientID = Convert.ToInt32(cmd.ExecuteNonQuery())

connection.Close()

And I get an error like this:

There was an error parsing the query. [ Token line number = 1,Token line offset = 76,Token in error = SELECT ]

Thanks!

You cannot have more queries in the same batch and scope_identity is not supported by SQL CE/Mobile. Use "SELECT @.@.IDENTITY" in a second .ExecuteNonQuery call.

SCOPE_IDENTITY() and "instead of" Triggers.

Here's a fun question :)
If I have an instead of trigger on a table, which replaces the insert, how
can I get the identity insert from the data inserted?
-- example --
IF OBJECT_ID('dbo.tblTest') IS NOT NULL DROP TABLE dbo.tblTest
CREATE TABLE dbo.tblTest ( ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
CLUSTERED , Data1 CHAR(1) NOT NULL )
INSERT INTO dbo.tblTest ( Data1 ) VALUES ( 'A' )
PRINT 'T-SQL: B ->' + CAST( SCOPE_IDENTITY() AS VARCHAR(11)) -- 1
GO
CREATE TRIGGER
TR_dbo_tblTest
ON
dbo.tblTest
INSTEAD OF INSERT
AS
SET NOCOUNT ON
IF ( ( SELECT COUNT(*) FROM inserted ) > 0 )
BEGIN
INSERT INTO
dbo.tblTest ( Data1 )
SELECT
Data1
FROM
inserted
PRINT 'TR_dbo_tblTest ->' + CAST( SCOPE_IDENTITY() AS VARCHAR(11)) -- 2
END
GO
INSERT INTO dbo.tblTest ( Data1 ) VALUES ( 'B' )
-- The following should return 2, but it returns NULL because the insert
was done by the trigger.
PRINT 'T-SQL: B ->' + ISNULL( CAST( SCOPE_IDENTITY() AS VARCHAR(11)) ,
'<NULL>' ) -- NULLI could be wrong, but are trying to get the ID for the record inserted?
If you are... When I use Int Identity fields for the primary key, I use a SP
to insert my records when I need to know what the primary key is.
For example, this SP is used to insert a new record into the Contacts table.
It returns a result set with a field called NewID that contains the ID for
the new record.
Create Procedure [dbo].[NewContactRec_SP]
@.LName VARCHAR(30),
@.FName VARCHAR(20),
@.ResID Int,
@.StaffID Int
as
Insert Into Contacts
(LName,FName,ResID,CreatedStaffID)
Values (@.LName,@.Fname,@.ResID,@.StaffID)
/* Return New ID */
Select SCOPE_IDENTITY() As NewID
HTH,
-Steve-|||For INSTEAD OF triggers, use the ol' @.@.IDENTITY instead:
INSERT INTO dbo.tblTest ( Data1 ) VALUES ( 'B' )
PRINT 'T-SQL: B ->' + ISNULL( CAST( @.@.IDENTITY AS VARCHAR(11)) ,'<NULL>' )
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:43551654$0$135$7b0f0fd3@.mistral.news.newnet.co.uk...
> Here's a fun question :)
> If I have an instead of trigger on a table, which replaces the insert, how
> can I get the identity insert from the data inserted?
>
> -- example --
> IF OBJECT_ID('dbo.tblTest') IS NOT NULL DROP TABLE dbo.tblTest
> CREATE TABLE dbo.tblTest ( ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
> CLUSTERED , Data1 CHAR(1) NOT NULL )
> INSERT INTO dbo.tblTest ( Data1 ) VALUES ( 'A' )
> PRINT 'T-SQL: B ->' + CAST( SCOPE_IDENTITY() AS VARCHAR(11)) -- 1
>
> GO
> CREATE TRIGGER
> TR_dbo_tblTest
> ON
> dbo.tblTest
> INSTEAD OF INSERT
> AS
> SET NOCOUNT ON
> IF ( ( SELECT COUNT(*) FROM inserted ) > 0 )
> BEGIN
> INSERT INTO
> dbo.tblTest ( Data1 )
> SELECT
> Data1
> FROM
> inserted
> PRINT 'TR_dbo_tblTest ->' + CAST( SCOPE_IDENTITY() AS VARCHAR(11)) -- 2
> END
> GO
>
> INSERT INTO dbo.tblTest ( Data1 ) VALUES ( 'B' )
> -- The following should return 2, but it returns NULL because the insert
> was done by the trigger.
> PRINT 'T-SQL: B ->' + ISNULL( CAST( SCOPE_IDENTITY() AS VARCHAR(11)) ,
> '<NULL>' ) -- NULL
>|||We have a ton of auditing code inside the trigger and can't change it to a
stored proc, mainly because people can still edit the data in SQL-EM and
these changes still need to be audited.
"Steve Zimmelman" <skz@.charter.nospam.net> wrote in message
news:eEstw$$0FHA.3660@.TK2MSFTNGP15.phx.gbl...
> I could be wrong, but are trying to get the ID for the record inserted?
> If you are... When I use Int Identity fields for the primary key, I use a
SP
> to insert my records when I need to know what the primary key is.
> For example, this SP is used to insert a new record into the Contacts
table.
> It returns a result set with a field called NewID that contains the ID for
> the new record.
> Create Procedure [dbo].[NewContactRec_SP]
> @.LName VARCHAR(30),
> @.FName VARCHAR(20),
> @.ResID Int,
> @.StaffID Int
> as
> Insert Into Contacts
> (LName,FName,ResID,CreatedStaffID)
> Values (@.LName,@.Fname,@.ResID,@.StaffID)
> /* Return New ID */
> Select SCOPE_IDENTITY() As NewID
> HTH,
> -Steve-
>|||Ok,
But that requires an exclusive table lock to stop people putting in more
data :)
I tried SET TRANSACTION ISOLATION LEVEL SERIALIZABLE,
but this still allowed multiple transactions to insert data
Good 'ol WITH(TABLOCKX) :)
Unless there's another way to block inserts, without blocking everything
else?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uUnggGA1FHA.268@.TK2MSFTNGP09.phx.gbl...
> For INSTEAD OF triggers, use the ol' @.@.IDENTITY instead:
> INSERT INTO dbo.tblTest ( Data1 ) VALUES ( 'B' )
> PRINT 'T-SQL: B ->' + ISNULL( CAST( @.@.IDENTITY AS VARCHAR(11)) ,'<NULL>' )
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
> news:43551654$0$135$7b0f0fd3@.mistral.news.newnet.co.uk...
how
insert
>|||> But that requires an exclusive table lock to stop people putting in more
> data :)
Hmm, not sure I understand. Why are you saying that?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:43551dbd$0$142$7b0f0fd3@.mistral.news.newnet.co.uk...
> Ok,
> But that requires an exclusive table lock to stop people putting in more
> data :)
> I tried SET TRANSACTION ISOLATION LEVEL SERIALIZABLE,
> but this still allowed multiple transactions to insert data
> Good 'ol WITH(TABLOCKX) :)
> Unless there's another way to block inserts, without blocking everything
> else?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:uUnggGA1FHA.268@.TK2MSFTNGP09.phx.gbl...
> how
> insert
>|||Yeah, this stinks. Like Tibor says, use @.@.identity if you can (it does not
require locks, it is scoped to a single session.
The best thing to do is to use your other key (you should have one because
the surrogate key (the identity value) should be a surrogate for something,
otherwise you have a potential mess) to fetch the value:
insert into dbo.tblTest --probably stop prefixing tables with tbl too :)
values...
select ID -- generally better to name <tablename>Id so they are easier to
implement/use as foreign keys
from tblTest
where keycolumn<s> = @.valueYouEntered
If you want it changed in the future, please go to:
http://lab.msdn.microsoft.com/produ...1b29351&lc=1033
And vote for this!
Thanks!
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:43551654$0$135$7b0f0fd3@.mistral.news.newnet.co.uk...
> Here's a fun question :)
> If I have an instead of trigger on a table, which replaces the insert, how
> can I get the identity insert from the data inserted?
>
> -- example --
> IF OBJECT_ID('dbo.tblTest') IS NOT NULL DROP TABLE dbo.tblTest
> CREATE TABLE dbo.tblTest ( ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
> CLUSTERED , Data1 CHAR(1) NOT NULL )
> INSERT INTO dbo.tblTest ( Data1 ) VALUES ( 'A' )
> PRINT 'T-SQL: B ->' + CAST( SCOPE_IDENTITY() AS VARCHAR(11)) -- 1
>
> GO
> CREATE TRIGGER
> TR_dbo_tblTest
> ON
> dbo.tblTest
> INSTEAD OF INSERT
> AS
> SET NOCOUNT ON
> IF ( ( SELECT COUNT(*) FROM inserted ) > 0 )
> BEGIN
> INSERT INTO
> dbo.tblTest ( Data1 )
> SELECT
> Data1
> FROM
> inserted
> PRINT 'TR_dbo_tblTest ->' + CAST( SCOPE_IDENTITY() AS VARCHAR(11)) -- 2
> END
> GO
>
> INSERT INTO dbo.tblTest ( Data1 ) VALUES ( 'B' )
> -- The following should return 2, but it returns NULL because the insert
> was done by the trigger.
> PRINT 'T-SQL: B ->' + ISNULL( CAST( SCOPE_IDENTITY() AS VARCHAR(11)) ,
> '<NULL>' ) -- NULL
>|||> Yeah, this stinks. Like Tibor says, use @.@.identity if you can (it does
> not require locks, it is scoped to a single session.
Oh yeah, the reason why you might not be able to use this would be if a
trigger inserted data into another table.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:OyyqtiB1FHA.2884@.TK2MSFTNGP09.phx.gbl...
> Yeah, this stinks. Like Tibor says, use @.@.identity if you can (it does
> not require locks, it is scoped to a single session.
> The best thing to do is to use your other key (you should have one because
> the surrogate key (the identity value) should be a surrogate for
> something, otherwise you have a potential mess) to fetch the value:
> insert into dbo.tblTest --probably stop prefixing tables with tbl too :)
> values...
> select ID -- generally better to name <tablename>Id so they are easier to
> implement/use as foreign keys
> from tblTest
> where keycolumn<s> = @.valueYouEntered
> If you want it changed in the future, please go to:
> http://lab.msdn.microsoft.com/produ...1b29351&lc=1033
> And vote for this!
> Thanks!
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing." (Oscar Wilde)
> "Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
> news:43551654$0$135$7b0f0fd3@.mistral.news.newnet.co.uk...
>|||Yeah I just remembered,
The auditing tables also have an identity field :)
fun \o/
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:e4ckAmB1FHA.3956@.TK2MSFTNGP09.phx.gbl...
> Oh yeah, the reason why you might not be able to use this would be if a
> trigger inserted data into another table.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
convincing."
> (Oscar Wilde)
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:OyyqtiB1FHA.2884@.TK2MSFTNGP09.phx.gbl...
because
to
http://lab.msdn.microsoft.com/produ...1b29351&lc=1033
> ----
--
2
insert
>|||Yes you're right of course it's session based,
I've been playing with mysql too much at the wend :o
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:OyyqtiB1FHA.2884@.TK2MSFTNGP09.phx.gbl...
> Yeah, this stinks. Like Tibor says, use @.@.identity if you can (it does
not
> require locks, it is scoped to a single session.

scope_identity()

I have this foreign function

Code Snippet

create function fillmuon1(jdbc ds)->boolean
as for each muon m
sqlu(ds," Insert into particle (id,eventid,px,py,pz,kf,ee) VALUES (" +
itoa(id(m)) + "," + itoa(id(event(m))) + "," + itoa(px(m)) + "," +
itoa(py(m)) + "," + itoa(pz(m)) + "," + itoa(kf(m)) + "," + itoa(Ee(m)) + ");
Insert into leptonaux(id) VALUES (SCOPE_IDENTITY());

Insert into muonaux(id) VALUES (SCOPE_IDENTITY());");

fillmuon1(:ds);

My problem when i call scope_identity() for the first time works, but when i call it the second time is trying to add a null value, so what can I do to keep the value

Yes. The idenity values will be reset when you call INSERT statement.

Change the code as follow as,

sqlu(ds," Insert into particle (id,eventid,px,py,pz,kf,ee) VALUES (" +
itoa(id(m)) + "," + itoa(id(event(m))) + "," + itoa(px(m)) + "," +
itoa(py(m)) + "," + itoa(pz(m)) + "," + itoa(kf(m)) + "," + itoa(Ee(m)) + ");
Declare @.ID as int; Set @.ID=SCOPE_IDENTITY(); Insert into leptonaux(id) VALUES (@.ID);

Insert into muonaux(id) VALUES (@.ID);");

scope_identity()

I have an ASP front end on SQL 2000 database. I have a form that submits to
an insert query. The entry field is an "identity" and the primary key. I
have used scope_identity() to display the entry# of the record just entered
on the confirmation page. Now I need to insert the entry into another
table. This is my query:

SET NOCOUNT ON
INSERT wo_main
(site_id, customer, po_number)
VALUES ('::site_id::', '::customer::', '::po_number::')
SELECT scope_identity() AS entry
INSERT INTO wo_combo_body
(entry) VALUES ('::entry::')
SET nocount off

This query displays the entry number of the record just entered, but inserts
a 0 in to entry field of the 2nd table. Any help would be great.

Thanks,
Darren>SELECT scope_identity() AS entry
does not assign the identity to a variable named entry, it just
returns a recordset like any other select

either
declare @.Entry int
set @.Entry = (select scope_identity())
insert into table (field) values (@.Entry)

or
insert int table (field) values (select scope_identity())

also move the set nocount off to the top

On Fri, 20 Feb 2004 19:40:54 GMT, "Scrappy"
<celtics@.lan-specialist.com> wrote:

>I have an ASP front end on SQL 2000 database. I have a form that submits to
>an insert query. The entry field is an "identity" and the primary key. I
>have used scope_identity() to display the entry# of the record just entered
>on the confirmation page. Now I need to insert the entry into another
>table. This is my query:
>SET NOCOUNT ON
>INSERT wo_main
> (site_id, customer, po_number)
>VALUES ('::site_id::', '::customer::', '::po_number::')
>SELECT scope_identity() AS entry
>INSERT INTO wo_combo_body
>(entry) VALUES ('::entry::')
>SET nocount off
>This query displays the entry number of the record just entered, but inserts
>a 0 in to entry field of the 2nd table. Any help would be great.
>Thanks,
>Darren|||Hi

If you can use a local variable to hold what is returned by SCOPE_IDENTITY.
This variable can then be used in the second insert statement. You may also
want to add some error checking! Rather than returning a result set it may
also be better(faster) to return the value as a parameter

John

"Scrappy" <celtics@.lan-specialist.com> wrote in message
news:aptZb.32366$um1.10431@.twister.nyroc.rr.com...
> I have an ASP front end on SQL 2000 database. I have a form that submits
to
> an insert query. The entry field is an "identity" and the primary key. I
> have used scope_identity() to display the entry# of the record just
entered
> on the confirmation page. Now I need to insert the entry into another
> table. This is my query:
> SET NOCOUNT ON
> INSERT wo_main
> (site_id, customer, po_number)
> VALUES ('::site_id::', '::customer::', '::po_number::')
> SELECT scope_identity() AS entry
> INSERT INTO wo_combo_body
> (entry) VALUES ('::entry::')
> SET nocount off
> This query displays the entry number of the record just entered, but
inserts
> a 0 in to entry field of the 2nd table. Any help would be great.
> Thanks,
> Darren|||Thanks! I used a trigger to accomplish this. I am new to SQL. Are there
any pitfalls with doing it with a trigger?

Also...

On the same confirmation page I want to diplay links to a page for each
table. Basically I need to select the entry field from each table that I
have inserted to with the trigger. I can then use this as a hyperlink to
the each page. Any ideas on this?

"Bruce Loving" <BRUCE@.LOVINGSCENTS.COM> wrote in message
news:tbtc30lq99c2h3lvjgrh93nt9hmqk1hisc@.4ax.com...
> >SELECT scope_identity() AS entry
> does not assign the identity to a variable named entry, it just
> returns a recordset like any other select
> either
> declare @.Entry int
> set @.Entry = (select scope_identity())
> insert into table (field) values (@.Entry)
> or
> insert int table (field) values (select scope_identity())
> also move the set nocount off to the top
> On Fri, 20 Feb 2004 19:40:54 GMT, "Scrappy"
> <celtics@.lan-specialist.com> wrote:
> >I have an ASP front end on SQL 2000 database. I have a form that submits
to
> >an insert query. The entry field is an "identity" and the primary key.
I
> >have used scope_identity() to display the entry# of the record just
entered
> >on the confirmation page. Now I need to insert the entry into another
> >table. This is my query:
> >SET NOCOUNT ON
> >INSERT wo_main
> > (site_id, customer, po_number)
> >VALUES ('::site_id::', '::customer::', '::po_number::')
> >SELECT scope_identity() AS entry
> >INSERT INTO wo_combo_body
> >(entry) VALUES ('::entry::')
> >SET nocount off
> >This query displays the entry number of the record just entered, but
inserts
> >a 0 in to entry field of the 2nd table. Any help would be great.
> >Thanks,
> >Darren|||Hi

You have very little scope to insert new records in a secondary table if you
use a trigger, as you can not pass parameters to it. If there is only one
column in the second table it should be redundant. If there are other
columns then you should write a stored procedure and use a transaction to
maintain consistency see books online :
BEGIN TRANSACTION:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_ba-bz_96zy.htm
ROLLBACK TRANSACTION:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_ra-rz_471q.htm
COMMIT TRANSACTION:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_ca-co_7w6m.htm

You should be able to identify the values inserted in a session by including
identifying data in the table such as Username, Datatime, SessionId etc.

John

"Scrappy" <celtics@.lan-specialist.com> wrote in message
news:bnxZb.71491$n62.1826@.twister.nyroc.rr.com...
> Thanks! I used a trigger to accomplish this. I am new to SQL. Are there
> any pitfalls with doing it with a trigger?
> Also...
> On the same confirmation page I want to diplay links to a page for each
> table. Basically I need to select the entry field from each table that I
> have inserted to with the trigger. I can then use this as a hyperlink to
> the each page. Any ideas on this?
>
> "Bruce Loving" <BRUCE@.LOVINGSCENTS.COM> wrote in message
> news:tbtc30lq99c2h3lvjgrh93nt9hmqk1hisc@.4ax.com...
> > >SELECT scope_identity() AS entry
> > does not assign the identity to a variable named entry, it just
> > returns a recordset like any other select
> > either
> > declare @.Entry int
> > set @.Entry = (select scope_identity())
> > insert into table (field) values (@.Entry)
> > or
> > insert int table (field) values (select scope_identity())
> > also move the set nocount off to the top
> > On Fri, 20 Feb 2004 19:40:54 GMT, "Scrappy"
> > <celtics@.lan-specialist.com> wrote:
> > >I have an ASP front end on SQL 2000 database. I have a form that
submits
> to
> > >an insert query. The entry field is an "identity" and the primary key.
> I
> > >have used scope_identity() to display the entry# of the record just
> entered
> > >on the confirmation page. Now I need to insert the entry into another
> > >table. This is my query:
> > > >SET NOCOUNT ON
> > >INSERT wo_main
> > > (site_id, customer, po_number)
> > >VALUES ('::site_id::', '::customer::', '::po_number::')
> > >SELECT scope_identity() AS entry
> > >INSERT INTO wo_combo_body
> > >(entry) VALUES ('::entry::')
> > >SET nocount off
> > > >This query displays the entry number of the record just entered, but
> inserts
> > >a 0 in to entry field of the 2nd table. Any help would be great.
> > > >Thanks,
> > >Darren
>

SCOPE_IDENTITY()

Trying to insert record in Parent table and return value of that rows IDENTITY, to pass back to app for use inserting child rows into other table. Looks clean against tutorial samples(http://aspnet.4guysfromrolla.com/demos/printPage.aspx?path=/articles/050207-1.aspx). Works with Insert until *** lines are added - HELP?

ERROR:

Msg 201, Level 16, State 4, Procedure qc_submitInsertQCparentReturningID_2, Line 0

Procedure or function 'qc_submitInsertQCparentReturningID_2' expects parameter '@.newQCparent_ID', which was not supplied.

Based on this Procedure:

ALTER PROCEDURE qc_submitInsertQCparentReturningID_2

(@.newQCparent_ID INT OUTPUT) ***

AS

-- Insert New QCparent RETURNING qcParentID

INSERT INTO app.dbo.a1_qcParent

([rowCreatedBy]

,[rowNotes]

,[rowLastAction]

,[rowLastActionBy]

,[rowLastActionNote])

-- Using Parameter Variables

VALUES('Anonymous Submit'

,'By qc_submitInsertQCparentReturningID'

,'Insert'

,'Guest'

,'show donald')

-- Read the qcParient_ID back for Items2fix Insert

SET @.newQCparent_ID = SCOPE_IDENTITY() ***

- Try New SPROC

EXEC qc_submitInsertQCparentReturningID_2

Thanks, unfortunately neither worked...

snippet 1 Error (EXEC qc_submitInsertQCparentReturningID_2 @.newQCparent_ID=@.newQCparent_ID):

Msg 137, Level 15, State 2, Line 1

Must declare the scalar variable "@.newQCparent_ID".

snippet 2 Error (EXEC qc_submitInsertQCparentReturningID_2 @.newQCparent_ID=scope_identity())

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ')'.

|||

Sorry, I got it wrong; Leave your original procedure code as it was. Change your call of the stored procedure from

Code Snippet

EXEC qc_submitInsertQCparentReturningID_2

to

Code Snippet

declare @.outputParm integer

EXEC qc_submitInsertQCparentReturningID_2 @.newQCparent_ID=@.outputParm output

and if you wish to see the results something like:

Code Snippet

declare @.outputParm integer

EXEC qc_submitInsertQCparentReturningID_2 @.newQCparent_ID=@.outputParm output

select @.outputParm as [@.outputParm]

|||

Parameters are by default NULLable in SQL Server. But in case of OUTPUT parameters, you will have to always pass a value. So if you want to ignore the output value you can do:

exec qc_submitInsertQCparentReturningID_2 NULL;

If you need to retrieve the output value then do:

declare @.id int;

exec qc_submitInsertQCparentReturningID_2 @.id OUTPUT;

|||

THat allowed it to run, insert was successful, but the following resulted in 9 as scope_Identity but 14 rows listed:

- Try New SPROC

declare @.id int;

exec qc_submitInsertQCparentReturningID_4 @.id OUTPUT;

go

Select SCOPE_IDENTITY()

go

Select * from a1_qcParent

a) I was expecting to see the same scope as the last inserted rows ID (14) ?

and

b) can I use the @.id to populate another procedure insert ?

|||Removed the GO between statements, but Scope_Identity remained 9 while rows increased|||

In the statement:

Code Snippet

declare @.id int;

exec qc_submitInsertQCparentReturningID_4 @.id OUTPUT;

go

Select SCOPE_IDENTITY()

go

Select * from a1_qcParent

The SCOPE_IDENTITY() function will not work as you are thinking -- because the IDENTITY column is assigned inside of the stored procedure and what happened there is NOT in the scope of the CALLING query. As I said above, you will need to do something like

Code Snippet

declare @.id int;

exec qc_submitInsertQCparentReturningID_4 @.id OUTPUT;

Select @.id

Select * from a1_qcParent

SCOPE_IDENTITY Problem I dont know how to use it.

Hi,

I am using following code to insert some record in to database and after that i want the id of new added record , so what kind of change i have to with my code plz anyone can do some change my code as it return id by using SCOPE_IDENTITY in my code

--------------------
Dim strconn As String = "server=xxx.xxx.xx; initial catalog=xxx;uid=xxx;pwd=xxx"
'Create a connection
Dim MyConn_member As New SqlConnection(strconn)
MyConn_member.Open()

'Start the transaction
Dim myTrans As SqlTransaction = MyConn_member.BeginTransaction()

Try
'Specify the first statement to run...
Dim MySQL_member As String = "Insert Into article ([articleCategoryId],[articleTitle],[articleDescription],[articleContent],[articlePostBy],[articleStatus],[addDate],[lastUpdate]) Values (@.category_id, @.article_title, @.article_description,@.article_content,@.article_postby,@.article_status,@.add_date, @.last_update)"

'Create the SqlCommand object, specifying the transaction through
Dim cmd_member As New SqlCommand(MySQL_member, MyConn_member, myTrans)
cmd_member.Parameters.Add(New SqlParameter("@.category_id", article_category.SelectedValue))
cmd_member.Parameters.Add(New SqlParameter("@.article_title", article_title.Text))
cmd_member.Parameters.Add(New SqlParameter("@.article_description", article_description.Text))
cmd_member.Parameters.Add(New SqlParameter("@.article_content", article_content.Text))
cmd_member.Parameters.Add(New SqlParameter("@.article_postby", post_by))
cmd_member.Parameters.Add(New SqlParameter("@.article_status", article_status))
cmd_member.Parameters.Add(New SqlParameter("@.add_date", last_update))
cmd_member.Parameters.Add(New SqlParameter("@.last_update", last_update))

cmd_member.ExecuteNonQuery()
myTrans.Commit()

Catch ex As Exception
'Something went wrong, so rollback the transaction
myTrans.Rollback()
MyConn_member.Close()
Throw 'Bubble up the exception
Finally
'Finally, close the connection
MyConn_member.Close()
End Try

--------------------

PLease help me
Thanks in advance

Create an additional parameter, set the parameter direction to Output.

Append this to the end of your SQL statement:

SELECT <Your Parameter Name> = SCOPE_IDENTITY;

Then read the vale of the output parameter.

|||Sorry , But I really don't understand where and how to append it

Can you please do for me...

Very Very Thanks in Advance|||

Kunal Mehta:

Sorry , But I really don't understand where and how to append it

Can you please do for me...

We're not here to do your work for you. The previous poster has demonstrated what you need to do. If you have a question that is more specific to an issue you are having vs. please do my work for me, then feel free to resubmit your question.

|||

I would be very happy to do it for you. Please send me a contract for employment. I will charge you $200.00 US per hour, minimum of 1 hour.

Maybe you could send me your client's contact information and I will do the whole project for you?

Scope_Identity and SqlDataSource

have a detailsView control with an SqlDataSource whose insert statement looks like this:

InsertCommand

="INSERT INTO [tblCompaniesNewSetRaw] ([NAME], [CITY], [ST], [ZIPCODE], [NAICS], [NAICSDESCRIPTION]) VALUES (@.NAME, @.CITY, @.ST, @.ZIPCODE, @.NAICS, @.NAICSDESCRIPTION); SELECT RETURN_VALUE = SCOPE_IDENTITY()"

also played with the same insert but used ...;Select SCOPE_IDENTITY()

my question is how do i get the last record inserted into tblCompaniesNewSetRaw after the insert is run. ie I read that the Select Scope_identity() would return the value but how do i access the return value from within the code behind page, iusing VB.

some things i tried in the detailsView_ItemInserted(...

Dim

rowAs DetailsViewRowForEach rowIn DetailsView3.Rows

x = row.Cells.Item(0).Text

Next

in the VS debugger x is just "" and not the last record inserted in that table.

probably way off base on this, clues appreciated, tc

I have never done it but i think what you will want to do is add a

InsertCommand="INSERT INTO [tblCompaniesNewSetRaw] ([NAME], [CITY], [ST], [ZIPCODE], [NAICS], [NAICSDESCRIPTION]) VALUES (@.NAME, @.CITY, @.ST, @.ZIPCODE, @.NAICS, @.NAICSDESCRIPTION); SELECT @.RETURN_VALUE = SCOPE_IDENTITY()"

<

asp:ParameterDirection="ReturnValue"Name="RETURN_VALUE"Type="Int16"/>

And then in the

ProtectedSub SqlDataSource1_Inserted(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)Handles SqlDataSource1.InsertedDim newIdAsObject = e.Command.Parameters("@.ReturnValue").ValueEndSub|||

sorry this

Dim newIdAsObject = e.Command.Parameters("@.ReturnValue").Value

should be this

Dim newIdAsObject = e.Command.Parameters("@.Return_Value").Value

|||

thanks rojay12

u gave me the clue i needed,

ended up using

Dim newIdAsObject = e.Command.Parameters("@.RETURN_VALUE").Value

<asp:ParameterName="RETURN_VALUE"Direction="Output"Type="Int32"/>

InsertCommand

="INSERT INTO [tblCompaniesNewSetRaw] ([NAME], [CITY], [ST], [ZIPCODE], [NAICS], [NAICSDESCRIPTION]) VALUES (@.NAME, @.CITY, @.ST, @.ZIPCODE, @.NAICS, @.NAICSDESCRIPTION); SELECT @.RETURN_VALUE = SCOPE_IDENTITY()

seems to be working, now got to go back and sync up some gridviews and the detailview afte the insert, much obliged, tc