InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 22:00:21 GMT20http://www.sqlservercentral.com/Forums/Topic1415066-1549-1.aspxSnapshot replication is usually a good option if you generally have lots of activity happening on a large database. I would not recommend snapshot replication since this would replace the historical data in your reporting system with just that days data available in the production environment.Thu, 18 Apr 2013 10:58:20 GMTmmartin1http://www.sqlservercentral.com/Forums/Topic1415066-1549-1.aspx[quote][b]AccDba (2/6/2013)[/b][hr]I found the stored proc that needs to be modified. I am not sure where the changes have to be made and I need some help from experts on this. In the stored proc below I want to update subscriber tables only if col6 value gets updated and do nothing if other columns get updated.[code="sql"]USE [CCF_SubscriberDB]GO/****** Object: StoredProcedure [dbo].[sp_MSupd_dbo2006] Script SET QUOTED_IDENTIFIER ONGOALTER procedure [dbo].[sp_MSupd_dbo2006]@c1 datetime = NULL,@c2 int = NULL,@c3 int = NULL,@c4 int = NULL,@c5 int = NULL,@c6 int = NULL,@c7 money = NULL,@c8 nvarchar(20) = NULL,@pkc1 datetime = NULL,@bitmap binary(1)asbegin if (substring(@bitmap,1,1) & 1 = 1))begin update [dbo].[2006] set[Date] = case substring(@bitmap,1,1) & 1 when 1 then @c1 else [Date] end,[col1] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [col1] end,[col2] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [col2] end,[col3] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [col3] end,[col4] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else [col4] end,[col5] = case substring(@bitmap,1,1) & 32 when 32 then @c6 else [col5] end,[col6] = case substring(@bitmap,1,1) & 64 when 64 then @c7 else [col6] end,[col7] = case substring(@bitmap,1,1) & 128 when 128 then @c8 else [col7] endwhere [Date] = @pkc1if @@rowcount = 0if @@microsoftversion>0x07320000exec sp_MSreplraiserror 20598end elsebegin update [dbo].[2006] set[col1] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [col1] end,[col2] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [col2] end,[col3] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [col3] end,[col4] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else [col4] end,[col5] = case substring(@bitmap,1,1) & 32 when 32 then @c6 else [col5] end,[col6] = case substring(@bitmap,1,1) & 64 when 64 then @c7 else [col6] end,[col7] = case substring(@bitmap,1,1) & 128 when 128 then @c8 else [col7] endwhere [Date] = @pkc1if @@rowcount = 0if @@microsoftversion>0x07320000exec sp_MSreplraiserror 20598end end GOThanks,[/code][/quote]In the above update statement delete columns except for col6 and then add only the below update in both places:[code="sql"]update dbo.2006 set [col6] = case substring(@bitmap,1,1) & 64 when 64 then @c7 else [col6] end where [Date] = @pkc1[/code]Wed, 06 Feb 2013 14:28:59 GMTSapenhttp://www.sqlservercentral.com/Forums/Topic1415066-1549-1.aspxI found the stored proc that needs to be modified. I am not sure where the changes have to be made and I need some help from experts on this. In the stored proc below I want to update subscriber tables only if col6 value gets updated and do nothing if other columns get updated.[code="sql"]USE [CCF_SubscriberDB]GO/****** Object: StoredProcedure [dbo].[sp_MSupd_dbo2006] Script SET QUOTED_IDENTIFIER ONGOALTER procedure [dbo].[sp_MSupd_dbo2006]@c1 datetime = NULL,@c2 int = NULL,@c3 int = NULL,@c4 int = NULL,@c5 int = NULL,@c6 int = NULL,@c7 money = NULL,@c8 nvarchar(20) = NULL,@pkc1 datetime = NULL,@bitmap binary(1)asbegin if (substring(@bitmap,1,1) & 1 = 1))begin update [dbo].[2006] set[Date] = case substring(@bitmap,1,1) & 1 when 1 then @c1 else [Date] end,[col1] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [col1] end,[col2] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [col2] end,[col3] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [col3] end,[col4] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else [col4] end,[col5] = case substring(@bitmap,1,1) & 32 when 32 then @c6 else [col5] end,[col6] = case substring(@bitmap,1,1) & 64 when 64 then @c7 else [col6] end,[col7] = case substring(@bitmap,1,1) & 128 when 128 then @c8 else [col7] endwhere [Date] = @pkc1if @@rowcount = 0if @@microsoftversion>0x07320000exec sp_MSreplraiserror 20598end elsebegin update [dbo].[2006] set[col1] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [col1] end,[col2] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [col2] end,[col3] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [col3] end,[col4] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else [col4] end,[col5] = case substring(@bitmap,1,1) & 32 when 32 then @c6 else [col5] end,[col6] = case substring(@bitmap,1,1) & 64 when 64 then @c7 else [col6] end,[col7] = case substring(@bitmap,1,1) & 128 when 128 then @c8 else [col7] endwhere [Date] = @pkc1if @@rowcount = 0if @@microsoftversion>0x07320000exec sp_MSreplraiserror 20598end end GOThanks,[/code]Wed, 06 Feb 2013 08:38:46 GMTAccDbahttp://www.sqlservercentral.com/Forums/Topic1415066-1549-1.aspxYou could customize the replication update proc on the subscriber to only update the columns you want.You custom proc script would have to be a post_snapshot_script (@post_snapshot_script in sp_add_publication)Tue, 05 Feb 2013 16:26:47 GMTarnipeturssonhttp://www.sqlservercentral.com/Forums/Topic1415066-1549-1.aspx[quote][b]Bhuvnesh (2/3/2013)[/b][hr][quote][b]AccDba (2/3/2013)[/b][hr]I will create a new database and put these 45 tables keep actual transactional database small and slim. So its like having a copy of the same tables but this copies will be used for reporting only from a different database. [/quote]How will you uodate the tables in NEw database from main database. ? Batch process ? Partitoning ? [/quote]I am thinking about transaction replication where the publisher, distributor and subscriber are located on the same database server instance.[quote][quote][b]AccDba (2/3/2013)[/b][hr]the issue here is when I run a purge job (maintenance) on a nightly basis on the transactional tables how would i prevent the impact of not getting the data deleted from the reporting tables. [/quote] Where do you run this job , on Main(source) database ?[/quote]The purge/delete job is run on the source database which the publisher database. But I came to know that we have a fix for this as mentioned in the below url: [url]http://www.mssqltips.com/sqlservertip/2520/options-to-not-replicate-sql-server-delete-commands/[/url]But can you tell me if there is a way that I can restrict the updates specific only to a column? Lets say if updates occur on source table(publisher database) on column a, column b and column c then I dont want any thing to get updated in the destination database tables (subscriber database). But if update occurs on column d on source table(publisher database) then I want it to get updated in destination table too (subscriber database). Is this possible?Thanks AgainTue, 05 Feb 2013 12:35:26 GMTAccDbahttp://www.sqlservercentral.com/Forums/Topic1415066-1549-1.aspxI assume that you want to replicate insertions from the Publisher, but keep them on the Subscriber for longer; so when they are deleted from the Publisher the Subscriber still has them?I don't have a development environment in front of me right now, but you can replicate just the inserts to a subscriber database. Using this method you can keep data on the Subscriber for a different length of time than the Publisher.The command for adding articles (sp_addarticle) has parameters (if I recall correctly) for @ins_cmd (the insert process), @del_cmd(for the deletion process) and @upd_cmd(for the update process).If you have 'NONE' set for @del_cmd and @upd_cmd then the replication will only process Inserts; Deletions and Updates won't have any impact on the Subscriber.You can achieve this by scripting the Add Article or within the Wizard there is a 'Statement Delivery' section within the 'Article Properties' option when selecting the Articles. 'Update Delivery format' can be set to 'Do Not replicate' and 'Delete delivery format' set to 'Do not Replicate'.Mon, 04 Feb 2013 02:20:48 GMTBrainDonorhttp://www.sqlservercentral.com/Forums/Topic1415066-1549-1.aspx[quote][b]AccDba (2/3/2013)[/b][hr]I will create a new database and put these 45 tables keep actual transactional database small and slim. So its like having a copy of the same tables but this copies will be used for reporting only from a different database. [/quote]How will you uodate the tables in NEw database from main database. ? Batch process ? Partitoning ? [quote][b]AccDba (2/3/2013)[/b][hr]the issue here is when I run a purge job (maintenance) on a nightly basis on the transactional tables how would i prevent the impact of not getting the data deleted from the reporting tables. [/quote] Where do you run this job , on Main(source) database ?Sun, 03 Feb 2013 23:43:21 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1415066-1549-1.aspxWe are implementing a new architecture for one our modules. There are 100 tables in the database and out of which 45 tables are using for reporting extensively.However I want to seperate out to the tables used for reporting meaning I will create a new database and put these 45 tables keep actual transactional database small and slim. So its like having a copy of the same tables but this copies will be used for reporting only from a different database. The retention of data in source tables is 1 day where as the retention for the data in reporting(or destination) tables is an year and a half. so I was thinking of implementing replication based on a condition like based on a specific column value. But the issue here is when I run a purge job (maintenance) on a nightly basis on the transactional tables how would i prevent the impact of not getting the data deleted from the reporting tables. The application that reads data from reporting tables needs the data to present all the time (cannot break replication at any given point of time meaning data should get replicated continuously)Appreciate your thoughts and inputsSun, 03 Feb 2013 15:59:30 GMTAccDba