I have insert sp's that run on a subscriber. After the insert sql statement
I use scope_identity to get the last inserted identity for child rows for
parent child relationships.
After implementing transactional replication with immediate updates, calling
this sp on the subscriber inserts the row properly but both scope_identity
and @.@.identity returns null.
How do I get the identity value of the last inserted row on a subscriber so
I can use that ID for child rows?Ben,
interesting. These identity values are controlled by the publisher rather
than the subscriber. In fact, you should find that on the subscriber there
isn't an identity property on the tables for this reason. Are you're using
SQL Server 2005? If so, you could capture the assigned value using the
OUTPUT clause.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||No I'm using SQL Server 2000...i may have to move to 2005 sooner than later.
Its hard to believe this can't work because let's say you have an order with
order details tables. You would first create the order on the order table
and get a new Order ID identity, then have to use that identity for the
parent child relationship to the order detail row. This type of scenario
must've been implemented in sql server 2000 with transactional replication
and immediate updates right? How do you retrieve the new ident from the
subscriber without doing maybe a Select max(ident_column) after the insert?
"Paul Ibison" wrote:
> Ben,
> interesting. These identity values are controlled by the publisher rather
> than the subscriber. In fact, you should find that on the subscriber there
> isn't an identity property on the tables for this reason. Are you're using
> SQL Server 2005? If so, you could capture the assigned value using the
> OUTPUT clause.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>|||Ben,
I've just retested this and it depends on your replication setup. If you
have the identity column set to 'Yes (Not for Replication)' then there are
problems, but if it is set to just 'Yes' (the recommended way) then
@.@.identity should return the correct value and scope_identity() will not
return anything. Perhaps this is the source of your issue? Alternatively I
was wondering if you have any user triggers in action?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||I have no triggers on these tables other than the ones created by replication.
Paul, I'm assuming you're asking me if the Publisher has the identity column
set to 'Yes (Not for Replication)'. If so, then no its set to simply "Yes".
In your test environment, was the subscriber on the same server? I tested
this with the subscriber on the same server and the @.@.identity did return the
correct value, but when the subscriber was on another sql server it still
returned null.
so to clarify, my publisher has a identity column with "Yes", and my
subscriber has this column replicated as an int (i.e. no ident column)...the
subscriber column shouldn't have an ident right?
"Paul Ibison" wrote:
> Ben,
> I've just retested this and it depends on your replication setup. If you
> have the identity column set to 'Yes (Not for Replication)' then there are
> problems, but if it is set to just 'Yes' (the recommended way) then
> @.@.identity should return the correct value and scope_identity() will not
> return anything. Perhaps this is the source of your issue? Alternatively I
> was wondering if you have any user triggers in action?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>|||Hi Ben,
Yes - I agree with everything you're saying :)
I'm currently testing your scenario on my home PC (2 databases on same
instance) which catches the @.@.identity (without the NFR attribute). I will
set it up accross servers when I get to work tomorrow.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||Thank you Paul!!! I've been racking my brain around this for almost 2 days.
My intern worse case scenario is to move to 2005 and use the OUTPUT clause
you mentioned or replacing the "Scope_Identity()" code to a Select
Max(Ident_Column) in the insert sp ...which to me feels more like a hack
thanks again!
"Paul Ibison" wrote:
> Hi Ben,
> Yes - I agree with everything you're saying :)
> I'm currently testing your scenario on my home PC (2 databases on same
> instance) which catches the @.@.identity (without the NFR attribute). I will
> set it up accross servers when I get to work tomorrow.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
>|||Ben,
Unfortunately we only have 1 instance of SQL Server 2000 on the network and
when I finally got this set up on 2 networked instances of SQL Server 2005
it worked fine - I could pick up both the @.@.identity and the
scope_identity() values. However I noticed that this is implemented
differently and the identity property is on the subscriber in the new
version so this is not really a decent test. I'll try to install SQL Server
2000 on another box later on and test it this way, but I recommend getting a
support engineer (PSS) to test this for you as I won't have the time to set
all this up quickly.
Regards,
Paul Ibison|||thanks for the help Paul. I will do a quick test on 2005 and see if it
works. I'll keep you posted.
"Paul Ibison" wrote:
> Ben,
> Unfortunately we only have 1 instance of SQL Server 2000 on the network and
> when I finally got this set up on 2 networked instances of SQL Server 2005
> it worked fine - I could pick up both the @.@.identity and the
> scope_identity() values. However I noticed that this is implemented
> differently and the identity property is on the subscriber in the new
> version so this is not really a decent test. I'll try to install SQL Server
> 2000 on another box later on and test it this way, but I recommend getting a
> support engineer (PSS) to test this for you as I won't have the time to set
> all this up quickly.
> Regards,
> Paul Ibison
>
>|||Paul, here's my update.
I tried sql server 2005 and Yes Scope_Identity() and @.@.identity worked as
you mentioned but it seems to work differently in 2005.
In 2000 replicated identity columns would be replicated to their base type,
in my case an int. In 2005 it seems to replicate it as an identity column,
which I guess is the reason why the Scope_Identity and @.@.identity work. But
in 2005 it seems like it defaults to Automatic Range Management, but in 2000
I was able to get the identities updating sequentially with no Automatic
Range Management required. Is this feature gone in 2005? I would prefer to
have the immediate updating subscribers get the next identity from the
publisher so that the subscriber and publisher use the same identity
"manager" to get the next identity value (i.e. no identity ranges and no
gaps).
I'm going to do some more testing and keep you up to date.
"Ben Lam" wrote:
> thanks for the help Paul. I will do a quick test on 2005 and see if it
> works. I'll keep you posted.
> "Paul Ibison" wrote:
> > Ben,
> > Unfortunately we only have 1 instance of SQL Server 2000 on the network and
> > when I finally got this set up on 2 networked instances of SQL Server 2005
> > it worked fine - I could pick up both the @.@.identity and the
> > scope_identity() values. However I noticed that this is implemented
> > differently and the identity property is on the subscriber in the new
> > version so this is not really a decent test. I'll try to install SQL Server
> > 2000 on another box later on and test it this way, but I recommend getting a
> > support engineer (PSS) to test this for you as I won't have the time to set
> > all this up quickly.
> > Regards,
> > Paul Ibison
> >
> >
> >
Showing posts with label identity. Show all posts
Showing posts with label identity. Show all posts
Thursday, March 22, 2012
Does scope_identity or @@identity always return NULL on Subscriber
I have insert sp's that run on a subscriber. After the insert sql statement
I use scope_identity to get the last inserted identity for child rows for
parent child relationships.
After implementing transactional replication with immediate updates, calling
this sp on the subscriber inserts the row properly but both scope_identity
and @.@.identity returns null.
How do I get the identity value of the last inserted row on a subscriber so
I can use that ID for child rows?Ben,
interesting. These identity values are controlled by the publisher rather
than the subscriber. In fact, you should find that on the subscriber there
isn't an identity property on the tables for this reason. Are you're using
SQL Server 2005? If so, you could capture the assigned value using the
OUTPUT clause.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I use scope_identity to get the last inserted identity for child rows for
parent child relationships.
After implementing transactional replication with immediate updates, calling
this sp on the subscriber inserts the row properly but both scope_identity
and @.@.identity returns null.
How do I get the identity value of the last inserted row on a subscriber so
I can use that ID for child rows?Ben,
interesting. These identity values are controlled by the publisher rather
than the subscriber. In fact, you should find that on the subscriber there
isn't an identity property on the tables for this reason. Are you're using
SQL Server 2005? If so, you could capture the assigned value using the
OUTPUT clause.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Wednesday, March 21, 2012
Does optimizer know about GUIDs?
Just wondering if there's anything the optimizer treats differently
when it sees a GUID instead of, say, an identity int, as PK. GUIDs
being random and identities being sequential, one could impute some
differences in efficiency, especially when one or both are clustered.
Thanks.
Joshua SternNo.
And, who says that GUIDs aren't sequential?
http://www.informit.com/articles/article.asp?p=25862&seqNum=7&rl=1
http://www.sqldev.net/xp/xpguid.htm
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:p82qf11f3bp12b9lkgt28q8f1gpinj6p8b@.4ax.com...
> Just wondering if there's anything the optimizer treats differently
> when it sees a GUID instead of, say, an identity int, as PK. GUIDs
> being random and identities being sequential, one could impute some
> differences in efficiency, especially when one or both are clustered.
> Thanks.
> Joshua Stern
>|||On Fri, 12 Aug 2005 18:29:52 -0400, "Adam Machanic"
<amachanic@.hotmail._removetoemail_.com> wrote:
>No.
>And, who says that GUIDs aren't sequential?
>http://www.informit.com/articles/article.asp?p=25862&seqNum=7&rl=1
>http://www.sqldev.net/xp/xpguid.htm
Hmm.
J.|||No, I do not think the optimizer tries to *guess* or infer anything about
guids... You provide the PK, FK , Indexes etc for the optimizer to use..
Just because a column is a uniqueidentifier does not mean it has to be
unique.
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:p82qf11f3bp12b9lkgt28q8f1gpinj6p8b@.4ax.com...
> Just wondering if there's anything the optimizer treats differently
> when it sees a GUID instead of, say, an identity int, as PK. GUIDs
> being random and identities being sequential, one could impute some
> differences in efficiency, especially when one or both are clustered.
> Thanks.
> Joshua Stern
>
when it sees a GUID instead of, say, an identity int, as PK. GUIDs
being random and identities being sequential, one could impute some
differences in efficiency, especially when one or both are clustered.
Thanks.
Joshua SternNo.
And, who says that GUIDs aren't sequential?
http://www.informit.com/articles/article.asp?p=25862&seqNum=7&rl=1
http://www.sqldev.net/xp/xpguid.htm
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:p82qf11f3bp12b9lkgt28q8f1gpinj6p8b@.4ax.com...
> Just wondering if there's anything the optimizer treats differently
> when it sees a GUID instead of, say, an identity int, as PK. GUIDs
> being random and identities being sequential, one could impute some
> differences in efficiency, especially when one or both are clustered.
> Thanks.
> Joshua Stern
>|||On Fri, 12 Aug 2005 18:29:52 -0400, "Adam Machanic"
<amachanic@.hotmail._removetoemail_.com> wrote:
>No.
>And, who says that GUIDs aren't sequential?
>http://www.informit.com/articles/article.asp?p=25862&seqNum=7&rl=1
>http://www.sqldev.net/xp/xpguid.htm
Hmm.
J.|||No, I do not think the optimizer tries to *guess* or infer anything about
guids... You provide the PK, FK , Indexes etc for the optimizer to use..
Just because a column is a uniqueidentifier does not mean it has to be
unique.
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:p82qf11f3bp12b9lkgt28q8f1gpinj6p8b@.4ax.com...
> Just wondering if there's anything the optimizer treats differently
> when it sees a GUID instead of, say, an identity int, as PK. GUIDs
> being random and identities being sequential, one could impute some
> differences in efficiency, especially when one or both are clustered.
> Thanks.
> Joshua Stern
>
Does optimizer know about GUIDs?
Just wondering if there's anything the optimizer treats differently
when it sees a GUID instead of, say, an identity int, as PK. GUIDs
being random and identities being sequential, one could impute some
differences in efficiency, especially when one or both are clustered.
Thanks.
Joshua Stern
No.
And, who says that GUIDs aren't sequential?
http://www.informit.com/articles/art...&seqNum=7&rl=1
http://www.sqldev.net/xp/xpguid.htm
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:p82qf11f3bp12b9lkgt28q8f1gpinj6p8b@.4ax.com...
> Just wondering if there's anything the optimizer treats differently
> when it sees a GUID instead of, say, an identity int, as PK. GUIDs
> being random and identities being sequential, one could impute some
> differences in efficiency, especially when one or both are clustered.
> Thanks.
> Joshua Stern
>
|||On Fri, 12 Aug 2005 18:29:52 -0400, "Adam Machanic"
<amachanic@.hotmail._removetoemail_.com> wrote:
>No.
>And, who says that GUIDs aren't sequential?
>http://www.informit.com/articles/art...&seqNum=7&rl=1
>http://www.sqldev.net/xp/xpguid.htm
Hmm.
J.
|||No, I do not think the optimizer tries to *guess* or infer anything about
guids... You provide the PK, FK , Indexes etc for the optimizer to use..
Just because a column is a uniqueidentifier does not mean it has to be
unique.
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:p82qf11f3bp12b9lkgt28q8f1gpinj6p8b@.4ax.com...
> Just wondering if there's anything the optimizer treats differently
> when it sees a GUID instead of, say, an identity int, as PK. GUIDs
> being random and identities being sequential, one could impute some
> differences in efficiency, especially when one or both are clustered.
> Thanks.
> Joshua Stern
>
when it sees a GUID instead of, say, an identity int, as PK. GUIDs
being random and identities being sequential, one could impute some
differences in efficiency, especially when one or both are clustered.
Thanks.
Joshua Stern
No.
And, who says that GUIDs aren't sequential?
http://www.informit.com/articles/art...&seqNum=7&rl=1
http://www.sqldev.net/xp/xpguid.htm
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:p82qf11f3bp12b9lkgt28q8f1gpinj6p8b@.4ax.com...
> Just wondering if there's anything the optimizer treats differently
> when it sees a GUID instead of, say, an identity int, as PK. GUIDs
> being random and identities being sequential, one could impute some
> differences in efficiency, especially when one or both are clustered.
> Thanks.
> Joshua Stern
>
|||On Fri, 12 Aug 2005 18:29:52 -0400, "Adam Machanic"
<amachanic@.hotmail._removetoemail_.com> wrote:
>No.
>And, who says that GUIDs aren't sequential?
>http://www.informit.com/articles/art...&seqNum=7&rl=1
>http://www.sqldev.net/xp/xpguid.htm
Hmm.
J.
|||No, I do not think the optimizer tries to *guess* or infer anything about
guids... You provide the PK, FK , Indexes etc for the optimizer to use..
Just because a column is a uniqueidentifier does not mean it has to be
unique.
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:p82qf11f3bp12b9lkgt28q8f1gpinj6p8b@.4ax.com...
> Just wondering if there's anything the optimizer treats differently
> when it sees a GUID instead of, say, an identity int, as PK. GUIDs
> being random and identities being sequential, one could impute some
> differences in efficiency, especially when one or both are clustered.
> Thanks.
> Joshua Stern
>
Does optimizer know about GUIDs?
Just wondering if there's anything the optimizer treats differently
when it sees a GUID instead of, say, an identity int, as PK. GUIDs
being random and identities being sequential, one could impute some
differences in efficiency, especially when one or both are clustered.
Thanks.
Joshua SternNo.
And, who says that GUIDs aren't sequential?
http://www.informit.com/articles/ar...2&seqNum=7&rl=1
http://www.sqldev.net/xp/xpguid.htm
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:p82qf11f3bp12b9lkgt28q8f1gpinj6p8b@.
4ax.com...
> Just wondering if there's anything the optimizer treats differently
> when it sees a GUID instead of, say, an identity int, as PK. GUIDs
> being random and identities being sequential, one could impute some
> differences in efficiency, especially when one or both are clustered.
> Thanks.
> Joshua Stern
>|||On Fri, 12 Aug 2005 18:29:52 -0400, "Adam Machanic"
<amachanic@.hotmail._removetoemail_.com> wrote:
>No.
>And, who says that GUIDs aren't sequential?
>http://www.informit.com/articles/ar...2&seqNum=7&rl=1
>http://www.sqldev.net/xp/xpguid.htm
Hmm.
J.|||No, I do not think the optimizer tries to *guess* or infer anything about
guids... You provide the PK, FK , Indexes etc for the optimizer to use..
Just because a column is a uniqueidentifier does not mean it has to be
unique.
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:p82qf11f3bp12b9lkgt28q8f1gpinj6p8b@.
4ax.com...
> Just wondering if there's anything the optimizer treats differently
> when it sees a GUID instead of, say, an identity int, as PK. GUIDs
> being random and identities being sequential, one could impute some
> differences in efficiency, especially when one or both are clustered.
> Thanks.
> Joshua Stern
>
when it sees a GUID instead of, say, an identity int, as PK. GUIDs
being random and identities being sequential, one could impute some
differences in efficiency, especially when one or both are clustered.
Thanks.
Joshua SternNo.
And, who says that GUIDs aren't sequential?
http://www.informit.com/articles/ar...2&seqNum=7&rl=1
http://www.sqldev.net/xp/xpguid.htm
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:p82qf11f3bp12b9lkgt28q8f1gpinj6p8b@.
4ax.com...
> Just wondering if there's anything the optimizer treats differently
> when it sees a GUID instead of, say, an identity int, as PK. GUIDs
> being random and identities being sequential, one could impute some
> differences in efficiency, especially when one or both are clustered.
> Thanks.
> Joshua Stern
>|||On Fri, 12 Aug 2005 18:29:52 -0400, "Adam Machanic"
<amachanic@.hotmail._removetoemail_.com> wrote:
>No.
>And, who says that GUIDs aren't sequential?
>http://www.informit.com/articles/ar...2&seqNum=7&rl=1
>http://www.sqldev.net/xp/xpguid.htm
Hmm.
J.|||No, I do not think the optimizer tries to *guess* or infer anything about
guids... You provide the PK, FK , Indexes etc for the optimizer to use..
Just because a column is a uniqueidentifier does not mean it has to be
unique.
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:p82qf11f3bp12b9lkgt28q8f1gpinj6p8b@.
4ax.com...
> Just wondering if there's anything the optimizer treats differently
> when it sees a GUID instead of, say, an identity int, as PK. GUIDs
> being random and identities being sequential, one could impute some
> differences in efficiency, especially when one or both are clustered.
> Thanks.
> Joshua Stern
>
Monday, March 19, 2012
Does not have permission to run DBCC CHECKIDENT
I have created a stored procedure in which i have created a temproray table with 2 fields, one is IDENTITY column and i set the seed by using DBCC CHECKIDENT but it gave an error about permission.
Please give suggestion for resolving this issue.
DBCC CHECKIDENT permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database role
Pls check the ONLINE Document for more details..
Sunday, March 11, 2012
Does Identity columns... create an index by default
Hi,
If I create an identity column on a sql server table.... does "sql server"
create an index too...
Or do I have to create an index explicitly.
Thanks
Nalaka"Nalaka" <nalaka12@.nospam.nospam> wrote in message
news:OXPawNnGIHA.4476@.TK2MSFTNGP06.phx.gbl...
> Hi,
> If I create an identity column on a sql server table.... does "sql
> server" create an index too...
> Or do I have to create an index explicitly.
> Thanks
> Nalaka
>
>
No it doesn't. Assuming, you want the column to be unique you should create
a PRIMARY KEY or UNIQUE constraint on the column. A constraint does
automatically create an index.
--
David Portas|||Thanks David
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:O23KLQnGIHA.5980@.TK2MSFTNGP04.phx.gbl...
> "Nalaka" <nalaka12@.nospam.nospam> wrote in message
> news:OXPawNnGIHA.4476@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> If I create an identity column on a sql server table.... does "sql
>> server" create an index too...
>> Or do I have to create an index explicitly.
>> Thanks
>> Nalaka
>>
>>
> No it doesn't. Assuming, you want the column to be unique you should
> create a PRIMARY KEY or UNIQUE constraint on the column. A constraint does
> automatically create an index.
> --
> David Portas
>
If I create an identity column on a sql server table.... does "sql server"
create an index too...
Or do I have to create an index explicitly.
Thanks
Nalaka"Nalaka" <nalaka12@.nospam.nospam> wrote in message
news:OXPawNnGIHA.4476@.TK2MSFTNGP06.phx.gbl...
> Hi,
> If I create an identity column on a sql server table.... does "sql
> server" create an index too...
> Or do I have to create an index explicitly.
> Thanks
> Nalaka
>
>
No it doesn't. Assuming, you want the column to be unique you should create
a PRIMARY KEY or UNIQUE constraint on the column. A constraint does
automatically create an index.
--
David Portas|||Thanks David
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:O23KLQnGIHA.5980@.TK2MSFTNGP04.phx.gbl...
> "Nalaka" <nalaka12@.nospam.nospam> wrote in message
> news:OXPawNnGIHA.4476@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> If I create an identity column on a sql server table.... does "sql
>> server" create an index too...
>> Or do I have to create an index explicitly.
>> Thanks
>> Nalaka
>>
>>
> No it doesn't. Assuming, you want the column to be unique you should
> create a PRIMARY KEY or UNIQUE constraint on the column. A constraint does
> automatically create an index.
> --
> David Portas
>
Subscribe to:
Posts (Atom)