rowguid : Merge Replication

12 Aug 2006 7:54 AM

Russell Mangel

Question:
How do I get the rowguid value after an insert
operation? I would like to return this guid to the client.

I simply have a stored procedure that inserts a
row into a table, just after the insert statement,
I would like to return the rowguid to the client
via an output parameter of the stored procedure.

Is this possible?

I am using: SQL Server 2005 and merge replication.

Thanks

Russell Mangel
Las Vegas, NV

12 Aug 2006 8:00 AM

Tibor Karaszi

You can either declare a uniqueiudentifier variable, set it to NEWID() and use it in your INSERT
statement. You now know the value. Or you can, after the INSERT, do a SELECT of this column where
you filter on the primary key value for the table (a value that you should know as you just inserted
a row). The no counterpart to SCOPE_IDENTITY()/@@IDENTITY for guids.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Show quote

"Russell Mangel" <russ***@tymer.net> wrote in message
news:uf%23dCSevGHA.3912@TK2MSFTNGP03.phx.gbl...
> Question:
> How do I get the rowguid value after an insert
> operation? I would like to return this guid to the client.
>
> I simply have a stored procedure that inserts a
> row into a table, just after the insert statement,
> I would like to return the rowguid to the client
> via an output parameter of the stored procedure.
>
> Is this possible?
>
> I am using: SQL Server 2005 and merge replication.
>
> Thanks
>
> Russell Mangel
> Las Vegas, NV
>
>
>

12 Aug 2006 9:42 AM

Russell Mangel

As you know when a table is published for
*merge* replication, a rowguid column is
added to every table, and a default of
(newid()) is also added. Since I didn't create
this rowguid column, I just wanted to leave
it alone.

Are you saying that it is okay for me to:
1. Supply the GUID myself on insert, and leave the default (newid()).
2. Supply the GUID myself on insert, and *remove* the (newid()).

Thanks for you reply.

Russell Mangel
Las Vegas, NV

Show quote

"Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in
message news:eWQzyVevGHA.5088@TK2MSFTNGP06.phx.gbl...
> You can either declare a uniqueiudentifier variable, set it to NEWID() and
> use it in your INSERT statement. You now know the value. Or you can, after
> the INSERT, do a SELECT of this column where you filter on the primary key
> value for the table (a value that you should know as you just inserted a
> row). The no counterpart to SCOPE_IDENTITY()/@@IDENTITY for guids.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
>
> "Russell Mangel" <russ***@tymer.net> wrote in message
> news:uf%23dCSevGHA.3912@TK2MSFTNGP03.phx.gbl...
>> Question:
>> How do I get the rowguid value after an insert
>> operation? I would like to return this guid to the client.
>>
>> I simply have a stored procedure that inserts a
>> row into a table, just after the insert statement,
>> I would like to return the rowguid to the client
>> via an output parameter of the stored procedure.
>>
>> Is this possible?
>>
>> I am using: SQL Server 2005 and merge replication.
>>
>> Thanks
>>
>> Russell Mangel
>> Las Vegas, NV
>>
>>
>>
>

12 Aug 2006 2:32 PM

Tibor Karaszi

> As you know when a table is published for
> *merge* replication, a rowguid column is
> added to every table, and a default of
> (newid()) is also added.

Ahh,m I probably knew that as some point in time, but forgot about it. Hmm, what do you want to do
with this? Isnät this like a "system column" which should be ignored?

Anyhow, I wouldn't mess with how the merge replication set it up. Do the insert and then grab the
valule from this column... Something like:

DECLARE @theGuid uniqueidentifier
INSERT ...
SET @theGuid = (SELECT ROWGUIDCOL FROM tblname WHERE pkCol = yourPkValueJustInserted)

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Show quote

"Russell Mangel" <russ***@tymer.net> wrote in message news:ufrbTOfvGHA.2036@TK2MSFTNGP05.phx.gbl...
> As you know when a table is published for
> *merge* replication, a rowguid column is
> added to every table, and a default of
> (newid()) is also added. Since I didn't create
> this rowguid column, I just wanted to leave
> it alone.
>
> Are you saying that it is okay for me to:
> 1. Supply the GUID myself on insert, and leave the default (newid()).
> 2. Supply the GUID myself on insert, and *remove* the (newid()).
>
> Thanks for you reply.
>
> Russell Mangel
> Las Vegas, NV
>
>
> "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message
> news:eWQzyVevGHA.5088@TK2MSFTNGP06.phx.gbl...
>> You can either declare a uniqueiudentifier variable, set it to NEWID() and use it in your INSERT
>> statement. You now know the value. Or you can, after the INSERT, do a SELECT of this column where
>> you filter on the primary key value for the table (a value that you should know as you just
>> inserted a row). The no counterpart to SCOPE_IDENTITY()/@@IDENTITY for guids.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>>
>> "Russell Mangel" <russ***@tymer.net> wrote in message
>> news:uf%23dCSevGHA.3912@TK2MSFTNGP03.phx.gbl...
>>> Question:
>>> How do I get the rowguid value after an insert
>>> operation? I would like to return this guid to the client.
>>>
>>> I simply have a stored procedure that inserts a
>>> row into a table, just after the insert statement,
>>> I would like to return the rowguid to the client
>>> via an output parameter of the stored procedure.
>>>
>>> Is this possible?
>>>
>>> I am using: SQL Server 2005 and merge replication.
>>>
>>> Thanks
>>>
>>> Russell Mangel
>>> Las Vegas, NV
>>>
>>>
>>>
>>
>
>

12 Aug 2006 12:00 PM

Ole Kristian Bangås

"Russell Mangel" <russ***@tymer.net> wrote in news:uf#dCSevGHA.3912

@TK2MSFTNGP03.phx.gbl:

> uid to the client
> via an output parameter of the

It would be possible for you to use the output clause in your select
statement to acheive this, but it would require a table variable:

USE tempdb;
GO

CREATE TABLE SomeTable (
Guid Uniqueidentifier DEFAULT newid() PRIMARY KEY,
SomeVal varchar(50)
);
GO

DECLARE @MyGuid TABLE (Guid uniqueidentifier);

INSERT INTO SomeTable (SomeVal)
OUTPUT inserted.Guid INTO @MyGuid
VALUES ('Some Value');

SElECT Guid FROM @MyGuid

--
Ole Kristian Bangås
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP