Re: Error 25081: Incorrectly generating identities after a merge

From: John Kendrick (jkendrick_at_DONTneo.SPAMrr.com)
Date: 11/02/04 

Date: Tue, 2 Nov 2004 14:44:07 -0500

> >> Another quick test would be to use Enterprise Manager to quickly create
a
> >> SQL Server subscription database. This would help narrow down if there
When I did this is Enterprise Manager the only option available from the
Action->New menu is to create a new "Pull Subscription...". Maybe SQL Server
2000 uses RDA but my assumption was that it just replicates the published
database similarly to SQL Server CE Replication. RDA Pulls and Pushes would
be equivalent to SQL Server 2000 Import/Export.

Table used for testing:
CREATE TABLE [dbo].[last_product_sel] (
 [rowid_pk] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
 [planid_fk] [int] NULL ,
 [prodid_fk] [int] NULL ,
 [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
-- More SQL creates the PRIMARY and FOREIGN keys

On the SQL Server 2000 database with replicated tables from the original
database I ran the following SQL statement in Query Analyzer after the first
synch:
INSERT INTO last_product_sel (planid_fk, prodid_fk) VALUES ('1','123')
    -> result inserted rowid_pk = 501001 (within the expected range)
The I synchronized that database and ran the same query:
INSERT INTO last_product_sel (planid_fk, prodid_fk) VALUES ('1','123')
    -> result inserted rowid_pk = 501002 (within the expected range)

On the handheld I ran the same query in CE Query Analyzer after creating the
database:
INSERT INTO last_product_sel (planid_fk, prodid_fk) VALUES ('1','123')
    -> result inserted rowid_pk = 502001 (within the expected range)
The I synchronized that database and ran the same query:
INSERT INTO last_product_sel (planid_fk, prodid_fk) VALUES ('1','123')
    -> error 25081

On the handheld I ran the same query in CE Query Analyzer after creating the
database again:
INSERT INTO last_product_sel (planid_fk, prodid_fk) VALUES ('1','123')
    -> result inserted rowid_pk = 503001 (within the expected range)
The I synchronized (with reinitialize) that database and ran the same query:
INSERT INTO last_product_sel (planid_fk, prodid_fk) VALUES ('1','123')
    -> result inserted rowid_pk = 504001 (new range allocated)

"Kevin Collins (MS - SQL Mobile)" <kevincol@online.microsoft.com> wrote in
message news:ODtl5ZEwEHA.3088@TK2MSFTNGP12.phx.gbl...
> I am slightly confused on some terminology that I wanted to clarify.
>
> When I hear "PULL" i think of RDA and not merge replication. I wanted to
> verify that you are not using RDA and are only using merge replication.
>
> Second, when you refer to the "tests" where the subscription is behaving
> normally what is the "test". What I am understanding from this email
thread
> is that some "test" applicaiton works fine on the device, but when ISQLWCE
> is used, a different behavior is exhibited.
>
> Can you clarify for me?
>
> thx.
>
>
>
> "John Kendrick" <jkendrick@DONTneo.SPAMrr.com> wrote in message
> news:ODfyYASvEHA.4048@TK2MSFTNGP15.phx.gbl...
> >I created a Pull Subscription (using all the default settings) and ran a
> >few
> > test INSERTs. The tests showed that this subscription is behaving
> > normally.
> > That is the first INSERT created a row id of 501001, then a standard
> > synchronization and finally another INSERT which created a row id
501002.
> > This is what I expect since the identity range was not used up on the
> > subscription database.
> >
> > I also ran the exact same test using the CE Query Analyzer and it failed
> > to
> > INSERT with the 25081 error.
> >
> >
> > "Kevin Collins (MS - SQL Mobile)" <kevincol@online.microsoft.com> wrote
in
> > message news:ugWkVGEvEHA.1264@TK2MSFTNGP12.phx.gbl...
> >> Another quick test would be to use Enterprise Manager to quickly create
a
> >> SQL Server subscription database. This would help narrow down if there
> >> is
> >> something at issue with the publication or if something is wrong with
SQL
> >> Server CE. Can you try creating a SQL Server subscription database and
> >> verify if the problems exist there also?
> >>
> >> thx.
> >>
> >> "John Kendrick" <jkendrick@DONTneo.SPAMrr.com> wrote in message
> >> news:%233hburquEHA.3624@TK2MSFTNGP09.phx.gbl...
> >> > Kevin,
> >> >
> >> > First, appreciate your help. I tried compacting the database after
the
> >> > synchronization and the error is still present.
> >> >
> >> > I started using the profiler to examine the synchronization.
> >> >
> >> > Prior to create I checked the publication's Identity Range Tab for
one
> > of
> >> > the failing tables (last_product_sel):
> >> > Identity Tab show next range value 437000
> >> >
> >> > Creating a new database (log record from SQL Profiler):
> >> > declare @P1 bigint
> >> > set @P1=438000
> >> > declare @P2 bigint
> >> > set @P2=1000
> >> > declare @P3 int
> >> > set @P3=80
> >> > exec
> >> >
> >> > "distribution"."dbo"."sp_MSfetchAdjustidentityrange";1 @publisher =
> >> > N'SERVER02', @publisher_db = N'testdb',
> >> >
> >> > @tablename = N'last_product_sel', @adjust_only = 1, @next_seed = @P1
> >> > output,
> >> > @range = @P2 output, @threshold = @P3
> >> >
> >> > output
> >> > select @P1, @P2, @P3
> >> >
> >> > After the create I checked the publication's Identity Range Tab for
one
> > of
> >> > the failing tables (last_product_sel):
> >> > Identity Tab show next range value 438000
> >> > At this point I can INSERT new rows.
> >> >
> >> > Synchronized the database (log record from SQL Profiler):
> >> > declare @P1 bigint
> >> > set @P1=439000
> >> > declare @P2 bigint
> >> > set @P2=1000
> >> > declare @P3 int
> >> > set @P3=80
> >> > exec
> >> >
> >> > "distribution"."dbo"."sp_MSfetchAdjustidentityrange";1 @publisher =
> >> > N'SERVER02', @publisher_db = N'testdb',
> >> >
> >> > @tablename = N'last_product_sel', @adjust_only = 1, @next_seed = @P1
> >> > output,
> >> > @range = @P2 output, @threshold = @P3
> >> >
> >> > output
> >> > select @P1, @P2, @P3
> >> >
> >> > After the synchronize I checked the publication's Identity Range Tab
> >> > for
> >> > one
> >> > of the failing tables (last_product_sel):
> >> > Identity Tab show next range value 439000
> >> >
> >> > The INSERTs now fail with the 25081 error when trying to insert a
> >> > record
> >> > with a generated value of 439000.
> >> >
> >> > Since I only inserted the one record after the create, the threshold
> >> > for
> >> > the
> >> > identity range was not been met, yet during the synchronization a new
> >> > range
> >> > was allocated. I think this is a configuration problem on the server.
> >> > Could
> >> > you recommend tables to examine and additional items to watch in the
> >> > profiler.
> >> >
> >> >
> >> > "Kevin Collins (MS - SQL Mobile)" <kevincol@online.microsoft.com>
wrote
> > in
> >> > message news:eH6T%23LFuEHA.2800@tk2msftngp13.phx.gbl...
> >> >> Compacting the database will not move the identity range up. So you
> >> >> are
> >> > safe
> >> >> to compact the database.
> >> >>
> >> >> You should not need to compact the database after every
> > synchronization.
> >> >> I'm just trying to eliminate any potential that there is something
> > messed
> >> > up
> >> >> in one of the internal tracking tables. If that doesn't solve the
> >> >> problem
> >> >> then we can investigate other areas.
> >> >>
> >> >>
> >> >> "John Kendrick" <jkendrick@DONTneo.SPAMrr.com> wrote in message
> >> >> news:%23plz3q5tEHA.200@TK2MSFTNGP11.phx.gbl...
> >> >> > This happens on all the databases that I synchronize (using the
.NET
> >> >> > application, the eVB application works fine). Also pattern occurs
> > after
> >> >> > every synchronization I do that doesn't use the
> >> >> > ReinitializeSubscription().
> >> >> >
> >> >> > I have never attempted to compact the database. If I compact the
> >> > database
> >> >> > doesn't that change next identity to the first available number?
For
> >> >> > example
> >> >> > I range an Identity Range allocated from the server that is 505000
> >> >> > to
> >> >> > 506000. After compacting the next identity could be 504007 since I
> >> > didn't
> >> >> > use that entire range before and I would still get an error
because
> > the
> >> >> > identity is below the allocated range.
> >> >> >
> >> >> > Since this error occurs on the first insert to a table with an
> > Identity
> >> >> > column, it sounds like I should compact the database after every
> >> >> > synchronization?
> >> >> >
> >> >> >
> >> >> > "Kevin Collins (MS - SQL Mobile)" <kevincol@online.microsoft.com>
> > wrote
> >> > in
> >> >> > message news:e9L5o54tEHA.2808@TK2MSFTNGP14.phx.gbl...
> >> >> >> Does this happen just on this particular database or does it
happen
> >> >> >> all
> >> >> > the
> >> >> >> time after doing subsequent syncs? Also have you tried
compacting
> > the
> >> >> >> database after you get this error to see if that might resolve
the
> >> >> > problem.
> >> >> >>
> >> >> >> "John Kendrick" <jkendrick@DONTneoSPAMrr.com> wrote in message
> >> >> >> news:eO4wz1ttEHA.2624@TK2MSFTNGP11.phx.gbl...
> >> >> >> > I'm receiving this error from SSCE 2.0 when trying to insert a
> >> >> >> > new
> >> > row.
> >> >> >> >
> >> >> >> > Error Code: 80004005
> >> >> >> > Message: The new value generated for the inserted row does not
> > fall
> >> >> > within
> >> >> >> > the allowable range. [Generated value,,,,,]
> >> >> >> > Minor err: 25081
> >> >> >> > Source: Microsoft SQL Server 2000 Windows CE Edition
> >> >> >> > Num. Par.: 505000
> >> >> >> >
> >> >> >> > I checked the SQL Server Publication Properties -> Articles ->
> >> >> >> > Indentity
> >> >> >> > Range and noticed that the "Next range starting value" is
505000.
> >> >> > Whenever
> >> >> >> > I
> >> >> >> > synchronize the "Next range starting value" is increased by
1000
> > and
> >> > if
> >> >> > I
> >> >> >> > insert a new row into that table on the PPC, I get this error
> > where
> >> > the
> >> >> >> > Num.
> >> >> >> > Par. matches the "Next range starting value". The only way I
> >> >> >> > found
> >> >> >> > to
> >> >> > get
> >> >> >> > around this error is to re-create the database or call
> >> >> >> > ReinitializeSubscription.
> >> >> >> >
> >> >> >> > Does anyone have any thoughts on why this is occuring.
> >> >> >> >
> >> >> >> > Some additional information:
> >> >> >> > The tables that the handheld will inserting value with Identity
> >> > columns
> >> >> >> > marked as NOT FOR REPLICATION. In the publication, the tables
> >> > Identity
> >> >> >> > Range
> >> >> >> > tab has the "Automatically assign and maintain a unique
identity
> >> >> > range..."
> >> >> >> > checked.
> >> >> >> >
> >> >> >> > This is an eVB application that is being rewritten to run under
> > the
> >> >> >> > .NET
> >> >> >> > platform. The eVB application continues to functions properly.
> >> >> >> >
> >> >> >> > When running the INSERT in the PPC Query Analyzer, I get the
> >> >> >> > identity
> >> >> >> > error
> >> >> >> > that the .NET application produces.
> >> >> >> >
> >> >> >> > There is a difference in the method of insert between the eVB
> >> >> > application
> >> >> >> > and the .NET application. The eVB uses an ADOCE.Recordset while
> > the
> >> > new
> >> >> >> > application is using straight SQL statements.
> >> >> >> >
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
> >
>
>

Relevant Pages

  • RE: full-text search failed with one million of key words (Sqlserv
    ... Research with "toto" was just a test. ... In real life, my database contains ... for only one book) And the table i want to query looks like this: ... I have to make big tests with sql server 2005 express (i'm waiting credits ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Looking for a professional SQL programmer for a small job
    ... Pro SQL Server 2000 Database Design - ... I have two queries to build and while I know my way around SQL, ... >> One is a seach query that pull rental properties from a database based on ...
    (microsoft.public.sqlserver.programming)
  • Re: Using a worksheet as a table in an SQL query
    ... I'll probably just dump the SQL Server data to a spreadsheet and join the two spreadsheets. ... You could use Access to link to both sources and create a crosstab query. ... There are ways to enter the data into their database via the application, but it would require them to go to many different windows instead of being able to enter the data all on one screen. ... There is not a great deal of data they would enter on the spreadsheet, but I'm not sure how slow that would make the query. ...
    (microsoft.public.excel.programming)
  • Re: Issue with a table that is 75%-80% of the database size
    ... SQL Server 2000 does not support table partitioning. ... Have you tried database differential backup? ... your query response time and backup time. ...
    (microsoft.public.sqlserver.server)
  • Re: Time out error synchronizing in one merge push subscriber
    ... The synchronization operation of a merge replication times out ... I've a Merge Replication with SQL Server 2005. ... subscribers, using push subscriptions. ... because the query timed out. ...
    (microsoft.public.sqlserver.replication)