[MSrepl_tran_version_default_F6FB929A_624E_4666_919D_08145657CCE8_2073058421]DEFAULT (newid()),PRIMARY KEY CLUSTERED ([pkcol] ASC )WITH (IGNORE_DUP_KEY = OFF) ON[PRIMARY]) ON [PRIMARY] GO4 columns.Please check the tables after the several inserts, updates and deletes haveflowed. You will notice that there are no additional columns.I am sorry you feel this way about the MVP program. I always strive foraccuracy, and normally repro everything before I make a post.The attached is tested on SQL 2000 and 2005. Note that for SQL 2005 the procnames will have to be of the form'sp_MSins_dbotbl_IssueTracker_I', insteadof the truncated versions that they are for SQL 2000.--Hilary CotterLooking for a SQL Server replication book?http://www.nwsu.com/0974973602.htmlLooking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com"Hilary Cotter" <hilary.cot***@gmail.com> wrote in messagenews:upE6T6NFGHA.2***@TK2MSFTNGP11.phx.gbl...> Your repro is for an immediate updating publication, which does require> schema modifications. Right click on the publication select properties and> in the updateable tab it will clearly say immediate on SQL 2000, on SQL2005> the Subscriptions Option tab will say Allow Immediate Updating> Subscriptions - true. The subscription is not an immedate updating> subscription however. This kb article is misleading and I will file a doc> bug on it.>> Notice that before the snapshot runs your table has>> create table two_way_test1>> (pkcol INTEGER PRIMARY KEY NOT NULL,>> intcol INTEGER IDENTITY(1,1) NOT FOR REPLICATION,>> charcol CHAR(100))>> a total of 3 columns.  After it has run it has>> CREATE TABLE [dbo].[two_way_test1](>> [pkcol] [int] NOT NULL,>> [intcol] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,>> [charcol] [char](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,>> [msrepl_tran_version] [uniqueidentifier] NOT NULL CONSTRAINT>[MSrepl_tran_version_default_F6FB929A_624E_4666_919D_08145657CCE8_2073058421] DEFAULT (newid()), PRIMARY KEY CLUSTERED ([pkcol] ASC )WITH(IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY] GO4 columns.Please checkthe tables after the several inserts, updates and deletes haveflowed. Youwill notice that there are no additional columns.I am sorry you feel thisway about the MVP program. I always strive foraccuracy, and normally reproeverything before I make a post.The below is tested on SQL 2000 and2005.Note that for SQL 2005 the proc names will have to be of theform'sp_MSins_dbotbl_IssueTracker_I', instead of the truncated versionsthatthey are for SQL 2000.create database bidi1GOcreate database bidi2GOUSEBIDI1goCREATE TABLE [dbo].[tbl_IssueTracker_IssueCategories] ([category_id][int] IDENTITY (10000, 1) NOT FOR REPLICATION  NOT NULL ,[name] [varchar](100)  NOT NULL ,[parent_category_id] [int] NOT NULL) ON [PRIMARY]GOALTERTABLE [dbo].[tbl_IssueTracker_IssueCategories] WITH NOCHECK ADDCONSTRAINT[PK_tbl_IssueTracker_IssueCategories] PRIMARY KEY  CLUSTERED([category_id])ON [PRIMARY]GOsp_replicationdboption'bidi1','publish','true'GOsp_addpublication'bidi1',@status='active'GOsp_addpublication_snapshot'bidi1'GOsp_addarticle'bidi1','tbl_IssueTracker_IssueCategories','tbl_IssueTracker_IssueCategories'GOsp_addsubscription 'bidi1',@SUBSCRIBER=@@servername, @destination_db='bidi2',@loopback_detection ='true', @sync_type= noneGOif exists (select * from sysobjects where type ='P' and name ='sp_MSins_tbl_IssueTracker_Issu')  dropproc[sp_MSins_tbl_IssueTracker_Issu]gocreate procedure[sp_MSins_tbl_IssueTracker_Issu] @c1 int,@c2varchar(100),@c3intASBEGINinsert into [tbl_IssueTracker_IssueCategories]([category_id],[name], [parent_category_id] )values (@c1, @c2, @c3 )ENDgoif exists (select* from sysobjects where type = 'P' and name='sp_MSupd_tbl_IssueTracker_Issu')  dropproc[sp_MSupd_tbl_IssueTracker_Issu]gocreate procedure[sp_MSupd_tbl_IssueTracker_Issu] @c1 int,@c2 varchar(100),@c3 int, @pkc1intasif @c1 = @pkc1beginupdate [tbl_IssueTracker_IssueCategories] set [name]=@c2,[parent_category_id] = @c3where [category_id] = @pkc1endelsebeginupdate[tbl_IssueTracker_IssueCategories] set --[category_id] = @c1,[name] =@c2,[parent_category_id] = @c3where [category_id] = @pkc1endgoif exists(select * from sysobjects where type = 'P' and name='sp_MSdel_tbl_IssueTracker_Issu')  dropproc[sp_MSdel_tbl_IssueTracker_Issu]gocreate procedure[sp_MSdel_tbl_IssueTracker_Issu] @pkc1 intasdelete[tbl_IssueTracker_IssueCategories]where [category_id] = @pkc1gousebidi2GOCREATE TABLE [dbo].[tbl_IssueTracker_IssueCategories] ([category_id][int] IDENTITY (20000, 1) NOT FOR REPLICATION  NOT NULL ,[name] [varchar](100)  NOT NULL ,[parent_category_id] [int] NOT NULL) ON [PRIMARY]GOALTERTABLE [dbo].[tbl_IssueTracker_IssueCategories] WITH NOCHECK ADDCONSTRAINT[PK_tbl_IssueTracker_IssueCategories] PRIMARY KEY  CLUSTERED([category_id])ON [PRIMARY]GOsp_replicationdboption'bidi2','publish','true'GOsp_addpublication'bidi1',@status='active'GOsp_addpublication_snapshot'bidi1'GOsp_addarticle'bidi1','tbl_IssueTracker_IssueCategories','tbl_IssueTracker_IssueCategories'GOsp_addsubscription 'bidi1',@SUBSCRIBER=@@servername, @destination_db='bidi1',@loopback_detection ='true', @sync_type= noneGOif exists (select * from sysobjects where type ='P' and name ='sp_MSins_tbl_IssueTracker_Issu')  dropproc[sp_MSins_tbl_IssueTracker_Issu]gocreate procedure[sp_MSins_tbl_IssueTracker_Issu] @c1 int,@c2varchar(100),@c3intASBEGINinsert into [tbl_IssueTracker_IssueCategories]([category_id],[name], [parent_category_id] )values (@c1, @c2, @c3 )ENDgoif exists (select* from sysobjects where type = 'P' and name='sp_MSupd_tbl_IssueTracker_Issu')  dropproc[sp_MSupd_tbl_IssueTracker_Issu]gocreate procedure[sp_MSupd_tbl_IssueTracker_Issu] @c1 int,@c2 varchar(100),@c3 int,@pkc1intasif @c1 = @pkc1beginupdate [tbl_IssueTracker_IssueCategories] set [name]=@c2,[parent_category_id] = @c3where [category_id] = @pkc1endelsebeginupdate[tbl_IssueTracker_IssueCategories] set --[category_id] = @c1,[name] =@c2,[parent_category_id] = @c3where [category_id] = @pkc1endgoif exists(select * from sysobjects where type = 'P' and name='sp_MSdel_tbl_IssueTracker_Issu')  dropproc[sp_MSdel_tbl_IssueTracker_Issu]gocreate procedure[sp_MSdel_tbl_IssueTracker_Issu] @pkc1 intasdelete[tbl_IssueTracker_IssueCategories]where [category_id] = @pkc1gousebidi1GOinsert into tbl_IssueTracker_IssueCategories(name,parent_category_id)values('test',2)insert intobidi2.dbo.tbl_IssueTracker_IssueCategories(name,parent_category_id)values('test',2)insert into tbl_IssueTracker_IssueCategories(name,parent_category_id)values('test',2)insert intobidi2.dbo.tbl_IssueTracker_IssueCategories(name,parent_category_id)values('test',2)insert into tbl_IssueTracker_IssueCategories(name,parent_category_id)values('test',2)insert intobidi2.dbo.tbl_IssueTracker_IssueCategories(name,parent_category_id)values('test',2)GO--note here that there are 3 columns stillselect * fromtbl_IssueTracker_IssueCategoriesselect * frombidi2.dbo.tbl_IssueTracker_IssueCategoriesGOupdatetbl_IssueTracker_IssueCategories set name='publisher'wherecategory_id>20000update bidi2.dbo.tbl_IssueTracker_IssueCategoriessetname='subscriber'  where category_id<20000GOselect * fromtbl_IssueTracker_IssueCategoriesselect * frombidi2.dbo.tbl_IssueTracker_IssueCategoriesGOdelete fromtbl_IssueTracker_IssueCategories where name='subscriber'delete frombidi2.dbo.tbl_IssueTracker_IssueCategories wherename='publisher'GOselect *from tbl_IssueTracker_IssueCategoriesselect * frombidi2.dbo.tbl_IssueTracker_IssueCategoriesGO--Hilary CotterLooking for a SQLServer replication book?http://www.nwsu.com/0974973602.htmlLooking for a FAQon Indexing Services/SQL FTShttp://www.indexserverfaq.com"Michael Hotek"<m***@solidqualitylearning.com> wrote inmessagenews:uTc%23afMFGHA.1***@TK2MSFTNGP14.phx.gbl...> Hilary,> I wouldsuggest that you exit these newsgroups right now.  No schemachange is aboutas false of a statement as you can ever possibly get.  Itrequires that youenable the @loopback_detection parameter and the loopbackdetection algorithmuses the additional column to determine the originatorof the change.  TheMVP program has sunk really low when it has members whoknowingly postblatantly false answers.>> The create table statement that I used toinitially generate the table is:> create table two_way_test1>> (pkcolINTEGER PRIMARY KEY NOT NULL,>> intcol INTEGER IDENTITY(1,1) NOT FORREPLICATION,>> charcol CHAR(100))>>> The table structure after the snapshotcompleted was as follows:> USE [test1]>> GO>> /****** Object: Table[dbo].[two_way_test1] Script Date: 01/08/200620:43:26 ******/>> SETANSI_NULLS ON>> GO>> SET QUOTED_IDENTIFIER ON>> GO>> SET ANSI_PADDING ON>>GO>> CREATE TABLE [dbo].[two_way_test1](>> [pkcol] [int] NOT NULL,>>[intcol] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,>> [charcol][char](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,>>[msrepl_tran_version] [uniqueidentifier] NOT NULLCONSTRAINT[MSrepl_tran_version_default_F6FB929A_624E_4666_919D_08145657CCE8_2073058421] DEFAULT (newid()),>> PRIMARY KEY CLUSTERED>> ([pkcol]ASC>> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]>> GO>> SETANSI_PADDING OFF>> GO>> USE [test1]>> GO>> ALTER TABLE [dbo].[two_way_test1]WITH NOCHECK ADD CONSTRAINT[checkprimcol] CHECK NOT FOR REPLICATION(([pkcol]>=(1) AND[pkcol]<=(1000)))>> GO>> ALTER TABLE [dbo].[two_way_test1]CHECK CONSTRAINT [checkprimcol]>> Maybe you can explain how adding a columnto a table doesn't qualify asmodifying the schema?>>http://support.microsoft.com/default.aspx?scid=820675>> Here is the code Iused to implement bi-directional, transactionalreplication.  (Straight outof the KB article.)>> use master>> go>> create database test1>> go>> createdatabase test2>> go>> use test1>> go>> create table two_way_test1>> (>>pkcol INTEGER PRIMARY KEY NOT NULL,>> intcol INTEGER IDENTITY(1,1) NOT FORREPLICATION,>> charcol CHAR(100)>> )>> use test2>> go>> create tabletwo_way_test2>> (>> pkcol INTEGER PRIMARY KEY NOT NULL,>> intcol INTEGERIDENTITY(1000000000,1) NOT FOR REPLICATION,>> charcol CHAR(100)>> )>>go>> -- Constraint to enforce a range of values between 1 and 1000 indatabasetest1>> use test1>> go>> alter table>> two_way_test1>> withnocheck>> add constraint>> checkprimcol check NOT FOR REPLICATION>> (>>pkcol BETWEEN 1 and 1000>> )>> go>>>> use test2>> go>> -- Constraint toenforce a range of values between 1001 and 2000 in thedatabase test2>> altertable>> two_way_test2>> with nocheck>> add constraint>> checkprimcol checkNOT FOR REPLICATION>> (>> pkcol BETWEEN 1001 and 2000>> )>> go>> use test1>>go>> -- INSERT Stored Procedure>> create procedure sp_ins_two_way_test1>>@pkcol int,>> @intcol int,>> @charcol char(100),>> @timestampcoltimestamp,>> @rowidcol uniqueidentifier>> as>> insert into two_way_test1>>(>> pkcol,>> intcol,>> charcol>> )>> values>> (>> @pkcol,>> @intcol,>>@charcol>> )>> go>> --UPDATE Stored Procedure>> create proceduresp_upd_two_way_test1>> @pkcol int,>> @intcol int,>> @charcol char(100),>>@timestampcol timestamp,>> @rowidcol uniqueidentifier,>> @old_pkcol int>>as>> declare @x int>> declare @y int>> declare @z char(100)>> select>>@x=pkcol,>> @y=intcol,>> @z=charcol>> from>> two_way_test1>> where>> pkcol =@pkcol>>>>>> delete>> two_way_test1>> where>> pkcol=@pkcol>>>>>> insert intotwo_way_test1>> (>> pkcol,>> intcol,>> charcol>> )>> values>> (>> caseisnull(@pkcol,0) when 0 then @x else @pkcol end,>> case isnull(@intcol,0)when 0 then @y else @intcol end,>> case isnull(@charcol,'N') when 'N' then@z else @charcol end>> )>> go>> -- DELETE Stored Procedure>> createprocedure sp_del_two_way_test1>> @old_pkcol int>> as>> delete>>two_way_test1>> where>> pkcol = @old_pkcol>> go>> use test2>> go>> -- INSERTStored Procedure>> create procedure sp_ins_two_way_test2>> @pkcol int,>>@intcol int,>> @charcol char(100),>> @timestampcol timestamp,>> @rowidcoluniqueidentifier>> as>> insert into two_way_test2>> (>> pkcol,>> intcol,>>charcol>> )>> values>> (>> @pkcol,>> @intcol,>> @charcol>> )>> go>> --UPDATEStored Procedure>> create procedure sp_upd_two_way_test2>> @pkcol int,>>@intcol int,>> @charcol char(100),>> @timestampcol timestamp,>> @rowidcoluniqueidentifier,>> @old_pkcol int>> as>> declare @x int>> declare @y int>>declare @z char(100)>> select>> @x=pkcol,>> @y=intcol,>> @z=charcol>> from>>two_way_test2>> where>> pkcol = @pkcol>>>>>> delete>> two_way_test2>>where>> pkcol=@pkcol>>>>>> insert into two_way_test2>> (>> pkcol,>>intcol,>> charcol>> )>> values>> (>> case isnull(@pkcol,0) when 0 then @xelse @pkcol end,>> case isnull(@intcol,0) when 0 then @y else @intcol end,>>case isnull(@charcol,'N') when 'N' then @z else @charcol end>> )>> go>>>> --DELETE Stored Procedure>> create procedure sp_del_two_way_test2>> @old_pkcolint>> as>> delete>> two_way_test2>> where>> pkcol = @old_pkcol>> go>> usemaster>> go>> exec sp_replicationdboption N'test1', N'publish', true>> go>>exec sp_replicationdboption N'test2', N'publish', true>> go>> --In thedatabase test1.>> use test1>> go>> -- Adding the transactionalpublication.>> exec sp_addpublication>> @publication =N'two_way_pub_test1',>> @restricted = N'false',>> @sync_method =N'native',>> @repl_freq = N'continuous',>> @description = N'Transactionalpublication of database test1.',>> @status = N'active',>> @allow_push =N'true',>> @allow_pull = N'true',>> @allow_anonymous = N'false',>>@enabled_for_internet = N'false',>> @independent_agent = N'false',>>@immediate_sync = N'false',>> @allow_sync_tran = N'true',>>@autogen_sync_procs = N'true',>> @retention = 72>> go>> execsp_addpublication_snapshot>> @publication = N'two_way_pub_test1',>>@frequency_type = 4,>> @frequency_interval = 1,>>@frequency_relative_interval = 0,>> @frequency_recurrence_factor = 1,>>@frequency_subday = 1,>> @frequency_subday_interval = 0,>>@active_start_date = 0,>> @active_end_date = 0,>> @active_start_time_of_day= 233000,>> @active_end_time_of_day = 0>> go>> -- Adding the transactionalarticles.>> exec sp_addarticle>> @publication = N'two_way_pub_test1',>>@article = N'two_way_test1',>> @source_owner = N'dbo',>> @source_object =N'two_way_test1',>> @destination_table = N'two_way_test1',>> @type =N'logbased',>> @creation_script = null,>> @description = null,>>@pre_creation_cmd = N'drop',>> @schema_option = 0x00000000000000F1,>>@status = 16,>> @vertical_partition = N'false',>> @ins_cmd = N'CALLsp_ins_two_way_test2',>> @del_cmd = N'CALL sp_del_two_way_test2',>> @upd_cmd= N'CALL sp_upd_two_way_test2',>> @filter = null,>> @sync_object = null,>>@identityrangemanagementoption = 'manual'>> go>> -- In the database test2>>use test2>> go>> -- Adding the transactional publication.>> execsp_addpublication>> @publication = N'two_way_pub_test2',>> @restricted =N'false',>> @sync_method = N'native',>> @repl_freq = N'continuous',>>@description = N'Transactional publication of database test2',>> @status =N'active',>> @allow_push = N'true',>> @allow_pull = N'true',>>@allow_anonymous = N'false',>> @enabled_for_internet = N'false',>>@independent_agent = N'false',>> @immediate_sync = N'false',>>@allow_sync_tran = N'true',>> @autogen_sync_procs = N'true',>> @retention =72>> go>> exec sp_addpublication_snapshot>> @publication =N'two_way_pub_test2',>> @frequency_type = 4,>> @frequency_interval = 1,>>@frequency_relative_interval = 0,>> @frequency_recurrence_factor = 1,>>@frequency_subday = 1,>> @frequency_subday_interval = 0,>>@active_start_date = 0,>> @active_end_date = 0,>> @active_start_time_of_day= 233000,>> @active_end_time_of_day = 0>> go>> -- Adding the transactionalarticles.>> exec sp_addarticle>> @publication = N'two_way_pub_test2',>>@article = N'two_way_test2',>> @source_owner = N'dbo',>> @source_object =N'two_way_test2',>> @destination_table = N'two_way_test2',>> @type =N'logbased',>> @creation_script = null,>> @description = null,>>@pre_creation_cmd = N'drop',>> @schema_option = 0x00000000000000F1,>>@status = 16,>> @vertical_partition = N'false',>> @ins_cmd = N'CALLsp_ins_two_way_test1',>> @del_cmd = N'CALL sp_del_two_way_test1',>> @upd_cmd= N'CALL sp_upd_two_way_test1',>> @filter = null,>> @sync_object = null,>>@identityrangemanagementoption = 'manual'>> go>> use test1>> go>> execsp_addsubscription>> @publication = N'two_way_pub_test1',>> @article =N'all',>> @subscriber = 'WAKKO',>> @destination_db = N'test2',>> @sync_type= N'none',>> @status = N'active',>> @update_mode = N'sync tran',>>@loopback_detection = 'true'>> go>> -- Adding the transactional subscriptionin test2.>> use test2>> go>> exec sp_addsubscription>> @publication =N'two_way_pub_test2',>> @article = N'all',>> @subscriber = 'WAKKO',>>@destination_db = N'test1',>> @sync_type = N'none',>> @status = N'active',>>@update_mode = N'sync tran',>> @loopback_detection = 'true'>> go>>> -->Mike> Mentor> Solid Quality Learning> http://www.solidqualitylearning.com>>>"Hilary Cotter" <hilary.cot***@gmail.com> wrote inmessagenews:%238PEHtKFGHA.***@TK2MSFTNGP15.phx.gbl...>> Bi-directionaltransactional replication requires no schema changes.>>>> -->> HilaryCotter>> Looking for a SQL Server replication book?>>http://www.nwsu.com/0974973602.html>>>> Looking for a FAQ on IndexingServices/SQL FTS>> http://www.indexserverfaq.com>>>> "Michael Hotek"<m***@solidqualitylearning.com> wrote inmessagenews:uJnQlhtEGHA.3***@tk2msftngp13.phx.gbl...>>> Log shipping can beas little as 3 - 5 minutes of latency.  Clusteringis only going to protectyou from hardware failure.>>>>>> Any replication method which sends data inboth directions will requirea schema change, there is absolutely no wayaround this.>>>>>> Database Mirroring in 2005 is the only technology whichexists whichwill allow you to maintain 2 databases synchronized with eachother, in realtime, without requiring a schema change.  BUT, the mirrordatabase isinaccessible and you will incur a performance penalty in yourapplicationswhen you do this.>>>>>> -->>> Mike>>> Mentor>>> Solid QualityLearning>>> http://www.solidqualitylearning.com>>>>>>>>> "Paul Ibison"<Paul.Ibi***@Pygmalion.Com> wrote inmessagenews:eZw9LhsEGHA.3***@TK2MSFTNGP12.phx.gbl...>>>> Jeff,>>>>log-shipping and transactional replication will both have latency. Onmysystems this can often be minimised in the case of transactionalreplicationto < 10 secs, but the data will usually be out of sync. Youmight beinterested in investigating synchronous database mirroring, orclustering toensure high-availability.>>>> Cheers,>>>>    Paul Ibison SQL Server MVP,www.replicationanswers.com>>>>     (recommended sql server 2000 replicationbook:>>>>http://www.nwsu.com/0974973602p.html)>>>>>>>>>>>>>>>>>>>>>>>>>>>>>