Retrieving NEWSEQUENTIALID

Hi,
We are thinking of changing the primary key of our table from GUID type to
squential id using the new NEWSEQUENTIALID function. But the issue we are
having is how to efficiently retrieve the generated value because we need to
pass this information back to the client app. With GUID we have a stored
procedure that calls NEWID and then we assign the generated value to the
primary key column. But with NEWSEQUENTIALID it can only be used as DEFAULT
constraint. We came up with a solution to retreive the generated id but the
performance is very slow.
Has anyone come up with a better strategy in doing this?
Thanks.Roy
Read this article in the BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e06d2cab-f1ff-42f1-8550-
6aaec57be36f.htm
I think , as the BOL suggests to use a scalar UDF to rertieve the value
"Roy" <Roy@.discussions.microsoft.com> wrote in message
news:E88F0D41-C460-4E71-932C-56CB5C5AA24F@.microsoft.com...
> Hi,
> We are thinking of changing the primary key of our table from GUID type to
> squential id using the new NEWSEQUENTIALID function. But the issue we are
> having is how to efficiently retrieve the generated value because we need
> to
> pass this information back to the client app. With GUID we have a stored
> procedure that calls NEWID and then we assign the generated value to the
> primary key column. But with NEWSEQUENTIALID it can only be used as
> DEFAULT
> constraint. We came up with a solution to retreive the generated id but
> the
> performance is very slow.
> Has anyone come up with a better strategy in doing this?
> Thanks.|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O%23wYzjzmGHA.1852@.TK2MSFTNGP03.phx.gbl...
> I think , as the BOL suggests to use a scalar UDF to rertieve the value
Uri,
I'm not sure if we have a different rev of BOL (I'm looking at the
December version), but mine seems to indicate that you CANNOT use a UDF on
NEWSEQUENTIALID... The best option, IMO, is to use an OUTPUT clause:
CREATE TABLE x1
(
col UNIQUEIDENTIFIER DEFAULT(NEWSEQUENTIALID())
)
GO
INSERT x1
OUTPUT inserted.col
DEFAULT VALUES
GO
DROP TABLE x1
GO
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--

>
> "Roy" <Roy@.discussions.microsoft.com> wrote in message
> news:E88F0D41-C460-4E71-932C-56CB5C5AA24F@.microsoft.com...
>|||DOH!!!! Thanks Adam. I did not read it properly , ( I need to freshen up
myself in the morning)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OUOE$tzmGHA.3440@.TK2MSFTNGP03.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O%23wYzjzmGHA.1852@.TK2MSFTNGP03.phx.gbl...
> Uri,
> I'm not sure if we have a different rev of BOL (I'm looking at the
> December version), but mine seems to indicate that you CANNOT use a UDF on
> NEWSEQUENTIALID... The best option, IMO, is to use an OUTPUT clause:
> --
> CREATE TABLE x1
> (
> col UNIQUEIDENTIFIER DEFAULT(NEWSEQUENTIALID())
> )
> GO
> INSERT x1
> OUTPUT inserted.col
> DEFAULT VALUES
> GO
> DROP TABLE x1
> GO
> --
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>|||Another approach is to assign the uniqueidentifier value in application code
rather than in SQL Server. That way, you don't need to retrieve the
assigned value at all because you already know it. To get a sequential GUID
value in application code, call the UuidCreateSequential RPC function.
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy" <Roy@.discussions.microsoft.com> wrote in message
news:E88F0D41-C460-4E71-932C-56CB5C5AA24F@.microsoft.com...
> Hi,
> We are thinking of changing the primary key of our table from GUID type to
> squential id using the new NEWSEQUENTIALID function. But the issue we are
> having is how to efficiently retrieve the generated value because we need
> to
> pass this information back to the client app. With GUID we have a stored
> procedure that calls NEWID and then we assign the generated value to the
> primary key column. But with NEWSEQUENTIALID it can only be used as
> DEFAULT
> constraint. We came up with a solution to retreive the generated id but
> the
> performance is very slow.
> Has anyone come up with a better strategy in doing this?
> Thanks.|||Thanks the response.
However, this is the solution we tried before but we saw a performance
degradation (during lots of inserts) compare to the old way of using GUIDs.
Roy
"Adam Machanic" wrote:

> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O%23wYzjzmGHA.1852@.TK2MSFTNGP03.phx.gbl...
> Uri,
> I'm not sure if we have a different rev of BOL (I'm looking at the
> December version), but mine seems to indicate that you CANNOT use a UDF on
> NEWSEQUENTIALID... The best option, IMO, is to use an OUTPUT clause:
> --
> CREATE TABLE x1
> (
> col UNIQUEIDENTIFIER DEFAULT(NEWSEQUENTIALID())
> )
> GO
> INSERT x1
> OUTPUT inserted.col
> DEFAULT VALUES
> GO
> DROP TABLE x1
> GO
> --
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>
>|||Thanks Dan.
We haven't tried this approach yet. We will it give it a try.
Roy
"Dan Guzman" wrote:

> Another approach is to assign the uniqueidentifier value in application co
de
> rather than in SQL Server. That way, you don't need to retrieve the
> assigned value at all because you already know it. To get a sequential GU
ID
> value in application code, call the UuidCreateSequential RPC function.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Roy" <Roy@.discussions.microsoft.com> wrote in message
> news:E88F0D41-C460-4E71-932C-56CB5C5AA24F@.microsoft.com...
>
>

Retrieving GUID from INSERT query -- HELP

I have tried many code sample but I am very stuck

The primary key of my database (SQL server 2005) table is a uniqueidentifier.

I am using the following code to insert a row into my table:

myCommand.CommandText = sqlEvent.ToString(); //add the sql query to the command
myCommand.Connection = this.dbConnection; //add the database connection to the command
myCommand.ExecuteNonQuery(); //execute the insert query

I need to retrieve the GUID that is automatically generated when the insert command is executed.

Can someone help me? How do I get the GUID that is automatically generated? I have tried lots of things like using

string _id = (string)myCommand.ExecuteScalar();

and I am still stuck. I will really appreciate it if someone can refer me to some code sample.

HELP

Here is a sample:

CREATE TABLE T1(
id uniqueidentifier PRIMARY KEY DEFAULT(NEWID()),
name nvarchar(100)
);

INSERT INTO T1 (name) OUTPUT inserted.id VALUES('name 1');

SELECT * from T1;

So you just need to call T-SQL similar to "INSERT INTO T1 (name) OUTPUT inserted.id VALUES('name 1');" with ExecuteScalar();

Thanks,

Zuomin

retrieve the GUID for inserted record

I am using this code to insert a record in my table where i have assigned a guid datatype field to generate an automatic guid for each record. but now i need to retrieve the guid to use it to send a confirmation email to the user.

SqlConnection sql_connection = new SqlConnection("Server=xxx.xxx.xx.xxx;uid=xxxxxxxx;password=xxxxxxx;database=xxxxxxx;");
SqlCommand sql_command = new SqlCommand("INSERT INTO members (member_sex, member_cpr, member_nationality, member_block, member_gov, member_daaera, member_email, member_mobile, member_created_ip) Values (@.member_sex, @.member_cpr, @.member_nationality, @.member_block, @.member_gov, @.member_daaera, @.member_email, @.member_mobile, @.member_created_ip)", sql_connection);

sql_command.Parameters.Add(new SqlParameter("@.member_sex", Session["member_sex"].ToString()));
sql_command.Parameters.Add(new SqlParameter("@.member_cpr", Session["member_cpr"]));
sql_command.Parameters.Add(new SqlParameter("@.member_nationality", Session["member_nationality"].ToString()));
sql_command.Parameters.Add(new SqlParameter("@.member_block", Session["member_block"].ToString()));
sql_command.Parameters.Add(new SqlParameter("@.member_gov", "GOV"));
sql_command.Parameters.Add(new SqlParameter("@.member_daaera", 6));
sql_command.Parameters.Add(new SqlParameter("@.member_email", Session["member_email"].ToString().ToLower()));
sql_command.Parameters.Add(new SqlParameter("@.member_mobile", Session["member_mobile"].ToString()));
sql_command.Parameters.Add(new SqlParameter("@.member_created_ip", Request.UserHostAddress.ToString()));

sql_connection.Open();
sql_command.ExecuteNonQuery();
sql_connection.Close();

Create the GUID yourself, and pass it into the insert.

Retrieve GUID or SID FROM Active Directory via ADSI and T-SQL only?

Hi all,

is there a way to get an object's SID or GUID using T-SQL only?

Up to now I got the following code of Active Directory Service Interfaces working,

but GUID or SID are not among the parameters known to me.

Code Snippet

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'

SELECT * FROM OpenQuery(ADSI, 'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn FROM ''LDAP://DC=whatever,DC=domain,DC=org'' where objectClass = ''User''')

I do not want to use anything other then SQL Server 2000 to get an AD-object's primary key.

Any comments would be appreciated.

Thank you!

Regards,

caracol

Windows 2000 Server, SQL Server 2000, AD in W2K only mode

This is not an SSIS question. Moving to the Transact-SQL Forum.|||

Hi all,

just in case there should be someone else looking for identifiable data to be extracted from Active Directory:

A complete list with all attributes can be found at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adschema/adschema/attributes_all.asp

For every attribute the LDAP-Display-Name is given which can be accessed by ADSI.

My SELECT from above should be

Code Snippet

SELECT * FROM OpenQuery(ADSI, 'SELECT objectGUID, title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn FROM ''LDAP://DC=whatever,DC=domain,DC=org'' where objectClass = ''User''')

then.

Regards,

caracol

Retrieve GUID from SQL 2005 Stored Procedure

I have a stored procedure that returns GUID and BIT datatypes (see
below). I am using the VS 2005 TableAdapter.GetData to execute and
return the values. BUT, I can't pass a null value for the GUID output
parameter.
- Why is it required to pass a value for an output value?
- How do I retrieve the GUID value? If I remove the GUID field from
the SP, I can retrieve the BIT fields...so I am confident the SP works
fine.
Thanks for any ideas.
-KB
STORED PROCEDURE:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_UserLogin]
(
@.UserName nvarchar(50),
@.Password nvarchar(50),
@.Authorized bit output,
@.UserID uniqueidentifier output,
@.Security_Expeditor bit output,
@.Security_Tech bit output,
@.Security_WS bit output,
@.Security_RN bit output,
@.Security_MD bit output,
@.Security_SuperUser bit output,
@.Security_ReportAccess bit output,
@.Security_Admin bit output
)
AS
SET NOCOUNT ON;
BEGIN
Select @.Authorized = 'False'
Select @.Security_Expeditor = 'False';
Select @.Security_Tech = 'False';
Select @.Security_WS = 'False';
Select @.Security_RN = 'False';
Select @.Security_MD = 'False';
Select @.Security_SuperUser = 'False';
Select @.Security_ReportAccess = 'False';
Select @.Security_Admin = 'False';
END
BEGIN
select @.UserID = (select top 1 UserID from vw_UserInfoRoles
where UserName = @.UserName and Password = @.Password and
IsApproved = 'True');
if @.@.rowcount > 0
set @.Authorized = 'True'
END
Begin
select * from vw_UserInfoRoles
where UserName = @.UserName and Password = @.Password and IsApproved =
'False'
end
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'expeditor' and UserName = @.UserName and
Password = @.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_Expeditor = 'True'
END
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'tech' and UserName = @.UserName and Password =
@.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_Tech = 'True'
END
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'ws' and UserName = @.UserName and Password =
@.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_WS = 'True'
END
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'rn' and UserName = @.UserName and Password =
@.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_RN = 'True'
END
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'md' and UserName = @.UserName and Password =
@.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_MD = 'True'
END
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'superuser' and UserName = @.UserName and
Password = @.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_SuperUser = 'True'
END
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'reportaccess' and UserName = @.UserName and
Password = @.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_ReportAccess = 'True'
END
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'adminsecurity' and UserName = @.UserName and
Password = @.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_Admin = 'True'
END(corsspost to unofficial group removed)
On 19 Apr 2006 10:09:21 -0700, kb wrote:

>I have a stored procedure that returns GUID and BIT datatypes (see
>below). I am using the VS 2005 TableAdapter.GetData to execute and
>return the values. BUT, I can't pass a null value for the GUID output
>parameter.
Hi kb,
How do you attempt to pass NULL? For an OUTPUT variable, you have to
pass in a variable, never a constant - but that variable can be NULL
(see code example below).

>- Why is it required to pass a value for an output value?
Not a value, but a variable - because an output variable can be changed
from the stored proc. You can't change a constant!

>- How do I retrieve the GUID value?
See this example:
CREATE PROC Test @.guid uniqueidentifier OUTPUT
AS
SET @.guid = NEWID()
go
DECLARE @.x uniqueidentifier
SET @.x = NULL
SELECT @.x
EXEC Test @.guid = @.x OUTPUT
SELECT @.x
go
DROP PROC Test
go
Hugo Kornelis, SQL Server MVP

Retrieve Guid after inserting recrod with NEWID()

Hi There,

I'm having a problem retreiving the auto generated Guid after inserting anew record with NEWID(), my stored proc is as follows:

SET @.uiTransactionID = NEWID()INSERT INTO Transactions (uiTransactionID) VALUES (@.uiTransactionID)IF @.@.ERROR = 0 AND @.@.ROWCOUNT = 1BEGIN SELECT @.uiTransactionID AS'@.@.GUID' RETURN 0END

And the return on my insert statement is:

command.ExecuteNonQuery();
m_uiTransactionID = (Guid

)command.Parameters["RETURN_VALUE"].Value;

I can never retreive the newly generated Guid, can onyone spot where i'm going wrong?

Many thanks

Ben

Hi,

i have tested your stored procedure and i think the stored procedure is correct. Please check your calling code for the stored procedure:

protected void CallStoredProcedure(){ Guid m_uiTransactionID; SqlCommand cmd =new SqlCommand("Test",new SqlConnection(ConfigurationManager.ConnectionStrings["DataBase"].ToString()));cmd.CommandType = CommandType.StoredProcedure;cmd.Parameters.Add("@.uiTransactionID", SqlDbType.UniqueIdentifier);cmd.Parameters["@.uiTransactionID"].Direction = ParameterDirection.Output;try { cmd.Connection.Open(); cmd.ExecuteNonQuery();m_uiTransactionID = (Guid)cmd.Parameters["@.uiTransactionID"].Value; }catch (Exception exc) { }finally { cmd.Connection.Close(); }}

And you can also check the stored procedure execute permissons.

Hope this can help you.

Regards
Marc André

|||

Hi Marc,

Thanks for your reply, you've helped me agreat deal and it is now working.

Many thnks

Ben