Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Replication / September 2003

Tip: Looking for answers? Try searching our database.

Biggest Scenario (Very imp scenario for all of u, u must aware of this....problem in sql server 2000)

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Asad Imam - 21 Sep 2003 07:22 GMT
Biggest Problem (Very imp scenario for all of u, u must aware of
this....problem in sql server 2000)

Here I have a different scenario in Merge Replication using SQL SERVER 2000
on Win2000 Server/Win 2000Adv Server/NtServer (all)

DB1.Table1
Identity Seed            =1
Identity Increment    =2
(auto number should be generated as odd numbers)
Assume this data bydefault
Column1 (AutoNo)       Column2 (any char datatype)
1                                    A1
3                                    A3

DB2.Table2
Identity Seed            =2
Identity Increment    =2
(auto number should be generated as even numbers)
Assume this data bydefault
Column1 (AutoNo)       Column2 (any char datatype)
2                                    B1
4                                    B3

After Merge Replication, I received the following output both for Table1 and
Table2 which is fine
DB1.Table1 (1,2)
Column1 (AutoNo)       Column2 (any char datatype)
1                                    A1
3                                    A3
2                                    B1
4                                    B3

DB2.Table2 (2,2)
Column1 (AutoNo)       Column2 (any char datatype)
1                                    A1
3                                    A3
2                                    B1
4                                    B3
Here is a question :
Now, I add some data at DB2.Table2. Note down next identity value in Column1
DB2.Table2 (2,2)
Column1 (AutoNo)       Column2 (any char datatype)
1                                    A1
3                                    A3
2                                    B1
4                                    B3
5                                    B4

Here, according to identity seed=2 and identity increment=2
Why I am receiving another id=5. However I was expecting it to be 6 b/c I
have identified seed=2 and increment=2(even number not an odd number).....

Thanks and Regards
Asad Imam
Pakistan.
Kestas - 22 Sep 2003 09:40 GMT
I think reseed of identity values using "DBCC CHECKIDENT" should help you.
I am using following stored procedure to reseed all db tables.

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sp_ReseedIdentity]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[sp_ReseedIdentity]
GO

CREATE  PROCEDURE dbo.sp_ReseedIdentity
@even bit
AS
BEGIN

SET NOCOUNT ON
SET IMPLICIT_TRANSACTIONS OFF

DECLARE l_hSqlCursor CURSOR LOCAL FAST_FORWARD
FOR  (
 SELECT '
  DECLARE @CURR_IDENT INT
  SELECT @CURR_IDENT =
  CASE WHEN ISNULL( MAX( ' + C.NAME + ' ), 0 ) % 2 = 0
    THEN ISNULL( MAX( ' + C.NAME + ' ), 0 )' +
  CASE WHEN @even = 1
   THEN ' + CASE WHEN ISNULL( MAX( ' + C.NAME + ' ), 0 ) = 0 THEN 2 ELSE
MAX( ' + C.NAME + ' ) END'
   ELSE ' + 1'
  END + '
   ELSE ISNULL( MAX( ' + C.NAME + ' ), 0 )' +
  CASE WHEN @even = 1
   THEN ' + 1'
   ELSE ''
  END + '
  END
  FROM '+  U.NAME + '.' + O.NAME
  + ' DBCC CHECKIDENT( '''
  + U.NAME + '.' + O.NAME
  + ''', RESEED, @CURR_IDENT )'
 FROM SYSOBJECTS O
  JOIN SYSCOLUMNS C
   ON O.ID = C.ID
  JOIN SYSUSERS U
   ON OBJECTPROPERTY( O.ID, 'OWNERID' ) = U.UID
 WHERE U.NAME != 'dbo'
 AND O.XTYPE = 'U'
 AND COLUMNPROPERTY( O.ID, C.NAME, 'IsIdentity' ) = 1
 )

-- Declare FETCH variables
DECLARE @RESEED_CMD VARCHAR(500)

-- Open SQL cursor
OPEN l_hSqlCursor

-- SQL variables
DECLARE @l_nError INT
SET @l_nError = 0

WHILE ( @l_nError = 0 )
BEGIN
 -- Fetch next record
 FETCH NEXT
 FROM l_hSqlCursor
 INTO @RESEED_CMD

 IF (@@FETCH_STATUS <> 0 ) -- handle errors
 BEGIN
  IF ( @@ERROR = 0 )
   PRINT 'Procedure succeeded'
  ELSE
   PRINT 'Procedure failed'
  BREAK
 END

 --------------------------------

 -- Do processing
 EXEC ( @RESEED_CMD )
 --PRINT ( @RESEED_CMD )

 --------------------------------

 IF ( @@ERROR <> 0 )
 BEGIN
  PRINT @@ERROR
  BREAK
 END

END

CLOSE l_hSqlCursor
DEALLOCATE l_hSqlCursor

SET IMPLICIT_TRANSACTIONS ON
SET NOCOUNT OFF

END
GO

Signature

_______________________
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
E-mail: Kestutis.Adomavicius@bss.biz
Phone:  +370 69911787

> Biggest Problem (Very imp scenario for all of u, u must aware of
> this....problem in sql server 2000)
[quoted text clipped - 52 lines]
> Asad Imam
> Pakistan.
Kestas - 22 Sep 2003 11:29 GMT
One more remark. This sp is reseeding only tables owned not by dbo, but it
can be changed of course :)

Signature

_______________________
Kestutis Adomavicius

Tom Michaels \(MSFT\) - 23 Sep 2003 01:45 GMT
A couple of additional pieces of information would be helpful.  When
defining the merge article are you using the @auto_identity_range set to
'true' or 'false' (default)?  When defining the merge subscription, are you
using @sync_type='none' or 'automatic' (default).

Based on what I see below, the following should work to set up odd values on
the publisher and even values on the subscriber.  A possible solution
requires you to manually manage the identity values on both sides and it
will require the merge subscription to use the No Sync feature --
@sync_type='none'.

Create your DB1 (publisher) and DB2 (subscriber) databases.  Create the
table in both databases, identity (1,2) in DB1 and identity (2,2) in DB2.
Make sure you also define the Rowguid column in both tables as well.  This
is required by merge.

create table t1 (
   Column1 int identity (1,2) NOT FOR REPLICATION,
   Column2 varchar(100),
   rowguid  uniqueidentifier ROWGUIDCOL  NOT NULL CONSTRAINT
[DF__t1__rowguid__74AE54BC] DEFAULT (newid()))
go

It looks like the merge subscription in your original scenario is set to
'automatic', so the merge agent will reseed the subscriber upon the first
merge while applying the initial snapshot.  You need to use
@sync_type='none' for the merge subscription.  This will prevent the merge
agent from reapplying the publisher table at the subscriber and thus
maintain your identity (2,2) on the subscriber.  You need to initially
populate the subscriber database.

A drawback to watch out for is that you will have a difficult time adding
more subscribers to your replicaiton topolgy because only odd and even
numbers are allowed.  If more subscribers are needed, you may need to break
this down and start over.

Another drawback is that the @sync_type option is set at the subscription
level.  This means all articles in the publication pointed to that
subscription are in No Sync mode.  You would have to initially sync all
articles manually.  A workaround for this would be to create two
publications, one with your identity table using a No Sync subscription and
another publication with all other articles that sync automatically.

Please read through the following topic in SQL 2000 BOL -- "Managing
Identity Values".  If you can conform to the requirements of the automatic
identity range management it may be a better solution for you going forward.
Instead of using odd and even numbers, your would use different ranges of
identity values on the publisher and subscriber.

Signature

Sincerely,
Tom Michaels
Microsoft Corporation

This posting is provided "AS IS" with no warranties, and confers no rights.

> Biggest Problem (Very imp scenario for all of u, u must aware of
> this....problem in sql server 2000)
[quoted text clipped - 52 lines]
> Asad Imam
> Pakistan.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.