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)|||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
> >
> >
> >

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)

Does Replication Agent locks the database?

Hi,
Does the first synchronization(Replication Agent) while setting up merge
replication locks both the publisher and subscriber databases or the
databases can be used while the first synchronization is taking place?
If first synchronization does not lock the databases, will the changes that
have taken place at both the ends be merged later on, when synchronization
is complete?
Thanks
Anukul
Anukul,
there is no exclusive lock on the database, if that is what you are
referring to. There is a shared lock on the database as there would be on
any connection. As far as I know, there aren't exclusive locks held on the
table during the snapshot generation in merge replication. There are
page-level shared locks which'll prevent updates while the snapshot of a
particular page is being generated, but concurrent reads are compatible. In
transactional there is the option to use concurrent snapshot processing
where concurrent changes can occur during the snapshot generation, but not
so in merge or snapshot replication.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul:
I am talking about Replication Agent, which runs after snapshot agent.
Replication Agnet does the syncronization.
Please suggest.
Thanks,
Anukul
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OLV3bsFGFHA.3888@.TK2MSFTNGP12.phx.gbl...
> Anukul,
> there is no exclusive lock on the database, if that is what you are
> referring to. There is a shared lock on the database as there would be on
> any connection. As far as I know, there aren't exclusive locks held on the
> table during the snapshot generation in merge replication. There are
> page-level shared locks which'll prevent updates while the snapshot of a
> particular page is being generated, but concurrent reads are compatible.
In
> transactional there is the option to use concurrent snapshot processing
> where concurrent changes can occur during the snapshot generation, but not
> so in merge or snapshot replication.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||My understanding is that the merge agent works with
batches of records and each separate batch is treated as
a transaction, but the combined synchronization process
is not held under a global transaction. In this case, an
edit to a record on a batch already processed would be
acceptable and would enter MSmerge_contents.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Does it effect performance to have several publications instead of just 1. to the same sub

for example:
I am publishing about 5 tables to one subscriber.
Each table is approx 10gb each. I want to break
them up into 5 different publications in case
something happens to one of them and I need to
reinitialize. I figure If i had to , it would
only need to reinitialize that 1 10gb table.
Whereas if I had all 5 in one publication I would
then be reinitilizing all 5 10gb tables instead
of doing just one.
Am I causing more work for my distributor? I
figured it wouldn't hurt since it's the same log
reader for all the publications and same
distribution agent as well.
tia
-comb
Absolutely not!
In general when deploying very large snapshots like this you should
investigate
compressing your snapshots
bcping the data into the file system and then into the tables
Or using a DTS package with the fast insert option - this provides the best
performance.
Doing this on a live environment will require validation and some clean up
work to ensure everything is in sync.
You will get better performance by using multiple distribution agents if you
use the independent option. I find that using two agents works best. Most
than two causes performance degradation a the pull subscriber. Your results
may vary.
"Combfilter" <adsf@.asdf.com> wrote in message
news:MPG.1be892207facc1cc9896cf@.news.newsreader.co m...
> for example:
> I am publishing about 5 tables to one subscriber.
> Each table is approx 10gb each. I want to break
> them up into 5 different publications in case
> something happens to one of them and I need to
> reinitialize. I figure If i had to , it would
> only need to reinitialize that 1 10gb table.
> Whereas if I had all 5 in one publication I would
> then be reinitilizing all 5 10gb tables instead
> of doing just one.
> Am I causing more work for my distributor? I
> figured it wouldn't hurt since it's the same log
> reader for all the publications and same
> distribution agent as well.
> tia
> -comb
|||In article <OdRqiZ7uEHA.3972
@.TK2MSFTNGP15.phx.gbl>, hilary.cotter@.gmail.com
says...
> Absolutely not!
> In general when deploying very large snapshots like this you should
> investigate
> compressing your snapshots
> bcping the data into the file system and then into the tables
> Or using a DTS package with the fast insert option - this provides the best
> performance.
> Doing this on a live environment will require validation and some clean up
> work to ensure everything is in sync.
> You will get better performance by using multiple distribution agents if you
> use the independent option. I find that using two agents works best. Most
> than two causes performance degradation a the pull subscriber. Your results
> may vary.
>
> "Combfilter" <adsf@.asdf.com> wrote in message
> news:MPG.1be892207facc1cc9896cf@.news.newsreader.co m...
>
>
Thanks Hilary.
I see how to compress the snapshots, but then
when i read some of the older group discussions
about that , that it takes a lot of time to
decompress on the subscriber side and really
doesn't seem to be faster. I would like to learn
how to find a faster way to get these snapshots
over to the subscriber and have them sync up, but
I am not that skilled at sql. I have read some
of your and pauls notes about doing a backup of
the db and restore on the subscriber and some how
you can setup replication "with no sync" or
something like that but cannot find any how to
articles on how to do this.
thanks,
comb
|||In general a compressed snapshot will travel across the wire faster than an
uncompressed on, but then you will have to wait for the snapshot to be
extracted. I have found that the snapshot files I have worked with extract
relatively quickly and compress quickly as well.
I think you should bcp your data into the file system, compress them, send
them across the wire and then bcp them in. Do a no sync subscription and a
validation. Then you need to play catch up to get your subscriber in sync
with the publisher.
If you need more details post back here and Paul or myself will help you.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Combfilter" <adsf@.asdf.com> wrote in message
news:MPG.1be979f3afe5a3769896d0@.news.newsreader.co m...[vbcol=seagreen]
> In article <OdRqiZ7uEHA.3972
> @.TK2MSFTNGP15.phx.gbl>, hilary.cotter@.gmail.com
> says...
best[vbcol=seagreen]
up[vbcol=seagreen]
you[vbcol=seagreen]
Most[vbcol=seagreen]
results
> Thanks Hilary.
> I see how to compress the snapshots, but then
> when i read some of the older group discussions
> about that , that it takes a lot of time to
> decompress on the subscriber side and really
> doesn't seem to be faster. I would like to learn
> how to find a faster way to get these snapshots
> over to the subscriber and have them sync up, but
> I am not that skilled at sql. I have read some
> of your and pauls notes about doing a backup of
> the db and restore on the subscriber and some how
> you can setup replication "with no sync" or
> something like that but cannot find any how to
> articles on how to do this.
> thanks,
> comb
|||In article <#WIdSMJvEHA.1300
@.TK2MSFTNGP14.phx.gbl>, hilary.cotter@.gmail.com
says...
> In general a compressed snapshot will travel across the wire faster than an
> uncompressed on, but then you will have to wait for the snapshot to be
> extracted. I have found that the snapshot files I have worked with extract
> relatively quickly and compress quickly as well.
> I think you should bcp your data into the file system, compress them, send
> them across the wire and then bcp them in. Do a no sync subscription and a
> validation. Then you need to play catch up to get your subscriber in sync
> with the publisher.
> If you need more details post back here and Paul or myself will help you.
>
I tried the compression method yesterday, but my
snapshot agent went suspect. I am guessing when
you say use bcp method that just means click the
check box that says "compress snapshot" under
snapshotlocation tab in the publication
properties? I saw it create a bcp file , and
then once that file was there the next step it
started was to add it to a .cab file, so I am
assuming that was correct? Not sure why my agent
went suspect.
Where do I find the no sync option? Will this
hurt me in the long run? I've searched google
groups and google up and down for a site that
will show this step by step.
Thanks for yours and pauls help in this group.
comb