|
|
You're in the
sql server replication
group:SQL 2005 --> Replicate Production DB to Development DB
sql server replication:
We are using SQL Server 2005. I would like to replicate our production
database to our development database. The trick here is that I do not want to delete any new tables in the Development database that have not been released to production so I can't use a straight database copy. How can I accomplish this? Thanks in adavance! Terry
I'd recommend snapshot replication. This way you'll be able to edit the data
on the development machines and completely copy over it when the time comes. The tables in the production database can be replicated and the existing (dev only) tables will not be affected. You will probably need a pre and post snapshot script to add and drop the FKs. HTH, Paul Ibison
There shouldn't be any foreign key dependencies violation issues during
snapshot processing in a pure SQL2005 environment for transactional\snapshot replication which would require the use of pre-\post- snapshot script to resolve. I would like to know (soon) if anyone hit any. -Raymond [quoted text, click to view] "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message news:4F825D73-54C5-429F-8298-26AFCD8B83A2@microsoft.com... > I'd recommend snapshot replication. This way you'll be able to edit the > data > on the development machines and completely copy over it when the time > comes. > The tables in the production database can be replicated and the existing > (dev > only) tables will not be affected. You will probably need a pre and post > snapshot script to add and drop the FKs. > HTH, > Paul Ibison >
Hi Paul,
Actually SQL2005 snapshot processing for transactional\snapshot replication should be able to handle FKs that exist only on the subscriber reasonably well. Consider the following .pre file generated from the snapshot agent in SQL2005 when the pre-creation command is 'drop': SET QUOTED_IDENTIFIER ON go if object_id('sys.sp_MSrestoresavedforeignkeys') < 0 exec sys.sp_MSdropfkreferencingarticle @destination_object_name = N'sometable', @destination_owner_name = N'dbo' go drop Table [dbo].[sometable] go The magical line here is the call to sp_MSdropfkreferencingarticle if sp_MSrestoresavedforeignkeys exists. The sp_MSdropfkreferencingarticle system procedure has been substantially enhanced in SQL2005 to save off meta-data of FKs referencing a published object that we are about to drop (so we can drop the table a few lines below) and at the end of the snapshot delivery session, the distribution agent will call the new sp_MSrestoresavedforeignkeys procedure to restore the dropped FKs based on meta-data we saved off. Now, it is very possible that I am misreading the whole thing or there are cases not handled by the new mechanism although I will encourage you to test it out :) -Raymond [quoted text, click to view] "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message news:%23Qe0tjYnHHA.4476@TK2MSFTNGP02.phx.gbl... > Raymond - I reckon we are talking at cross-purposes, or I might be > off-track - anyway let me explain my thinking.... > In Terri's case there are tables in the Dev environment that don't exist > in the Prod environment, so when the other tables are reinitialized using > snapshot replication from Prod to Dev, if there are any relationships that > exist purely in the Dev environment then there would be FK issues. > Rgds, > Paul Ibison >
Raymond - I reckon we are talking at cross-purposes, or I might be
off-track - anyway let me explain my thinking.... In Terri's case there are tables in the Dev environment that don't exist in the Prod environment, so when the other tables are reinitialized using snapshot replication from Prod to Dev, if there are any relationships that exist purely in the Dev environment then there would be FK issues. Rgds, Paul Ibison
Thanks for the heads-up Raymond. I hadn't seen this before and it looks like
I am using post-snapshot scripts unnecessarily. I'll try it all out and then post back. Cheers, Paul
I just wanted to say thanks to both of you for your responses. We have been
able to get replication working to a test database and will try against our actual development database here shortly. Thanks again, Terry [quoted text, click to view] "Paul Ibison" wrote:
> Thanks for the heads-up Raymond. I hadn't seen this before and it looks like > I am using post-snapshot scripts unnecessarily. I'll try it all out and then > post back. > Cheers, > Paul
Replication of the tables is working but I am having an issue when adding
rows to the replicated tables. There seems to be a problem with dealing with idenity columns. For instance I have a table with three columns one of which is the primary key column. When a new row is added I think the system is generating an already present value for the primary key. I am receiving a duplicate key error. Is there a configuration setting that I need to set? Thanks again for your help, Terry [quoted text, click to view] "Raymond Mak [MSFT]" wrote:
> Hi Paul, > > Actually SQL2005 snapshot processing for transactional\snapshot replication > should be able to handle FKs that exist only on the subscriber reasonably > well. Consider the following .pre file generated from the snapshot agent in > SQL2005 when the pre-creation command is 'drop': > > SET QUOTED_IDENTIFIER ON > > > > go > > > > if object_id('sys.sp_MSrestoresavedforeignkeys') < 0 exec > sys.sp_MSdropfkreferencingarticle @destination_object_name = N'sometable', > @destination_owner_name = N'dbo' > > > > go > > > > drop Table [dbo].[sometable] > > go > > The magical line here is the call to sp_MSdropfkreferencingarticle if > sp_MSrestoresavedforeignkeys exists. The sp_MSdropfkreferencingarticle > system procedure has been substantially enhanced in SQL2005 to save off > meta-data of FKs referencing a published object that we are about to drop > (so we can drop the table a few lines below) and at the end of the snapshot > delivery session, the distribution agent will call the new > sp_MSrestoresavedforeignkeys procedure to restore the dropped FKs based on > meta-data we saved off. Now, it is very possible that I am misreading the > whole thing or there are cases not handled by the new mechanism although I > will encourage you to test it out :) > > -Raymond > > > > > > "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message > news:%23Qe0tjYnHHA.4476@TK2MSFTNGP02.phx.gbl... > > Raymond - I reckon we are talking at cross-purposes, or I might be > > off-track - anyway let me explain my thinking.... > > In Terri's case there are tables in the Dev environment that don't exist > > in the Prod environment, so when the other tables are reinitialized using > > snapshot replication from Prod to Dev, if there are any relationships that > > exist purely in the Dev environment then there would be FK issues. > > Rgds, > > Paul Ibison > > > >
Hi Terry,
Unfortunately the only solution that I can think of is to manually run DBCC CHECKIDENT on your subscriber tables with identity columns (you can simply put that in a post-snapshot script) to reset the identity seed values to be the max values of the identity columns. While it may be worthwhile for replication to automatically do this for you, I can think of at least one case where doing so is not desirable (rolling up data with different identity ranges on a central subscriber). In any case, you may want to log a feedback item about this @ http://connect.microsoft.com so we can consider doing something about this in a future release. -Raymond [quoted text, click to view] "Terry Wahl" <TerryWahl@discussions.microsoft.com> wrote in message news:DAD169CE-0E70-40FB-97C5-51314DE515F0@microsoft.com... > Replication of the tables is working but I am having an issue when adding > rows to the replicated tables. There seems to be a problem with dealing > with > idenity columns. For instance I have a table with three columns one of > which > is the primary key column. When a new row is added I think the system is > generating an already present value for the primary key. I am receiving a > duplicate key error. Is there a configuration setting that I need to set? > > Thanks again for your help, > Terry > > > "Raymond Mak [MSFT]" wrote: > >> Hi Paul, >> >> Actually SQL2005 snapshot processing for transactional\snapshot >> replication >> should be able to handle FKs that exist only on the subscriber reasonably >> well. Consider the following .pre file generated from the snapshot agent >> in >> SQL2005 when the pre-creation command is 'drop': >> >> SET QUOTED_IDENTIFIER ON >> >> >> >> go >> >> >> >> if object_id('sys.sp_MSrestoresavedforeignkeys') < 0 exec >> sys.sp_MSdropfkreferencingarticle @destination_object_name = >> N'sometable', >> @destination_owner_name = N'dbo' >> >> >> >> go >> >> >> >> drop Table [dbo].[sometable] >> >> go >> >> The magical line here is the call to sp_MSdropfkreferencingarticle if >> sp_MSrestoresavedforeignkeys exists. The sp_MSdropfkreferencingarticle >> system procedure has been substantially enhanced in SQL2005 to save off >> meta-data of FKs referencing a published object that we are about to drop >> (so we can drop the table a few lines below) and at the end of the >> snapshot >> delivery session, the distribution agent will call the new >> sp_MSrestoresavedforeignkeys procedure to restore the dropped FKs based >> on >> meta-data we saved off. Now, it is very possible that I am misreading the >> whole thing or there are cases not handled by the new mechanism although >> I >> will encourage you to test it out :) >> >> -Raymond >> >> >> >> >> >> "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message >> news:%23Qe0tjYnHHA.4476@TK2MSFTNGP02.phx.gbl... >> > Raymond - I reckon we are talking at cross-purposes, or I might be >> > off-track - anyway let me explain my thinking.... >> > In Terri's case there are tables in the Dev environment that don't >> > exist >> > in the Prod environment, so when the other tables are reinitialized >> > using >> > snapshot replication from Prod to Dev, if there are any relationships >> > that >> > exist purely in the Dev environment then there would be FK issues. >> > Rgds, >> > Paul Ibison >> > >> >> >>
Hi Raymond,
We are using push replication so it looks like I will have to execute the command by hand. But I am having problems with the 'DBCC CHECIDENT' command. The command that I am running after the data is replicatated is the following: DBCC CHECIDENT('dbo.TcmLatestReport', RESEED) I receive the following message: Checking identity information: current identity value 'NULL', current column value 'NULL'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. The table contains entries so I'm not sure why the command is not setting the identiy seed to be the max value of the current value? Thanks again for your help, Terry [quoted text, click to view] "Raymond Mak [MSFT]" wrote:
> Hi Terry, > > Unfortunately the only solution that I can think of is to manually run DBCC > CHECKIDENT on your subscriber tables with identity columns (you can simply > put that in a post-snapshot script) to reset the identity seed values to be > the max values of the identity columns. While it may be worthwhile for > replication to automatically do this for you, I can think of at least one > case where doing so is not desirable (rolling up data with different > identity ranges on a central subscriber). In any case, you may want to log a > feedback item about this @ http://connect.microsoft.com so we can consider > doing something about this in a future release. > > -Raymond > > "Terry Wahl" <TerryWahl@discussions.microsoft.com> wrote in message > news:DAD169CE-0E70-40FB-97C5-51314DE515F0@microsoft.com... > > Replication of the tables is working but I am having an issue when adding > > rows to the replicated tables. There seems to be a problem with dealing > > with > > idenity columns. For instance I have a table with three columns one of > > which > > is the primary key column. When a new row is added I think the system is > > generating an already present value for the primary key. I am receiving a > > duplicate key error. Is there a configuration setting that I need to set? > > > > Thanks again for your help, > > Terry > > > > > > "Raymond Mak [MSFT]" wrote: > > > >> Hi Paul, > >> > >> Actually SQL2005 snapshot processing for transactional\snapshot > >> replication > >> should be able to handle FKs that exist only on the subscriber reasonably > >> well. Consider the following .pre file generated from the snapshot agent > >> in > >> SQL2005 when the pre-creation command is 'drop': > >> > >> SET QUOTED_IDENTIFIER ON > >> > >> > >> > >> go > >> > >> > >> > >> if object_id('sys.sp_MSrestoresavedforeignkeys') < 0 exec > >> sys.sp_MSdropfkreferencingarticle @destination_object_name = > >> N'sometable', > >> @destination_owner_name = N'dbo' > >> > >> > >> > >> go > >> > >> > >> > >> drop Table [dbo].[sometable] > >> > >> go > >> > >> The magical line here is the call to sp_MSdropfkreferencingarticle if > >> sp_MSrestoresavedforeignkeys exists. The sp_MSdropfkreferencingarticle > >> system procedure has been substantially enhanced in SQL2005 to save off > >> meta-data of FKs referencing a published object that we are about to drop > >> (so we can drop the table a few lines below) and at the end of the > >> snapshot > >> delivery session, the distribution agent will call the new > >> sp_MSrestoresavedforeignkeys procedure to restore the dropped FKs based > >> on > >> meta-data we saved off. Now, it is very possible that I am misreading the > >> whole thing or there are cases not handled by the new mechanism although > >> I > >> will encourage you to test it out :) > >> > >> -Raymond > >> > >> > >> > >> > >> > >> "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message > >> news:%23Qe0tjYnHHA.4476@TK2MSFTNGP02.phx.gbl... > >> > Raymond - I reckon we are talking at cross-purposes, or I might be > >> > off-track - anyway let me explain my thinking.... > >> > In Terri's case there are tables in the Dev environment that don't > >> > exist > >> > in the Prod environment, so when the other tables are reinitialized > >> > using > >> > snapshot replication from Prod to Dev, if there are any relationships > >> > that > >> > exist purely in the Dev environment then there would be FK issues. > >> > Rgds, > >> > Paul Ibison > >> > > >> > >> > >> > >
Hi Terry,
SQL2005 SP2 fixes a DBCC CHECKIDENT bug with the exact symtoms that you described so I would encourage you to check that out. Or, you can work around the problem by performing a dumming insert that triggers the PK violation and then run DBCC CHECKIDENT to reseed the identity value. -Raymond [quoted text, click to view] "Terry Wahl" <TerryWahl@discussions.microsoft.com> wrote in message news:854D386A-E82D-4948-BC03-47BB3F8E8E15@microsoft.com... > Hi Raymond, > We are using push replication so it looks like I will have to execute the > command by hand. But I am having problems with the 'DBCC CHECIDENT' > command. > The command that I am running after the data is replicatated is the > following: > > DBCC CHECIDENT('dbo.TcmLatestReport', RESEED) > > I receive the following message: > Checking identity information: current identity value 'NULL', current > column > value 'NULL'. > DBCC execution completed. If DBCC printed error messages, contact your > system administrator. > > The table contains entries so I'm not sure why the command is not setting > the identiy seed to be the max value of the current value? > > Thanks again for your help, > Terry > > > > > > "Raymond Mak [MSFT]" wrote: > >> Hi Terry, >> >> Unfortunately the only solution that I can think of is to manually run >> DBCC >> CHECKIDENT on your subscriber tables with identity columns (you can >> simply >> put that in a post-snapshot script) to reset the identity seed values to >> be >> the max values of the identity columns. While it may be worthwhile for >> replication to automatically do this for you, I can think of at least one >> case where doing so is not desirable (rolling up data with different >> identity ranges on a central subscriber). In any case, you may want to >> log a >> feedback item about this @ http://connect.microsoft.com so we can >> consider >> doing something about this in a future release. >> >> -Raymond >> >> "Terry Wahl" <TerryWahl@discussions.microsoft.com> wrote in message >> news:DAD169CE-0E70-40FB-97C5-51314DE515F0@microsoft.com... >> > Replication of the tables is working but I am having an issue when >> > adding >> > rows to the replicated tables. There seems to be a problem with >> > dealing >> > with >> > idenity columns. For instance I have a table with three columns one of >> > which >> > is the primary key column. When a new row is added I think the system >> > is >> > generating an already present value for the primary key. I am >> > receiving a >> > duplicate key error. Is there a configuration setting that I need to >> > set? >> > >> > Thanks again for your help, >> > Terry >> > >> > >> > "Raymond Mak [MSFT]" wrote: >> > >> >> Hi Paul, >> >> >> >> Actually SQL2005 snapshot processing for transactional\snapshot >> >> replication >> >> should be able to handle FKs that exist only on the subscriber >> >> reasonably >> >> well. Consider the following .pre file generated from the snapshot >> >> agent >> >> in >> >> SQL2005 when the pre-creation command is 'drop': >> >> >> >> SET QUOTED_IDENTIFIER ON >> >> >> >> >> >> >> >> go >> >> >> >> >> >> >> >> if object_id('sys.sp_MSrestoresavedforeignkeys') < 0 exec >> >> sys.sp_MSdropfkreferencingarticle @destination_object_name = >> >> N'sometable', >> >> @destination_owner_name = N'dbo' >> >> >> >> >> >> >> >> go >> >> >> >> >> >> >> >> drop Table [dbo].[sometable] >> >> >> >> go >> >> >> >> The magical line here is the call to sp_MSdropfkreferencingarticle if >> >> sp_MSrestoresavedforeignkeys exists. The sp_MSdropfkreferencingarticle >> >> system procedure has been substantially enhanced in SQL2005 to save >> >> off >> >> meta-data of FKs referencing a published object that we are about to >> >> drop >> >> (so we can drop the table a few lines below) and at the end of the >> >> snapshot >> >> delivery session, the distribution agent will call the new >> >> sp_MSrestoresavedforeignkeys procedure to restore the dropped FKs >> >> based >> >> on >> >> meta-data we saved off. Now, it is very possible that I am misreading >> >> the >> >> whole thing or there are cases not handled by the new mechanism >> >> although >> >> I >> >> will encourage you to test it out :) >> >> >> >> -Raymond >> >> >> >> >> >> >> >> >> >> >> >> "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message >> >> news:%23Qe0tjYnHHA.4476@TK2MSFTNGP02.phx.gbl... >> >> > Raymond - I reckon we are talking at cross-purposes, or I might be >> >> > off-track - anyway let me explain my thinking.... >> >> > In Terri's case there are tables in the Dev environment that don't >> >> > exist >> >> > in the Prod environment, so when the other tables are reinitialized >> >> > using >> >> > snapshot replication from Prod to Dev, if there are any >> >> > relationships >> >> > that >> >> > exist purely in the Dev environment then there would be FK issues. >> >> > Rgds, >> >> > Paul Ibison >> >> > >> >> >> >> >> >> >> >> >>
Hi Raymond,
I installed SQL2005 SP2 this morning. The DBCC CHECKIDENT command now works. Very happy! Thanks again for your help! Terry [quoted text, click to view] "Raymond Mak [MSFT]" wrote:
> Hi Terry, > > SQL2005 SP2 fixes a DBCC CHECKIDENT bug with the exact symtoms that you > described so I would encourage you to check that out. Or, you can work > around the problem by performing a dumming insert that triggers the PK > violation and then run DBCC CHECKIDENT to reseed the identity value. > > -Raymond > > "Terry Wahl" <TerryWahl@discussions.microsoft.com> wrote in message > news:854D386A-E82D-4948-BC03-47BB3F8E8E15@microsoft.com... > > Hi Raymond, > > We are using push replication so it looks like I will have to execute the > > command by hand. But I am having problems with the 'DBCC CHECIDENT' > > command. > > The command that I am running after the data is replicatated is the > > following: > > > > DBCC CHECIDENT('dbo.TcmLatestReport', RESEED) > > > > I receive the following message: > > Checking identity information: current identity value 'NULL', current > > column > > value 'NULL'. > > DBCC execution completed. If DBCC printed error messages, contact your > > system administrator. > > > > The table contains entries so I'm not sure why the command is not setting > > the identiy seed to be the max value of the current value? > > > > Thanks again for your help, > > Terry > > > > > > > > > > > > "Raymond Mak [MSFT]" wrote: > > > >> Hi Terry, > >> > >> Unfortunately the only solution that I can think of is to manually run > >> DBCC > >> CHECKIDENT on your subscriber tables with identity columns (you can > >> simply > >> put that in a post-snapshot script) to reset the identity seed values to > >> be > >> the max values of the identity columns. While it may be worthwhile for > >> replication to automatically do this for you, I can think of at least one > >> case where doing so is not desirable (rolling up data with different > >> identity ranges on a central subscriber). In any case, you may want to > >> log a > >> feedback item about this @ http://connect.microsoft.com so we can > >> consider > >> doing something about this in a future release. > >> > >> -Raymond > >> > >> "Terry Wahl" <TerryWahl@discussions.microsoft.com> wrote in message > >> news:DAD169CE-0E70-40FB-97C5-51314DE515F0@microsoft.com... > >> > Replication of the tables is working but I am having an issue when > >> > adding > >> > rows to the replicated tables. There seems to be a problem with > >> > dealing > >> > with > >> > idenity columns. For instance I have a table with three columns one of > >> > which > >> > is the primary key column. When a new row is added I think the system > >> > is > >> > generating an already present value for the primary key. I am > >> > receiving a > >> > duplicate key error. Is there a configuration setting that I need to > >> > set? > >> > > >> > Thanks again for your help, > >> > Terry > >> > > >> > > >> > "Raymond Mak [MSFT]" wrote: > >> > > >> >> Hi Paul, > >> >> > >> >> Actually SQL2005 snapshot processing for transactional\snapshot > >> >> replication > >> >> should be able to handle FKs that exist only on the subscriber > >> >> reasonably > >> >> well. Consider the following .pre file generated from the snapshot > >> >> agent > >> >> in > >> >> SQL2005 when the pre-creation command is 'drop': > >> >> > >> >> SET QUOTED_IDENTIFIER ON > >> >> > >> >> > >> >> > >> >> go > >> >> > >> >> > >> >> > >> >> if object_id('sys.sp_MSrestoresavedforeignkeys') < 0 exec > >> >> sys.sp_MSdropfkreferencingarticle @destination_object_name = > >> >> N'sometable', > >> >> @destination_owner_name = N'dbo' > >> >> > >> >> > >> >> > >> >> go > >> >> > >> >> > >> >> > >> >> drop Table [dbo].[sometable] > >> >> > >> >> go > >> >> > >> >> The magical line here is the call to sp_MSdropfkreferencingarticle if > >> >> sp_MSrestoresavedforeignkeys exists. The sp_MSdropfkreferencingarticle > >> >> system procedure has been substantially enhanced in SQL2005 to save > >> >> off > >> >> meta-data of FKs referencing a published object that we are about to > >> >> drop > >> >> (so we can drop the table a few lines below) and at the end of the > >> >> snapshot > >> >> delivery session, the distribution agent will call the new > >> >> sp_MSrestoresavedforeignkeys procedure to restore the dropped FKs > >> >> based > >> >> on > >> >> meta-data we saved off. Now, it is very possible that I am misreading > >> >> the > >> >> whole thing or there are cases not handled by the new mechanism > >> >> although > >> >> I > >> >> will encourage you to test it out :) > >> >> > >> >> -Raymond > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message > >> >> news:%23Qe0tjYnHHA.4476@TK2MSFTNGP02.phx.gbl... > >> >> > Raymond - I reckon we are talking at cross-purposes, or I might be > >> >> > off-track - anyway let me explain my thinking.... > >> >> > In Terri's case there are tables in the Dev environment that don't > >> >> > exist > >> >> > in the Prod environment, so when the other tables are reinitialized > >> >> > using > >> >> > snapshot replication from Prod to Dev, if there are any > >> >> > relationships > >> >> > that > >> >> > exist purely in the Dev environment then there would be FK issues. > >> >> > Rgds, > >> >> > Paul Ibison > >> >> > > >> >> > >> >> > >> >> > >> > >> > >> > >
Hi Raymond,
I have a follow-up question. What I would like to happen is schedule a task such that once a day the production database replicates to the development database. As part of this replication the contents of the development tables would equal the contents of the production database. While testing I have inserted new rows into a table on the development database. If I manually initiate the replication process I receive a status message stating "No replicated transaction are available". The newly inserted rows still remain in the development database table. If I first select 'Reinitialize subscription(s)' and then select 'Mark For Reinialization' in the dialog and then manually initiate the replication process the inserted rows in the development database table are removed. How can I configure the publisher to instrut the subscriber to reinitialize from the snapshot? I'm surprised that the inserted rows in the development table persist after replication. Thanks again for your help, Terry [quoted text, click to view] "Raymond Mak [MSFT]" wrote:
> Hi Terry, > > SQL2005 SP2 fixes a DBCC CHECKIDENT bug with the exact symtoms that you > described so I would encourage you to check that out. Or, you can work > around the problem by performing a dumming insert that triggers the PK > violation and then run DBCC CHECKIDENT to reseed the identity value. > > -Raymond > > "Terry Wahl" <TerryWahl@discussions.microsoft.com> wrote in message > news:854D386A-E82D-4948-BC03-47BB3F8E8E15@microsoft.com... > > Hi Raymond, > > We are using push replication so it looks like I will have to execute the > > command by hand. But I am having problems with the 'DBCC CHECIDENT' > > command. > > The command that I am running after the data is replicatated is the > > following: > > > > DBCC CHECIDENT('dbo.TcmLatestReport', RESEED) > > > > I receive the following message: > > Checking identity information: current identity value 'NULL', current > > column > > value 'NULL'. > > DBCC execution completed. If DBCC printed error messages, contact your > > system administrator. > > > > The table contains entries so I'm not sure why the command is not setting > > the identiy seed to be the max value of the current value? > > > > Thanks again for your help, > > Terry > > > > > > > > > > > > "Raymond Mak [MSFT]" wrote: > > > >> Hi Terry, > >> > >> Unfortunately the only solution that I can think of is to manually run > >> DBCC > >> CHECKIDENT on your subscriber tables with identity columns (you can > >> simply > >> put that in a post-snapshot script) to reset the identity seed values to > >> be > >> the max values of the identity columns. While it may be worthwhile for > >> replication to automatically do this for you, I can think of at least one > >> case where doing so is not desirable (rolling up data with different > >> identity ranges on a central subscriber). In any case, you may want to > >> log a > >> feedback item about this @ http://connect.microsoft.com so we can > >> consider > >> doing something about this in a future release. > >> > >> -Raymond > >> > >> "Terry Wahl" <TerryWahl@discussions.microsoft.com> wrote in message > >> news:DAD169CE-0E70-40FB-97C5-51314DE515F0@microsoft.com... > >> > Replication of the tables is working but I am having an issue when > >> > adding > >> > rows to the replicated tables. There seems to be a problem with > >> > dealing > >> > with > >> > idenity columns. For instance I have a table with three columns one of > >> > which > >> > is the primary key column. When a new row is added I think the system > >> > is > >> > generating an already present value for the primary key. I am > >> > receiving a > >> > duplicate key error. Is there a configuration setting that I need to > >> > set? > >> > > >> > Thanks again for your help, > >> > Terry > >> > > >> > > >> > "Raymond Mak [MSFT]" wrote: > >> > > >> >> Hi Paul, > >> >> > >> >> Actually SQL2005 snapshot processing for transactional\snapshot > >> >> replication > >> >> should be able to handle FKs that exist only on the subscriber > >> >> reasonably > >> >> well. Consider the following .pre file generated from the snapshot > >> >> agent > >> >> in > >> >> SQL2005 when the pre-creation command is 'drop': > >> >> > >> >> SET QUOTED_IDENTIFIER ON > >> >> > >> >> > >> >> > >> >> go > >> >> > >> >> > >> >> > >> >> if object_id('sys.sp_MSrestoresavedforeignkeys') < 0 exec > >> >> sys.sp_MSdropfkreferencingarticle @destination_object_name = > >> >> N'sometable', > >> >> @destination_owner_name = N'dbo' > >> >> > >> >> > >> >> > >> >> go > >> >> > >> >> > >> >> > >> >> drop Table [dbo].[sometable] > >> >> > >> >> go > >> >> > >> >> The magical line here is the call to sp_MSdropfkreferencingarticle if > >> >> sp_MSrestoresavedforeignkeys exists. The sp_MSdropfkreferencingarticle > >> >> system procedure has been substantially enhanced in SQL2005 to save > >> >> off > >> >> meta-data of FKs referencing a published object that we are about to > >> >> drop > >> >> (so we can drop the table a few lines below) and at the end of the > >> >> snapshot > >> >> delivery session, the distribution agent will call the new > >> >> sp_MSrestoresavedforeignkeys procedure to restore the dropped FKs > >> >> based > >> >> on > >> >> meta-data we saved off. Now, it is very possible that I am misreading > >> >> the > >> >> whole thing or there are cases not handled by the new mechanism > >> >> although > >> >> I > >> >> will encourage you to test it out :) > >> >> > >> >> -Raymond > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message > >> >> news:%23Qe0tjYnHHA.4476@TK2MSFTNGP02.phx.gbl... > >> >> > Raymond - I reckon we are talking at cross-purposes, or I might be > >> >> > off-track - anyway let me explain my thinking.... > >> >> > In Terri's case there are tables in the Dev environment that don't > >> >> > exist > >> >> > in the Prod environment, so when the other tables are reinitialized > >> >> > using > >> >> > snapshot replication from Prod to Dev, if there are any > >> >> > relationships > >> >> > that > >> >> > exist purely in the Dev environment then there would be FK issues. > >> >> > Rgds, > >> >> > Paul Ibison > >> >> > > >> >> > >> >> > >> >> > >> > >> > >> > >
Hi Terry,
Not sure if I understand your scenario correctly (typically folks will like to keep their changes at the subscriber), but you can definitely schedule a T-SQL job to execute sp_reinitsubscription followed by sp_startpublication_snapshot at the publisher on a daily basis. If your distribution agent is running continuously, it should pick up the new snapshot and remove all your subscriber data once the snapshot has been generated. -Raymond [quoted text, click to view] "Terry Wahl" <TerryWahl@discussions.microsoft.com> wrote in message
news:70456384-45CC-4C15-A582-1C28E284B60A@microsoft.com... > Hi Raymond, > I have a follow-up question. What I would like to happen is schedule a > task > such that once a day the production database replicates to the development > database. As part of this replication the contents of the development > tables > would equal the contents of the production database. > > While testing I have inserted new rows into a table on the development > database. If I manually initiate the replication process I receive a > status > message stating "No replicated transaction are available". The newly > inserted rows still remain in the development database table. > > If I first select 'Reinitialize subscription(s)' and then select 'Mark For > Reinialization' in the dialog and then manually initiate the replication > process the inserted rows in the development database table are removed. > > How can I configure the publisher to instrut the subscriber to > reinitialize > from the snapshot? I'm surprised that the inserted rows in the > development > table persist after replication. > > Thanks again for your help, > Terry > > > > > "Raymond Mak [MSFT]" wrote: > >> Hi Terry, >> >> SQL2005 SP2 fixes a DBCC CHECKIDENT bug with the exact symtoms that you >> described so I would encourage you to check that out. Or, you can work >> around the problem by performing a dumming insert that triggers the PK >> violation and then run DBCC CHECKIDENT to reseed the identity value. >> >> -Raymond >> >> "Terry Wahl" <TerryWahl@discussions.microsoft.com> wrote in message >> news:854D386A-E82D-4948-BC03-47BB3F8E8E15@microsoft.com... >> > Hi Raymond, >> > We are using push replication so it looks like I will have to execute >> > the >> > command by hand. But I am having problems with the 'DBCC CHECIDENT' >> > command. >> > The command that I am running after the data is replicatated is the >> > following: >> > >> > DBCC CHECIDENT('dbo.TcmLatestReport', RESEED) >> > >> > I receive the following message: >> > Checking identity information: current identity value 'NULL', current >> > column >> > value 'NULL'. >> > DBCC execution completed. If DBCC printed error messages, contact your >> > system administrator. >> > >> > The table contains entries so I'm not sure why the command is not >> > setting >> > the identiy seed to be the max value of the current value? >> > >> > Thanks again for your help, >> > Terry >> > >> > >> > >> > >> > >> > "Raymond Mak [MSFT]" wrote: >> > >> >> Hi Terry, >> >> >> >> Unfortunately the only solution that I can think of is to manually run >> >> DBCC >> >> CHECKIDENT on your subscriber tables with identity columns (you can >> >> simply >> >> put that in a post-snapshot script) to reset the identity seed values >> >> to >> >> be >> >> the max values of the identity columns. While it may be worthwhile for >> >> replication to automatically do this for you, I can think of at least >> >> one >> >> case where doing so is not desirable (rolling up data with different >> >> identity ranges on a central subscriber). In any case, you may want to >> >> log a >> >> feedback item about this @ http://connect.microsoft.com so we can >> >> consider >> >> doing something about this in a future release. >> >> >> >> -Raymond >> >> >> >> "Terry Wahl" <TerryWahl@discussions.microsoft.com> wrote in message >> >> news:DAD169CE-0E70-40FB-97C5-51314DE515F0@microsoft.com... >> >> > Replication of the tables is working but I am having an issue when >> >> > adding >> >> > rows to the replicated tables. There seems to be a problem with >> >> > dealing >> >> > with >> >> > idenity columns. For instance I have a table with three columns one >> >> > of >> >> > which >> >> > is the primary key column. When a new row is added I think the >> >> > system >> >> > is >> >> > generating an already present value for the primary key. I am >> >> > receiving a >> >> > duplicate key error. Is there a configuration setting that I need >> >> > to >> >> > set? >> >> > >> >> > Thanks again for your help, >> >> > Terry >> >> > >> >> > >> >> > "Raymond Mak [MSFT]" wrote: >> >> > >> >> >> Hi Paul, >> >> >> >> >> >> Actually SQL2005 snapshot processing for transactional\snapshot >> >> >> replication >> >> >> should be able to handle FKs that exist only on the subscriber >> >> >> reasonably >> >> >> well. Consider the following .pre file generated from the snapshot >> >> >> agent >> >> >> in >> >> >> SQL2005 when the pre-creation command is 'drop': >> >> >> >> >> >> SET QUOTED_IDENTIFIER ON >> >> >> >> >> >> >> >> >> >> >> >> go >> >> >> >> >> >> >> >> >> >> >> >> if object_id('sys.sp_MSrestoresavedforeignkeys') < 0 exec >> >> >> sys.sp_MSdropfkreferencingarticle @destination_object_name = >> >> >> N'sometable', >> >> >> @destination_owner_name = N'dbo' >> >> >> >> >> >> >> >> >> >> >> >> go >> >> >> >> >> >> >> >> >> >> >> >> drop Table [dbo].[sometable] >> >> >> >> >> >> go >> >> >> >> >> >> The magical line here is the call to sp_MSdropfkreferencingarticle >> >> >> if >> >> >> sp_MSrestoresavedforeignkeys exists. The >> >> >> sp_MSdropfkreferencingarticle >> >> >> system procedure has been substantially enhanced in SQL2005 to save >> >> >> off >> >> >> meta-data of FKs referencing a published object that we are about >> >> >> to >> >> >> drop >> >> >> (so we can drop the table a few lines below) and at the end of the >> >> >> snapshot >> >> >> delivery session, the distribution agent will call the new >> >> >> sp_MSrestoresavedforeignkeys procedure to restore the dropped FKs >> >> >> based >> >> >> on >> >> >> meta-data we saved off. Now, it is very possible that I am >> >> >> misreading >> >> >> the >> >> >> whole thing or there are cases not handled by the new mechanism >> >> >> although >> >> >> I >> >> >> will encourage you to test it out :) >> >> >> >> >> >> -Raymond >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message >> >> >> news:%23Qe0tjYnHHA.4476@TK2MSFTNGP02.phx.gbl... >> >> >> > Raymond - I reckon we are talking at cross-purposes, or I might >> >> >> > be >> >> >> > off-track - anyway let me explain my thinking.... >> >> >> > In Terri's case there are tables in the Dev environment that >> >> >> > don't
Hi Raymond,
Tried it and things worked. Thanks again for your help. If you don't mind a couple of additional question...(I really appriciate your help!) After examining the tables more closely the it looks like for some reason the FK relationships are getting dropped. On the replicated tables the FK are defined but all the relationships are gone. Things are configured as a snapshot push. The destination has sp2 installed while the source server does not. Shouldn't the relationships get replicated? Thanks again, Terry [quoted text, click to view] "Raymond Mak [MSFT]" wrote:
> Hi Terry, > > Not sure if I understand your scenario correctly (typically folks will like > to keep their changes at the subscriber), but you can definitely schedule a > T-SQL job to execute sp_reinitsubscription followed by > sp_startpublication_snapshot at the publisher on a daily basis. If your > distribution agent is running continuously, it should pick up the new > snapshot and remove all your subscriber data once the snapshot has been > generated. > > -Raymond > > "Terry Wahl" <TerryWahl@discussions.microsoft.com> wrote in message > news:70456384-45CC-4C15-A582-1C28E284B60A@microsoft.com... > > Hi Raymond, > > I have a follow-up question. What I would like to happen is schedule a > > task > > such that once a day the production database replicates to the development > > database. As part of this replication the contents of the development > > tables > > would equal the contents of the production database. > > > > While testing I have inserted new rows into a table on the development > > database. If I manually initiate the replication process I receive a > > status > > message stating "No replicated transaction are available". The newly > > inserted rows still remain in the development database table. > > > > If I first select 'Reinitialize subscription(s)' and then select 'Mark For > > Reinialization' in the dialog and then manually initiate the replication > > process the inserted rows in the development database table are removed. > > > > How can I configure the publisher to instrut the subscriber to > > reinitialize > > from the snapshot? I'm surprised that the inserted rows in the > > development > > table persist after replication. > > > > Thanks again for your help, > > Terry > > > > > > > > > > "Raymond Mak [MSFT]" wrote: > > > >> Hi Terry, > >> > >> SQL2005 SP2 fixes a DBCC CHECKIDENT bug with the exact symtoms that you > >> described so I would encourage you to check that out. Or, you can work > >> around the problem by performing a dumming insert that triggers the PK > >> violation and then run DBCC CHECKIDENT to reseed the identity value. > >> > >> -Raymond > >> > >> "Terry Wahl" <TerryWahl@discussions.microsoft.com> wrote in message > >> news:854D386A-E82D-4948-BC03-47BB3F8E8E15@microsoft.com... > >> > Hi Raymond, > >> > We are using push replication so it looks like I will have to execute > >> > the > >> > command by hand. But I am having problems with the 'DBCC CHECIDENT' > >> > command. > >> > The command that I am running after the data is replicatated is the > >> > following: > >> > > >> > DBCC CHECIDENT('dbo.TcmLatestReport', RESEED) > >> > > >> > I receive the following message: > >> > Checking identity information: current identity value 'NULL', current > >> > column > >> > value 'NULL'. > >> > DBCC execution completed. If DBCC printed error messages, contact your > >> > system administrator. > >> > > >> > The table contains entries so I'm not sure why the command is not > >> > setting > >> > the identiy seed to be the max value of the current value? > >> > > >> > Thanks again for your help, > >> > Terry > >> > > >> > > >> > > >> > > >> > > >> > "Raymond Mak [MSFT]" wrote: > >> > > >> >> Hi Terry, > >> >> > >> >> Unfortunately the only solution that I can think of is to manually run > >> >> DBCC > >> >> CHECKIDENT on your subscriber tables with identity columns (you can > >> >> simply > >> >> put that in a post-snapshot script) to reset the identity seed values > >> >> to > >> >> be > >> >> the max values of the identity columns. While it may be worthwhile for > >> >> replication to automatically do this for you, I can think of at least > >> >> one > >> >> case where doing so is not desirable (rolling up data with different > >> >> identity ranges on a central subscriber). In any case, you may want to > >> >> log a > >> >> feedback item about this @ http://connect.microsoft.com so we can > >> >> consider > >> >> doing something about this in a future release. > >> >> > >> >> -Raymond > >> >> > >> >> "Terry Wahl" <TerryWahl@discussions.microsoft.com> wrote in message > >> >> news:DAD169CE-0E70-40FB-97C5-51314DE515F0@microsoft.com... > >> >> > Replication of the tables is working but I am having an issue when > >> >> > adding > >> >> > rows to the replicated tables. There seems to be a problem with > >> >> > dealing > >> >> > with > >> >> > idenity columns. For instance I have a table with three columns one > >> >> > of > >> >> > which > >> >> > is the primary key column. When a new row is added I think the > >> >> > system > >> >> > is > >> >> > generating an already present value for the primary key. I am > >> >> > receiving a > >> >> > duplicate key error. Is there a configuration setting that I need > >> >> > to > >> >> > set? > >> >> > > >> >> > Thanks again for your help, > >> >> > Terry > >> >> > > >> >> > > >> >> > "Raymond Mak [MSFT]" wrote: > >> >> > > >> >> >> Hi Paul, > >> >> >> > >> >> >> Actually SQL2005 snapshot processing for transactional\snapshot > >> >> >> replication > >> >> >> should be able to handle FKs that exist only on the subscriber > >> >> >> reasonably > >> >> >> well. Consider the following .pre file generated from the snapshot > >> >> >> agent > >> >> >> in > >> >> >> SQL2005 when the pre-creation command is 'drop': > >> >> >> > >> >> >> SET QUOTED_IDENTIFIER ON > >> >> >> > >> >> >> > >> >> >> > >> >> >> go > >> >> >> > >> >> >> > >> >> >> > >> >> >> if object_id('sys.sp_MSrestoresavedforeignkeys') < 0 exec > >> >> >> sys.sp_MSdropfkreferencingarticle @destination_object_name = > >> >> >> N'sometable', > >> >> >> @destination_owner_name = N'dbo' > >> >> >> > >> >> >> > >> >> >> > >> >> >> go > >> >> >> > >> >> >> > >> >> >> > >> >> >> drop Table [dbo].[sometable] > >> >> >> > >> >> >> go > >> >> >> > >> >> >> The magical line here is the call to sp_MSdropfkreferencingarticle > >> >> >> if > >> >> >> sp_MSrestoresavedforeignkeys exists. The > >> >> >> sp_MSdropfkreferencingarticle > >> >> >> system procedure has been substantially enhanced in SQL2005 to save > >> >> >> off > >> >> >> meta-data of FKs referencing a published object that we are about > >> >> >> to > >> >> >> drop
Just to rule out the obvious, did you enable the "Copy foreign keys" article
option when you set up the publication? It is off by default. -Raymond [quoted text, click to view] "Terry Wahl" <TerryWahl@discussions.microsoft.com> wrote in message
news:4E799380-9942-43A2-82B9-DD9B9D1FAA3E@microsoft.com... > Hi Raymond, > > Tried it and things worked. Thanks again for your help. > > If you don't mind a couple of additional question...(I really appriciate > your help!) > After examining the tables more closely the it looks like for some reason > the FK relationships are getting dropped. On the replicated tables the > FK > are defined but all the relationships are gone. Things are configured as > a > snapshot push. The destination has sp2 installed while the source server > does not. Shouldn't the relationships get replicated? > > Thanks again, > Terry > > > > > > "Raymond Mak [MSFT]" wrote: > >> Hi Terry, >> >> Not sure if I understand your scenario correctly (typically folks will >> like >> to keep their changes at the subscriber), but you can definitely schedule >> a >> T-SQL job to execute sp_reinitsubscription followed by >> sp_startpublication_snapshot at the publisher on a daily basis. If your >> distribution agent is running continuously, it should pick up the new >> snapshot and remove all your subscriber data once the snapshot has been >> generated. >> >> -Raymond >> >> "Terry Wahl" <TerryWahl@discussions.microsoft.com> wrote in message >> news:70456384-45CC-4C15-A582-1C28E284B60A@microsoft.com... >> > Hi Raymond, >> > I have a follow-up question. What I would like to happen is schedule a >> > task >> > such that once a day the production database replicates to the >> > development >> > database. As part of this replication the contents of the development >> > tables >> > would equal the contents of the production database. >> > >> > While testing I have inserted new rows into a table on the development >> > database. If I manually initiate the replication process I receive a >> > status >> > message stating "No replicated transaction are available". The newly >> > inserted rows still remain in the development database table. >> > >> > If I first select 'Reinitialize subscription(s)' and then select 'Mark >> > For >> > Reinialization' in the dialog and then manually initiate the >> > replication >> > process the inserted rows in the development database table are >> > removed. >> > >> > How can I configure the publisher to instrut the subscriber to >> > reinitialize >> > from the snapshot? I'm surprised that the inserted rows in the >> > development >> > table persist after replication. >> > >> > Thanks again for your help, >> > Terry >> > >> > >> > >> > >> > "Raymond Mak [MSFT]" wrote: >> > >> >> Hi Terry, >> >> >> >> SQL2005 SP2 fixes a DBCC CHECKIDENT bug with the exact symtoms that >> >> you >> >> described so I would encourage you to check that out. Or, you can work >> >> around the problem by performing a dumming insert that triggers the PK >> >> violation and then run DBCC CHECKIDENT to reseed the identity value. >> >> >> >> -Raymond >> >> >> >> "Terry Wahl" <TerryWahl@discussions.microsoft.com> wrote in message >> >> news:854D386A-E82D-4948-BC03-47BB3F8E8E15@microsoft.com... >> >> > Hi Raymond, >> >> > We are using push replication so it looks like I will have to >> >> > execute >> >> > the >> >> > command by hand. But I am having problems with the 'DBCC CHECIDENT' >> >> > command. >> >> > The command that I am running after the data is replicatated is the >> >> > following: >> >> > >> >> > DBCC CHECIDENT('dbo.TcmLatestReport', RESEED) >> >> > >> >> > I receive the following message: >> >> > Checking identity information: current identity value 'NULL', >> >> > current >> >> > column >> >> > value 'NULL'. >> >> > DBCC execution completed. If DBCC printed error messages, contact >> >> > your >> >> > system administrator. >> >> > >> >> > The table contains entries so I'm not sure why the command is not >> >> > setting >> >> > the identiy seed to be the max value of the current value? >> >> > >> >> > Thanks again for your help, >> >> > Terry >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > "Raymond Mak [MSFT]" wrote: >> >> > >> >> >> Hi Terry, >> >> >> >> >> >> Unfortunately the only solution that I can think of is to manually >> >> >> run >> >> >> DBCC >> >> >> CHECKIDENT on your subscriber tables with identity columns (you can >> >> >> simply >> >> >> put that in a post-snapshot script) to reset the identity seed >> >> >> values >> >> >> to >> >> >> be >> >> >> the max values of the identity columns. While it may be worthwhile >> >> >> for >> >> >> replication to automatically do this for you, I can think of at >> >> >> least >> >> >> one >> >> >> case where doing so is not desirable (rolling up data with >> >> >> different >> >> >> identity ranges on a central subscriber). In any case, you may want >> >> >> to >> >> >> log a >> >> >> feedback item about this @ http://connect.microsoft.com so we can >> >> >> consider >> >> >> doing something about this in a future release. >> >> >> >> >> >> -Raymond >> >> >> >> >> >> "Terry Wahl" <TerryWahl@discussions.microsoft.com> wrote in message >> >> >> news:DAD169CE-0E70-40FB-97C5-51314DE515F0@microsoft.com... >> >> >> > Replication of the tables is working but I am having an issue >> >> >> > when >> >> >> > adding >> >> >> > rows to the replicated tables. There seems to be a problem with >> >> >> > dealing >> >> >> > with >> >> >> > idenity columns. For instance I have a table with three columns >> >> >> > one >> >> >> > of >> >> >> > which >> >> >> > is the primary key column. When a new row is added I think the >> >> >> > system >> >> >> > is >> >> >> > generating an already present value for the primary key. I am >> >> >> > receiving a >> >> >> > duplicate key error. Is there a configuration setting that I >> >> >> > need >> >> >> > to >> >> >> > set? >> >> >> > >> >> >> > Thanks again for your help, >> >> >> > Terry >> >> >> > >> >> >> > >> >> >> > "Raymond Mak [MSFT]" wrote: >> >> >> > >> >> >> >> Hi Paul, >> >> >> >> >> >> >> >> Actually SQL2005 snapshot processing for transactional\snapshot >> >> >> >> replication >> >> >> >> should be able to handle FKs that exist only on the subscriber >> >> >> >> reasonably >> >> >> >> well. Consider the following .pre file generated from the >> >> >> >> snapshot >> >> >> >> agent >> >> >> >> in >> >> >> >> SQL2005 when the pre-creation command is 'drop': >> >> >> >> >> >> >> >> SET QUOTED_IDENTIFIER ON >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> go >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> if object_id('sys.sp_MSrestoresavedforeignkeys') < 0 exec >> >> >> >> sys.sp_MSdropfkreferencingarticle @destination_object_name = >> >> >> >> N'sometable',
That was it. Thanks again for your help.
Terry [quoted text, click to view] "Raymond Mak [MSFT]" wrote:
> Just to rule out the obvious, did you enable the "Copy foreign keys" article > option when you set up the publication? It is off by default. > > -Raymond > > "Terry Wahl" <TerryWahl@discussions.microsoft.com> wrote in message > news:4E799380-9942-43A2-82B9-DD9B9D1FAA3E@microsoft.com... > > Hi Raymond, > > > > Tried it and things worked. Thanks again for your help. > > > > If you don't mind a couple of additional question...(I really appriciate > > your help!) > > After examining the tables more closely the it looks like for some reason > > the FK relationships are getting dropped. On the replicated tables the > > FK > > are defined but all the relationships are gone. Things are configured as > > a > > snapshot push. The destination has sp2 installed while the source server > > does not. Shouldn't the relationships get replicated? > > > > Thanks again, > > Terry > > > > > > > > > > > > "Raymond Mak [MSFT]" wrote: > > > >> Hi Terry, > >> > >> Not sure if I understand your scenario correctly (typically folks will > >> like > >> to keep their changes at the subscriber), but you can definitely schedule > >> a > >> T-SQL job to execute sp_reinitsubscription followed by > >> sp_startpublication_snapshot at the publisher on a daily basis. If your > >> distribution agent is running continuously, it should pick up the new > >> snapshot and remove all your subscriber data once the snapshot has been > >> generated. > >> > >> -Raymond > >> > >> "Terry Wahl" <TerryWahl@discussions.microsoft.com> wrote in message > >> news:70456384-45CC-4C15-A582-1C28E284B60A@microsoft.com... > >> > Hi Raymond, > >> > I have a follow-up question. What I would like to happen is schedule a > >> > task > >> > such that once a day the production database replicates to the > >> > development > >> > database. As part of this replication the contents of the development > >> > tables > >> > would equal the contents of the production database. > >> > > >> > While testing I have inserted new rows into a table on the development > >> > database. If I manually initiate the replication process I receive a > >> > status > >> > message stating "No replicated transaction are available". The newly > >> > inserted rows still remain in the development database table. > >> > > >> > If I first select 'Reinitialize subscription(s)' and then select 'Mark > >> > For > >> > Reinialization' in the dialog and then manually initiate the > >> > replication > >> > process the inserted rows in the development database table are > >> > removed. > >> > > >> > How can I configure the publisher to instrut the subscriber to > >> > reinitialize > >> > from the snapshot? I'm surprised that the inserted rows in the > >> > development > >> > table persist after replication. > >> > > >> > Thanks again for your help, > >> > Terry > >> > > >> > > >> > > >> > > >> > "Raymond Mak [MSFT]" wrote: > >> > > >> >> Hi Terry, > >> >> > >> >> SQL2005 SP2 fixes a DBCC CHECKIDENT bug with the exact symtoms that > >> >> you > >> >> described so I would encourage you to check that out. Or, you can work > >> >> around the problem by performing a dumming insert that triggers the PK > >> >> violation and then run DBCC CHECKIDENT to reseed the identity value. > >> >> > >> >> -Raymond > >> >> > >> >> "Terry Wahl" <TerryWahl@discussions.microsoft.com> wrote in message > >> >> news:854D386A-E82D-4948-BC03-47BB3F8E8E15@microsoft.com... > >> >> > Hi Raymond, > >> >> > We are using push replication so it looks like I will have to > >> >> > execute > >> >> > the > >> >> > command by hand. But I am having problems with the 'DBCC CHECIDENT' > >> >> > command. > >> >> > The command that I am running after the data is replicatated is the > >> >> > following: > >> >> > > >> >> > DBCC CHECIDENT('dbo.TcmLatestReport', RESEED) > >> >> > > >> >> > I receive the following message: > >> >> > Checking identity information: current identity value 'NULL', > >> >> > current > >> >> > column > >> >> > value 'NULL'. > >> >> > DBCC execution completed. If DBCC printed error messages, contact > >> >> > your > >> >> > system administrator. > >> >> > > >> >> > The table contains entries so I'm not sure why the command is not > >> >> > setting > >> >> > the identiy seed to be the max value of the current value? > >> >> > > >> >> > Thanks again for your help, > >> >> > Terry > >> >> > > >> >> > > >> >> > > >> >> > > >> >> > > >> >> > "Raymond Mak [MSFT]" wrote: > >> >> > > >> >> >> Hi Terry, > >> >> >> > >> >> >> Unfortunately the only solution that I can think of is to manually > >> >> >> run > >> >> >> DBCC > >> >> >> CHECKIDENT on your subscriber tables with identity columns (you can > >> >> >> simply > >> >> >> put that in a post-snapshot script) to reset the identity seed > >> >> >> values > >> >> >> to > >> >> >> be > >> >> >> the max values of the identity columns. While it may be worthwhile > >> >> >> for > >> >> >> replication to automatically do this for you, I can think of at > >> >> >> least > >> >> >> one > >> >> >> case where doing so is not desirable (rolling up data with > >> >> >> different > >> >> >> identity ranges on a central subscriber). In any case, you may want > >> >> >> to > >> >> >> log a > >> >> >> feedback item about this @ http://connect.microsoft.com so we can > >> >> >> consider > >> >> >> doing something about this in a future release. > >> >> >> > >> >> >> -Raymond > >> >> >> > >> >> >> "Terry Wahl" <TerryWahl@discussions.microsoft.com> wrote in message > >> >> >> news:DAD169CE-0E70-40FB-97C5-51314DE515F0@microsoft.com... > >> >> >> > Replication of the tables is working but I am having an issue > >> >> >> > when > >> >> >> > adding > >> >> >> > rows to the replicated tables. There seems to be a problem with > >> >> >> > dealing > >> >> >> > with > >> >> >> > idenity columns. For instance I have a table with three columns > >> >> >> > one > >> >> >> > of > >> >> >> > which > >> >> >> > is the primary key column. When a new row is added I think the > >> >> >> > system > >> >> >> > is > >> >> >> > generating an already present value for the primary key. I am > >> >> >> > receiving a > >> >> >> > duplicate key error. Is there a configuration setting that I > >> >> >> > need > >> >> >> > to > >> >> >> > set? > >> >> >> > > >> >> >> > Thanks again for your help, > >> >> >> > Terry > >> >> >> > > >> >> >> > > >> >> >> > "Raymond Mak [MSFT]" wrote: > >> >> >> > > >> >> >> >> Hi Paul, > >> >> >> >> > >> >> >> >> Actually SQL2005 snapshot processing for transactional\snapshot > >> >> >> >> replication > >> >> >> >> should be able to handle FKs that exist only on the subscriber > >> >> >> >> reasonably
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |