Incorrect Identity Range

I'm trying to replicate a contacts table that has about
80000 records in it. When I set an identity range of 1000
but when the publication is created it keeps setting the
next identity range to start at 49000. This creates an
error that means that data cannot be entered either at the
publishing end or the subscribing end.
I did previously have this table replicating to two
subscribers and this is when the error first occurred. I
removed the subscriptions and then the publication and
tried to clean up any meta data that may have remained. I
then redid the publication but had the same problem.
is the datatype for the identity column int or smallint, or something else
altogether?
What happens if you script out your publication and make the changes to the
underlying script and deploy it?
Also do the underlying tables have any check constraints remaining on them
for the identity column? These are not removed when you recreate a nosync
subscription.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Mburns" <anonymous@.discussions.microsoft.com> wrote in message
news:814f01c484dc$23bfcb20$a401280a@.phx.gbl...
> I'm trying to replicate a contacts table that has about
> 80000 records in it. When I set an identity range of 1000
> but when the publication is created it keeps setting the
> next identity range to start at 49000. This creates an
> error that means that data cannot be entered either at the
> publishing end or the subscribing end.
> I did previously have this table replicating to two
> subscribers and this is when the error first occurred. I
> removed the subscriptions and then the publication and
> tried to clean up any meta data that may have remained. I
> then redid the publication but had the same problem.
|||Thanks for your reply Hilary. My answers to your questions:
The datatype for the identity column is int.
I haven't tried scripting it. I did look for a script but
I couldn't see where to define the starting identity
range. Could you point me somewhere?
I couldn't find any constraints in the underlying tables.
I am replicating this table by itself (own publication) as
it is so large and replicating the other tables from this
db in a separate publication. Is this a problem?
Thanks
>--Original Message--
>is the datatype for the identity column int or smallint,
or something else
>altogether?
>What happens if you script out your publication and make
the changes to the
>underlying script and deploy it?
>Also do the underlying tables have any check constraints
remaining on them
>for the identity column? These are not removed when you
recreate a nosync
>subscription.
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Mburns" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:814f01c484dc$23bfcb20$a401280a@.phx.gbl...
1000[vbcol=seagreen]
the[vbcol=seagreen]
I
>
>.
>

Including New Tables in an Existing Publication

Hi.
I work for a company with 40+ remote workstations that synchronize their
databases wirelessly to receive new jobs, as well as submit field tickets
created on their laptops. This time of the year is our busiest period,
however we have some new tables in the database that we would like to
include in the replication scheme. It is my understanding that you can do
this, however you have to ensure that all of your subscriptions have
synchronized and that no data is modified until the new tables are included
in the publication, the dynamic snapshot jobs are re-run, and the
subscriptions have been re-created. Is this correct? If it is, herein lies
my problem. Because our company is in our busy period, the chances of this
happening are somewhere in between none and impossible. I was wondering if
any of you have encountered a situation like this before, and what you did
to "make the job happen". Any suggestions are MUCH appreciated, and if you
require any more information, please do not hesitate to let me know.
Best Regards,
Brad
PS - We are using SQL2000, with Merge Replication. Sorry I didn't include
this in the first post.
Brad
"Brad M." <anonymous@.discussions.microsoft.com> wrote in message
news:%23QGMl1X3EHA.3840@.tk2msftngp13.phx.gbl...
> Hi.
> I work for a company with 40+ remote workstations that synchronize their
> databases wirelessly to receive new jobs, as well as submit field tickets
> created on their laptops. This time of the year is our busiest period,
> however we have some new tables in the database that we would like to
> include in the replication scheme. It is my understanding that you can do
> this, however you have to ensure that all of your subscriptions have
> synchronized and that no data is modified until the new tables are
> included in the publication, the dynamic snapshot jobs are re-run, and the
> subscriptions have been re-created. Is this correct? If it is, herein
> lies my problem. Because our company is in our busy period, the chances
> of this happening are somewhere in between none and impossible. I was
> wondering if any of you have encountered a situation like this before, and
> what you did to "make the job happen". Any suggestions are MUCH
> appreciated, and if you require any more information, please do not
> hesitate to let me know.
> Best Regards,
> Brad
>
|||I think you would be best to create a new publication to distribute these
articles.
Either that or do something like this:
sp_addmergearticle 'Publication', 'NewTable', 'NewTable',
@.force_invalidate_snapshot =1 which will generate and distribute an entire
new snapshot of all the articles.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Brad M." <anonymous@.discussions.microsoft.com> wrote in message
news:O48xI8Z3EHA.2624@.TK2MSFTNGP11.phx.gbl...
> PS - We are using SQL2000, with Merge Replication. Sorry I didn't include
> this in the first post.
> Brad
> "Brad M." <anonymous@.discussions.microsoft.com> wrote in message
> news:%23QGMl1X3EHA.3840@.tk2msftngp13.phx.gbl...
>
|||So will this affect my users in any way? The main thing I need to know is
that will my users still be able to run their app, create records, save
them, sync, receive the new tables, and the data they created before last
sync - will it still be there, and will it make it back to the publisher?
I've thought about creating a second publication, but for ease of
administration, I'd like to keep everything in one publication if at all
possible.
Thanks alot for your help Hilary!
Best Regards,
Brad
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eOb3zNf3EHA.2156@.TK2MSFTNGP10.phx.gbl...
>I think you would be best to create a new publication to distribute these
>articles.
> Either that or do something like this:
> sp_addmergearticle 'Publication', 'NewTable', 'NewTable',
> @.force_invalidate_snapshot =1 which will generate and distribute an entire
> new snapshot of all the articles.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "Brad M." <anonymous@.discussions.microsoft.com> wrote in message
> news:O48xI8Z3EHA.2624@.TK2MSFTNGP11.phx.gbl...
>
|||A safe way (I've done this) would be to add a second publication on your
server containing the new tables...
On the subscribers add a second subscription... This will leave your
production stuff in place & gives you time to roll out the new tables--
Only downside now there are 80 subs in the field logging in, + the
additional sub on the client (logs in twice... so its slower)
Once you have some down time you could then combine the 2 & redo all the
clients... (you can build scripts for this..)
"Brad M." <anonymous@.discussions.microsoft.com> wrote in message
news:O$$gO8f3EHA.524@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> So will this affect my users in any way? The main thing I need to know is
> that will my users still be able to run their app, create records, save
> them, sync, receive the new tables, and the data they created before last
> sync - will it still be there, and will it make it back to the publisher?
> I've thought about creating a second publication, but for ease of
> administration, I'd like to keep everything in one publication if at all
> possible.
> Thanks alot for your help Hilary!
> Best Regards,
> Brad
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eOb3zNf3EHA.2156@.TK2MSFTNGP10.phx.gbl...
entire[vbcol=seagreen]
their[vbcol=seagreen]
busiest[vbcol=seagreen]
that[vbcol=seagreen]
between
>
|||Okay, first let me thank you for helping me out. I realize that there is an
option to add a second publication, but I'd really like to avoid this if at
all possible. So, is it technically possible to add articles to merge
publications without reinitializing the subscriptions/affecting any data
entered by the subscriber which has not yet replicated to the publisher?
Basically, the reason why I'm trying to avoid the 2nd pub is because our
busy period is our *busiest* - we are still slammed in the spring, summer
and fall and we never get an opportunity to see all of the subscribers at
once - so I would never have enough downtime to redo the subscribers in a
timely manner which would allow for uninterrupted flow of data (which senior
management in my company demands).
If all of our merge subscriptions are dynamic snapshots (which they are),
and I want to add articles to the pub, will this require reinitializing each
subscriber?
Thanks to all of you for your responses and answers.
Best Regards,
Brad
"S c o t t K r a m e r" <sckramer2000@.hotmail.com> wrote in message
news:vc0ud.227$bu1.134@.fe61.usenetserver.com...
>A safe way (I've done this) would be to add a second publication on your
> server containing the new tables...
> On the subscribers add a second subscription... This will leave your
> production stuff in place & gives you time to roll out the new tables--
> Only downside now there are 80 subs in the field logging in, + the
> additional sub on the client (logs in twice... so its slower)
> Once you have some down time you could then combine the 2 & redo all the
> clients... (you can build scripts for this..)
>
> "Brad M." <anonymous@.discussions.microsoft.com> wrote in message
> news:O$$gO8f3EHA.524@.TK2MSFTNGP09.phx.gbl...
> entire
> their
> busiest
> that
> between
>
>