none
Merge Replication advisable or not?

    Question

  • Hi,

    I have query w.r.t merge replication. I have a scenario in which synchronization of databases is required. I will have one main database available on a server and I will have other databases available on different database servers. Now the task is to synchronize the data available in other database servers to the main database. Let us consider I have one central database availabe on central server and have 10 other local databases each on a different server and different location. Now at each location specific each of the local database would be populated with the data. And once or twice in a day each location specific database would be synchronized with the central database in an offline mode, that means data would be pushed from each local database and put into the central database which would be diferentiated either on the specific host_name() or suser_name() of each of local database. So the main flow of data would be from localdb to the centraldb. The data that would flow from the central database to localdb would be common metadata or common schema data. the structure of tables at each of the locladb will be same as the centralDB or subset of centralDB. The moment there is a change required in any of the table structure it would be changed at centraldb and consequently it should be propgated to all the localdb's irrespective of location since this structure change would be applicable to all the stores. When it comes to data inside each of the structure that needs to be differentiated based on the location specific entity; here in this case we can say host_name() or suser_name(). Please note that the amount of data to be synchronized will be some where around 200 MB for each localdb on a day.

    Now my questions are:

    1) Is it good to have merge replication to achieve the above said scenario.

    2) There is possiblity to have more than 10,000 localdb's at different locations. Will it be advisable then also?

    3) I have planned to have a pull subscription with one publisher (publisher itself distributor and 10,000 subscribers. Will this be a good idea?

    4) Will I be able to start the synchronization process from both the ends( both at publisher or subscriber). Because I was able to start the synchronization process at the subscriber end but dont see any option to start the synchronization process from the publisher. Niether I am not sure about the TSQL query which runs in the back ground which instantiates either the synchronization process or the snapshot agent available at the publisher. Through wizard we just right click and select the respective option like "View Snapshot agent status" etc. How to get the transact SQL query to initaite the same?

    5) How will we install or deploy the merge replication. How can we create an installer to install each of it in all the localdb's and centralDB. If we have are providing a script to create a publication or subscription, then there we need to provide the administrator password or process account password in the script itslef which is not safe or correct process to do it. How can we automate the deployment of merge replication between a publisher and several subscribers and these subscribers will get adding up to one common publisher on a frequent basis not at once.

    6) Can i get a sample template of a custom stored procedure to resolve conflicts?

    7) If not this then whether we should go for Microsoft sync framework 4.0 to synchronize the databases? Please note that there is no possibility of having heterogenous databases. We will have MS SQL server only both at CentralDB and localDB. Then what would be pros and cons of using a sync framework over merge replication?

    Please help me out as I have not done merge replication before and going for it for the first time. Let me know if you have further queries to understand the scenario.



    • Edited by Ashish7891 Wednesday, September 28, 2011 8:22 AM
    Wednesday, September 28, 2011 7:34 AM

Answers

  • 1) yes

    2) you are pushing the limits of merge replication here. When you scale like this you will need to do republishing or heirarchies. However are we talking of thousands of seperate servers or thousands of subscriber db's? It might work better if you were to consolidate some of these subscriber db's into a smaller number.

    3) yes pull is best.

    4) yes, but if it is pull you will likely have to connect to the subscriber via ado.net and kick it off, or run xp_cmdshell to run the merge agent on the subscriber. So yes, it can be done, but not easily.

    5) for this you will need to use compiled code. Note that you can encrypt the publisher/subscriber password look at the parameter SubscriberEncryptedPassword/DistributorEncrypted on replmerg.exe. Now this is for the execution of the merge agent. For the actual deployment it will need to be compiled code.

    6)Please see the end. What you need to do is to craft a results set that you want applied on both sides of your replication topology. Note that what I am doing here, is using dynamic SQL to return the results set from the subscriber and then merging a results set from both the publisher and subscriber.

    7) I have not found synchronization services to be scalable. Your results may vary.

    ALTER PROC [dbo].[spOrderLineRec]
       (
         @tableowner SYSNAME ,
         @tablename SYSNAME ,
         @rowguid UNIQUEIDENTIFIER ,
         @subscriber SYSNAME ,
         @subscriber_db SYSNAME ,
         @log_conflict INT OUTPUT ,
         @conflict_message NVARCHAR(512) OUTPUT ,
         @destowner SYSNAME
       )
    AS
     insert into LogTable (Subscriber_DB, subscriber, str)
    values(@Subscriber_DB, @subscriber, 'logging conflict')
     set @log_conflict =2
     SET @conflict_message = 'conflict logged from '+@subscriber + ' on
    database '+@subscriber_db
    --getting subscriber data
    --SET @log_conflict = 0
       DECLARE @str NVARCHAR(MAX)
       DECLARE @newstr NVARCHAR(MAX)
       DECLARE @params NVARCHAR(MAX)
    
       DECLARE @Company NUMERIC(3, 0)
       DECLARE @Division NCHAR(3)
       DECLARE @CounterNumber NUMERIC(2, 0)
       DECLARE @OrderNumber NCHAR(10)
       DECLARE @LineNumber NUMERIC(3, 0)
       DECLARE @LineSuffix NUMERIC(2, 0)
       DECLARE @InfoType NCHAR(2)
       DECLARE @LineType NCHAR(1)
       DECLARE @HighStatus NCHAR(2)
       DECLARE @Facility NCHAR(3)
       DECLARE @Warehouse NCHAR(3)
       DECLARE @ItemNumber NCHAR(15)
       DECLARE @ReplacedItemNumber NCHAR(15)
       DECLARE @ItemName NCHAR(30)
       DECLARE @ItemDescription NCHAR(60)
       DECLARE @OrderedQtyBasic NUMERIC(15, 6)
       DECLARE @BalanceQty NUMERIC(15, 6)
       DECLARE @OrderedQtyAlt NUMERIC(15, 6)
       DECLARE @RemainingQtyBasic NUMERIC(15, 6)
       DECLARE @RemainingQtyAlt NUMERIC(15, 6)
       DECLARE @AllocatedQtyBasic NUMERIC(15, 6)
       DECLARE @AllocatedQtyAlt NUMERIC(15, 6)
       DECLARE @PickListQtyBasic NUMERIC(15, 6)
       DECLARE @PickListQtyAlt NUMERIC(15, 6)
       DECLARE @DeliveredQtyBasic NUMERIC(15, 6)
       DECLARE @DeliveredQtyAlt NUMERIC(15, 6)
       DECLARE @InvoicedQtyBasic NUMERIC(15, 6)
       DECLARE @InvoicedQtyAlt NUMERIC(15, 6)
       DECLARE @NumOfDecimal NUMERIC(1, 0)
       DECLARE @AltUOM NCHAR(3)
       DECLARE @ConversionFactor NUMERIC(15, 9)
       DECLARE @ConversionForm NUMERIC(1, 0)
       DECLARE @SalesPriceUOM NCHAR(3)
       DECLARE @PriceAdjFactor NUMERIC(15, 9)
       DECLARE @AliasNumber NCHAR(30)
       DECLARE @AliasCategory NUMERIC(2, 0)
       DECLARE @AliasQualifier NCHAR(4)
       DECLARE @SalesPrice NUMERIC(17, 6)
       DECLARE @NetPrice NUMERIC(17, 6)
       DECLARE @SalesPriceQty NUMERIC(5, 0)
       DECLARE @PriceOrigin NCHAR(1)
       DECLARE @DiscountStatus1 NUMERIC(1, 0)
       DECLARE @DiscountStatus2 NUMERIC(1, 0)
       DECLARE @DiscountStatus3 NUMERIC(1, 0)
       DECLARE @DiscountStatus4 NUMERIC(1, 0)
       DECLARE @DiscountStatus5 NUMERIC(1, 0)
       DECLARE @DiscountStatus6 NUMERIC(1, 0)
       DECLARE @Discount1 NUMERIC(5, 2)
       DECLARE @Discount2 NUMERIC(5, 2)
       DECLARE @Discount3 NUMERIC(5, 2)
       DECLARE @Discount4 NUMERIC(5, 2)
       DECLARE @Discount5 NUMERIC(5, 2)
       DECLARE @Discount6 NUMERIC(5, 2)
       DECLARE @DiscountAmount1 NUMERIC(15, 2)
       DECLARE @DiscountAmount2 NUMERIC(15, 2)
       DECLARE @DiscountAmount3 NUMERIC(15, 2)
       DECLARE @DiscountAmount4 NUMERIC(15, 2)
       DECLARE @DiscountAmount5 NUMERIC(15, 2)
       DECLARE @DiscountAmount6 NUMERIC(15, 2)
       DECLARE @InternalDiscount NUMERIC(1, 0)
       DECLARE @OrderTotalDiscount NUMERIC(1, 0)
       DECLARE @RequestedDeliveryDate NUMERIC(8, 0)
       DECLARE @RequestedDeliveryTime NUMERIC(6, 0)
       DECLARE @ConfirmDeliveryDate NUMERIC(8, 0)
       DECLARE @VATCode NUMERIC(2, 0)
       DECLARE @LineAmount NUMERIC(15, 2)
       DECLARE @LineAmount2 NUMERIC(15, 2)
       DECLARE @PriceList NCHAR(2)
       DECLARE @NetPriceUse NUMERIC(1, 0)
       DECLARE @Quantity NUMERIC(15, 6)
       DECLARE @SalesPerson NCHAR(4)
       DECLARE @CountryOfOrigin NCHAR(3)
       DECLARE @Customer NCHAR(10)
       DECLARE @Location NCHAR(10)
       DECLARE @LotNumber NCHAR(20)
       DECLARE @AddressNumber NCHAR(6)
       DECLARE @OrderType NCHAR(3)
       DECLARE @PurchasePrice NUMERIC(17, 6)
       DECLARE @PurchasePriceQty NUMERIC(5, 0)
       DECLARE @PurchasePriceUOM NCHAR(3)
       DECLARE @Currency NCHAR(3)
       DECLARE @OurReferenceNumber NCHAR(10)
       DECLARE @ReferenceType NCHAR(1)
       DECLARE @Buyer NCHAR(10)
       DECLARE @GrossWeight NUMERIC(9, 3)
       DECLARE @TareWeight NUMERIC(9, 3)
       DECLARE @NetWeight NUMERIC(9, 3)
       DECLARE @M3OrderNumber NCHAR(10)
       DECLARE @TaxApplicable NCHAR(1)
       DECLARE @SalesTaxAmount NUMERIC(15, 2)
       DECLARE @DeliveryMethod NCHAR(4)
       DECLARE @AllocFlag NUMERIC(1, 0)
       DECLARE @ReasonCode NCHAR(2)
       DECLARE @RecStatus NUMERIC(1, 0)
       DECLARE @ReplicationStatus NUMERIC(1, 0)
       DECLARE @OrderLock NUMERIC(1, 0)
       DECLARE @EntryDate NUMERIC(8, 0)
       DECLARE @EntryTime NUMERIC(6, 0)
       DECLARE @ChangeDate NUMERIC(8, 0)
       DECLARE @ChangeNumber NUMERIC(3, 0)
       DECLARE @ChangedBy NCHAR(10)
    
       SET @str = 'SELECT
    @Company = Company,
    @Division = Division,
    @CounterNumber = CounterNumber,
    @OrderNumber = OrderNumber,
    @LineNumber = LineNumber,
    @LineSuffix = LineSuffix,
    @InfoType = InfoType,
    @LineType = LineType,
    @HighStatus = HighStatus,
    @Facility = Facility,
    @Warehouse = Warehouse,
    @ItemNumber = ItemNumber,
    @ReplacedItemNumber = ReplacedItemNumber,
    @ItemName = ItemName,
    @ItemDescription = ItemDescription,
    @OrderedQtyBasic = OrderedQtyBasic,
    @BalanceQty = BalanceQty,
    @OrderedQtyAlt = OrderedQtyAlt,
    @RemainingQtyBasic = RemainingQtyBasic,
    @RemainingQtyAlt = RemainingQtyAlt,
    @AllocatedQtyBasic = AllocatedQtyBasic,
    @AllocatedQtyAlt = AllocatedQtyAlt,
    @PickListQtyBasic = PickListQtyBasic,
    @PickListQtyAlt = PickListQtyAlt,
    @DeliveredQtyBasic = DeliveredQtyBasic,
    @DeliveredQtyAlt = DeliveredQtyAlt,
    @InvoicedQtyBasic = InvoicedQtyBasic,
    @InvoicedQtyAlt = InvoicedQtyAlt,
    @NumOfDecimal = NumOfDecimal,
    @AltUOM = AltUOM,
    @ConversionFactor = ConversionFactor,
    @ConversionForm = ConversionForm,
    @SalesPriceUOM = SalesPriceUOM,
    @PriceAdjFactor = PriceAdjFactor,
    @AliasNumber = AliasNumber,
    @AliasCategory = AliasCategory,
    @AliasQualifier = AliasQualifier,
    @SalesPrice = SalesPrice,
    @NetPrice = NetPrice,
    @SalesPriceQty = SalesPriceQty,
    @PriceOrigin = PriceOrigin,
    @DiscountStatus1 = DiscountStatus1,
    @DiscountStatus2 = DiscountStatus2,
    @DiscountStatus3 = DiscountStatus3,
    @DiscountStatus4 = DiscountStatus4,
    @DiscountStatus5 = DiscountStatus5,
    @DiscountStatus6 = DiscountStatus6,
    @Discount1 = Discount1,
    @Discount2 = Discount2,
    @Discount3 = Discount3,
    @Discount4 = Discount4,
    @Discount5 = Discount5,
    @Discount6 = Discount6,
    @DiscountAmount1 = DiscountAmount1,
    @DiscountAmount2 = DiscountAmount2,
    @DiscountAmount3 = DiscountAmount3,
    @DiscountAmount4 = DiscountAmount4,
    @DiscountAmount5 = DiscountAmount5,
    @DiscountAmount6 = DiscountAmount6,
    @InternalDiscount = InternalDiscount,
    @OrderTotalDiscount = OrderTotalDiscount,
    @RequestedDeliveryDate = RequestedDeliveryDate,
    @RequestedDeliveryTime = RequestedDeliveryTime,
    @ConfirmDeliveryDate = ConfirmDeliveryDate,
    @VATCode = VATCode,
    @LineAmount = LineAmount,
    @LineAmount2 = LineAmount2,
    @PriceList = PriceList,
    @NetPriceUse = NetPriceUse,
    @Quantity = Quantity,
    @SalesPerson = SalesPerson,
    @CountryOfOrigin = CountryOfOrigin,
    @Customer = Customer,
    @Location = Location,
    @LotNumber = LotNumber,
    @AddressNumber = AddressNumber,
    @OrderType = OrderType,
    @PurchasePrice = PurchasePrice,
    @PurchasePriceQty = PurchasePriceQty,
    @PurchasePriceUOM = PurchasePriceUOM,
    @Currency = Currency,
    @OurReferenceNumber = OurReferenceNumber,
    @ReferenceType = ReferenceType,
    @Buyer = Buyer,
    @GrossWeight = GrossWeight,
    @TareWeight = TareWeight,
    @NetWeight = NetWeight,
    @M3OrderNumber = M3OrderNumber,
    @TaxApplicable = TaxApplicable,
    @SalesTaxAmount = SalesTaxAmount,
    @DeliveryMethod = DeliveryMethod,
    @AllocFlag = AllocFlag,
    @ReasonCode = ReasonCode,
    @RecStatus = RecStatus,
    @ReplicationStatus = ReplicationStatus,
    @OrderLock = OrderLock,
    @EntryDate = EntryDate,
    @EntryTime = EntryTime,
    @ChangeDate = ChangeDate,
    @ChangeNumber = ChangeNumber,
    @ChangedBy = ChangedBy
    
     FROM [@subscriber].@subscriber_db.dbo.OrderLineRec WHERE
    rowguid=''@rowguid'''
       SELECT  @newstr = REPLACE(@str, '@subscriber_db', @subscriber_db)
       SELECT  @newstr = REPLACE(@newstr, '@subscriber', @subscriber)
       SELECT  @newstr = REPLACE(@newstr, '@rowguid', @rowguid)
       insert into LogTable (Subscriber_DB, subscriber, str)
    values(@Subscriber_DB, @subscriber, @newstr)
    
       SELECT  @params = N'@subscriber sysname, @subscriber_db sysname,
    @rowguid uniqueidentifier,
       @Company NUMERIC(3, 0) OUTPUT,
       @Division NCHAR(4) OUTPUT,
       @CounterNumber NUMERIC(2, 0) OUTPUT,
       @OrderNumber NCHAR(11) OUTPUT,
       @LineNumber NUMERIC(3, 0) OUTPUT,
       @LineSuffix NUMERIC(2, 0) OUTPUT,
       @InfoType NCHAR(3) OUTPUT,
       @LineType NCHAR(2) OUTPUT,
       @HighStatus NCHAR(3) OUTPUT,
       @Facility NCHAR(4) OUTPUT,
       @Warehouse NCHAR(4) OUTPUT,
       @ItemNumber NCHAR(16) OUTPUT,
       @ReplacedItemNumber NCHAR(16) OUTPUT,
       @ItemName NCHAR(31) OUTPUT,
       @ItemDescription NCHAR(61) OUTPUT,
       @OrderedQtyBasic NUMERIC(15, 6) OUTPUT,
       @BalanceQty NUMERIC(15, 6) OUTPUT,
       @OrderedQtyAlt NUMERIC(15, 6) OUTPUT,
       @RemainingQtyBasic NUMERIC(15, 6) OUTPUT,
       @RemainingQtyAlt NUMERIC(15, 6) OUTPUT,
       @AllocatedQtyBasic NUMERIC(15, 6) OUTPUT,
       @AllocatedQtyAlt NUMERIC(15, 6) OUTPUT,
       @PickListQtyBasic NUMERIC(15, 6) OUTPUT,
       @PickListQtyAlt NUMERIC(15, 6) OUTPUT,
       @DeliveredQtyBasic NUMERIC(15, 6) OUTPUT,
       @DeliveredQtyAlt NUMERIC(15, 6) OUTPUT,
       @InvoicedQtyBasic NUMERIC(15, 6) OUTPUT,
       @InvoicedQtyAlt NUMERIC(15, 6) OUTPUT,
       @NumOfDecimal NUMERIC(1, 0) OUTPUT,
       @AltUOM NCHAR(4) OUTPUT,
       @ConversionFactor NUMERIC(15, 9) OUTPUT,
       @ConversionForm NUMERIC(1, 0) OUTPUT,
       @SalesPriceUOM NCHAR(4) OUTPUT,
       @PriceAdjFactor NUMERIC(15, 9) OUTPUT,
       @AliasNumber NCHAR(31) OUTPUT,
       @AliasCategory NUMERIC(2, 0) OUTPUT,
       @AliasQualifier NCHAR(5) OUTPUT,
       @SalesPrice NUMERIC(17, 6) OUTPUT,
       @NetPrice NUMERIC(17, 6) OUTPUT,
       @SalesPriceQty NUMERIC(5, 0) OUTPUT,
       @PriceOrigin NCHAR(2) OUTPUT,
       @DiscountStatus1 NUMERIC(1, 0) OUTPUT,
       @DiscountStatus2 NUMERIC(1, 0) OUTPUT,
       @DiscountStatus3 NUMERIC(1, 0) OUTPUT,
       @DiscountStatus4 NUMERIC(1, 0) OUTPUT,
       @DiscountStatus5 NUMERIC(1, 0) OUTPUT,
       @DiscountStatus6 NUMERIC(1, 0) OUTPUT,
       @Discount1 NUMERIC(5, 2) OUTPUT,
       @Discount2 NUMERIC(5, 2) OUTPUT,
       @Discount3 NUMERIC(5, 2) OUTPUT,
       @Discount4 NUMERIC(5, 2) OUTPUT,
       @Discount5 NUMERIC(5, 2) OUTPUT,
       @Discount6 NUMERIC(5, 2) OUTPUT,
       @DiscountAmount1 NUMERIC(15, 2) OUTPUT,
       @DiscountAmount2 NUMERIC(15, 2) OUTPUT,
       @DiscountAmount3 NUMERIC(15, 2) OUTPUT,
       @DiscountAmount4 NUMERIC(15, 2) OUTPUT,
       @DiscountAmount5 NUMERIC(15, 2) OUTPUT,
       @DiscountAmount6 NUMERIC(15, 2) OUTPUT,
       @InternalDiscount NUMERIC(1, 0) OUTPUT,
       @OrderTotalDiscount NUMERIC(1, 0) OUTPUT,
       @RequestedDeliveryDate NUMERIC(8, 0) OUTPUT,
       @RequestedDeliveryTime NUMERIC(6, 0) OUTPUT,
       @ConfirmDeliveryDate NUMERIC(8, 0) OUTPUT,
       @VATCode NUMERIC(2, 0) OUTPUT,
       @LineAmount NUMERIC(15, 2) OUTPUT,
       @LineAmount2 NUMERIC(15, 2) OUTPUT,
       @PriceList NCHAR(3) OUTPUT,
       @NetPriceUse NUMERIC(1, 0) OUTPUT,
       @Quantity NUMERIC(15, 6) OUTPUT,
       @SalesPerson NCHAR(5) OUTPUT,
       @CountryOfOrigin NCHAR(4) OUTPUT,
     @Customer NCHAR(11) OUTPUT,
       @Location NCHAR(11) OUTPUT,
       @LotNumber NCHAR(21) OUTPUT,
       @AddressNumber NCHAR(7) OUTPUT,
       @OrderType NCHAR(4) OUTPUT,
       @PurchasePrice NUMERIC(17, 6) OUTPUT,
       @PurchasePriceQty NUMERIC(5, 0) OUTPUT,
       @PurchasePriceUOM NCHAR(4) OUTPUT,
       @Currency NCHAR(4) OUTPUT,
       @OurReferenceNumber NCHAR(11) OUTPUT,
       @ReferenceType NCHAR(2) OUTPUT,
       @Buyer NCHAR(11) OUTPUT,
       @GrossWeight NUMERIC(9, 3) OUTPUT,
       @TareWeight NUMERIC(9, 3) OUTPUT,
       @NetWeight NUMERIC(9, 3) OUTPUT,
       @M3OrderNumber NCHAR(11) OUTPUT,
       @TaxApplicable NCHAR(2) OUTPUT,
       @SalesTaxAmount NUMERIC(15, 2) OUTPUT,
       @DeliveryMethod NCHAR(5) OUTPUT,
       @AllocFlag NUMERIC(1, 0) OUTPUT,
       @ReasonCode NCHAR(3) OUTPUT,
       @RecStatus NUMERIC(1, 0) OUTPUT,
       @ReplicationStatus NUMERIC(1, 0) OUTPUT,
       @OrderLock NUMERIC(1, 0) OUTPUT,
       @EntryDate NUMERIC(8, 0) OUTPUT,
       @EntryTime NUMERIC(6, 0) OUTPUT,
       @ChangeDate NUMERIC(8, 0) OUTPUT,
       @ChangeNumber NUMERIC(3, 0) OUTPUT,
       @ChangedBy NCHAR(11) OUTPUT
    '
       EXEC sp_executesql @newstr, @params, @subscriber, @subscriber_db,
    @rowguid,
           @Company = @Company OUTPUT, @Division = @Division OUTPUT,
           @CounterNumber = @CounterNumber, @OrderNumber = @OrderNumber OUTPUT,
           @LineNumber = @LineNumber OUTPUT, @LineSuffix = @LineSuffix OUTPUT,
           @InfoType = @InfoType OUTPUT, @LineType = @LineType OUTPUT,
       @HighStatus = @HighStatus OUTPUT, @Facility = @Facility OUTPUT,
           @Warehouse = @Warehouse OUTPUT, @ItemNumber = @ItemNumber OUTPUT,
           @ReplacedItemNumber = @ReplacedItemNumber OUTPUT,
           @ItemName = @ItemName OUTPUT,
           @ItemDescription = @ItemDescription OUTPUT,
           @OrderedQtyBasic = @OrderedQtyBasic OUTPUT,
           @BalanceQty = @BalanceQty OUTPUT,
           @OrderedQtyAlt = @OrderedQtyAlt OUTPUT,
           @RemainingQtyBasic = @RemainingQtyBasic OUTPUT,
           @RemainingQtyAlt = @RemainingQtyAlt OUTPUT,
           @AllocatedQtyBasic = @AllocatedQtyBasic OUTPUT,
           @AllocatedQtyAlt = @AllocatedQtyAlt OUTPUT,
           @PickListQtyBasic = @PickListQtyBasic OUTPUT,
           @PickListQtyAlt = @PickListQtyAlt OUTPUT,
           @DeliveredQtyBasic = @DeliveredQtyBasic OUTPUT,
           @DeliveredQtyAlt = @DeliveredQtyAlt OUTPUT,
           @InvoicedQtyBasic = @InvoicedQtyBasic OUTPUT,
           @InvoicedQtyAlt = @InvoicedQtyAlt OUTPUT,
           @NumOfDecimal = @NumOfDecimal OUTPUT, @AltUOM = @AltUOM OUTPUT,
           @ConversionFactor = @ConversionFactor OUTPUT,
           @ConversionForm = @ConversionForm OUTPUT,
           @SalesPriceUOM = @SalesPriceUOM OUTPUT,
           @PriceAdjFactor = @PriceAdjFactor OUTPUT,
           @AliasNumber = @AliasNumber OUTPUT,
           @AliasCategory = @AliasCategory OUTPUT,
           @AliasQualifier = @AliasQualifier OUTPUT,
           @SalesPrice = @SalesPrice OUTPUT, @NetPrice = @NetPrice OUTPUT,
           @SalesPriceQty = @SalesPriceQty OUTPUT,
           @PriceOrigin = @PriceOrigin OUTPUT,
           @DiscountStatus1 = @DiscountStatus1 OUTPUT,
           @DiscountStatus2 = @DiscountStatus2 OUTPUT,
           @DiscountStatus3 = @DiscountStatus3 OUTPUT,
           @DiscountStatus4 = @DiscountStatus4 OUTPUT,
           @DiscountStatus5 = @DiscountStatus5 OUTPUT,
           @DiscountStatus6 = @DiscountStatus6 OUTPUT,
           @Discount1 = @Discount1 OUTPUT, @Discount2 = @Discount2 OUTPUT,
           @Discount3 = @Discount3 OUTPUT, @Discount4 = @Discount4 OUTPUT,
           @Discount5 = @Discount5 OUTPUT, @Discount6 = @Discount6 OUTPUT,
           @DiscountAmount1 = @DiscountAmount1 OUTPUT,
           @DiscountAmount2 = @DiscountAmount2 OUTPUT,
           @DiscountAmount3 = @DiscountAmount3 OUTPUT,
           @DiscountAmount4 = @DiscountAmount4 OUTPUT,
           @DiscountAmount5 = @DiscountAmount5 OUTPUT,
         @DiscountAmount6 = @DiscountAmount6 OUTPUT,
           @InternalDiscount = @InternalDiscount OUTPUT,
           @OrderTotalDiscount = @OrderTotalDiscount OUTPUT,
           @RequestedDeliveryDate = @RequestedDeliveryDate OUTPUT,
           @RequestedDeliveryTime = @RequestedDeliveryTime OUTPUT,
           @ConfirmDeliveryDate = @ConfirmDeliveryDate OUTPUT,
           @VATCode = @VATCode OUTPUT, @LineAmount = @LineAmount OUTPUT,
           @LineAmount2 = @LineAmount2, @PriceList = @PriceList OUTPUT,
           @NetPriceUse = @NetPriceUse OUTPUT, @Quantity = @Quantity OUTPUT,
           @SalesPerson = @SalesPerson OUTPUT,
           @CountryOfOrigin = @CountryOfOrigin OUTPUT,
           @Customer = @Customer OUTPUT, @Location = @Location OUTPUT,
           @LotNumber = @LotNumber OUTPUT, @AddressNumber =
    @AddressNumber OUTPUT,
           @OrderType = @OrderType OUTPUT, @PurchasePrice =
    @PurchasePrice OUTPUT,
           @PurchasePriceQty = @PurchasePriceQty OUTPUT,
           @PurchasePriceUOM = @PurchasePriceUOM OUTPUT,
           @Currency = @Currency OUTPUT,
           @OurReferenceNumber = @OurReferenceNumber OUTPUT,
           @ReferenceType = @ReferenceType OUTPUT, @Buyer = @Buyer OUTPUT,
           @GrossWeight = @GrossWeight OUTPUT, @TareWeight = @TareWeight OUTPUT,
           @NetWeight = @NetWeight OUTPUT, @M3OrderNumber =
    @M3OrderNumber OUTPUT,
           @TaxApplicable = @TaxApplicable OUTPUT,
           @SalesTaxAmount = @SalesTaxAmount OUTPUT,
           @DeliveryMethod = @DeliveryMethod OUTPUT,
           @AllocFlag = @AllocFlag OUTPUT, @ReasonCode = @ReasonCode OUTPUT,
           @RecStatus = @RecStatus OUTPUT,
           @ReplicationStatus = @ReplicationStatus OUTPUT,
           @OrderLock = @OrderLock OUTPUT, @EntryDate = @EntryDate OUTPUT,
           @EntryTime = @EntryTime OUTPUT, @ChangeDate = @ChangeDate OUTPUT,
           @ChangeNumber = @ChangeNumber OUTPUT, @ChangedBy = @ChangedBy
    
       DECLARE @PublisherReplicationStatus NUMERIC(1, 0)
       DECLARE @PublisherRecStatus NUMERIC(1, 0)
       DECLARE @PublisherM3OrderNumber NCHAR(10)
           DECLARE @PublisherHighStatus NCHAR(2) -- AR210010726
    
    --getting values from Publisher
    
       SET @str = 'SELECT @PublisherReplicationStatus=replicationstatus,
    @PublisherRecStatus=recstatus,@PublisherM3OrderNumber=M3OrderNumber,@PublisherHighStatus=HighStatus
    FROM Pub1.dbo.OrderLineRec WHERE rowguid=''@rowguid'''
       SELECT  @newstr = REPLACE(@str, '@rowguid', @rowguid)
       SELECT  @params = N'@rowguid uniqueidentifier,
    @PublisherM3OrderNumber nchar(10) OUTPUT, @PublisherReplicationStatus
    numeric(1, 0) OUTPUT, @PublisherRecStatus numeric(1, 0) OUTPUT,
    @PublisherHighStatus nchar(2) OUTPUT'
       EXEC sp_executesql @newstr, @params, @rowguid,
       @PublisherM3OrderNumber = @PublisherM3OrderNumber OUTPUT,
    @PublisherRecStatus = @PublisherRecStatus OUTPUT,
       @PublisherReplicationStatus = @PublisherReplicationStatus OUTPUT,
    @PublisherHighStatus = @PublisherHighStatus OUTPUT
           insert into LogTable (Subscriber_DB, subscriber, str)
    values(@Subscriber_DB, @subscriber, @newstr)
    
       IF @OrderLock = 1 AND @PublisherHighStatus < 79 --subscriber winner
           BEGIN
                           insert into LogTable (Subscriber_DB, subscriber, str)
    values(@Subscriber_DB, @subscriber, 'inside Branch 1')
    
               --SET @log_conflict = 2
               SELECT  @Company ,
                       RTRIM(@Division) +' ',
                       @CounterNumber ,
                       RTRIM(@OrderNumber)  +' ',
                       @LineNumber ,
                       @LineSuffix ,
                       RTRIM(@InfoType)   +' ',
                       RTRIM(@LineType)   +' ',
                       RTRIM(@HighStatus)   +' ',
                       RTRIM(@Facility)   +' ',
                       RTRIM(@Warehouse)   +' ',
                       RTRIM(@ItemNumber)   +' ',
                       RTRIM(@ReplacedItemNumber)   +' ',
                       RTRIM(@ItemName)   +' ',
                       RTRIM(@ItemDescription)   +' ',
                       @OrderedQtyBasic ,
                       @BalanceQty ,
                       @OrderedQtyAlt ,
                       @RemainingQtyBasic ,
                       @RemainingQtyAlt ,
                       @AllocatedQtyBasic ,
                       @AllocatedQtyAlt ,
                       @PickListQtyBasic ,
                       @PickListQtyAlt ,
                       @DeliveredQtyBasic ,
                       @DeliveredQtyAlt ,
                       @InvoicedQtyBasic ,
                       @InvoicedQtyAlt ,
                       @NumOfDecimal ,
                       RTRIM(@AltUOM)   +' ',
                       @ConversionFactor ,
                       @ConversionForm ,
                       RTRIM(@SalesPriceUOM)   +' ',
                       @PriceAdjFactor ,
                       RTRIM(@AliasNumber) +' ',
                       @AliasCategory ,
                       RTRIM(@AliasQualifier) +' ',
                       @SalesPrice ,
                       @NetPrice ,
                       @SalesPriceQty ,
                       RTRIM(@PriceOrigin) +' ',
                       @DiscountStatus1 ,
                       @DiscountStatus2 ,
                                           @DiscountStatus3 ,
                       @DiscountStatus4 ,
                       @DiscountStatus5 ,
                       @DiscountStatus6 ,
                       @Discount1 ,
                       @Discount2 ,
                       @Discount3 ,
                                           @Discount4 ,
                       @Discount5 ,
                       @Discount6 ,
                       @DiscountAmount1 ,
                       @DiscountAmount2 ,
                       @DiscountAmount3 ,
                       @DiscountAmount4 ,
                       @DiscountAmount5 ,
                       @DiscountAmount6 ,
                       @InternalDiscount ,
                       @OrderTotalDiscount ,
                       @RequestedDeliveryDate ,
                       @RequestedDeliveryTime ,
                       @ConfirmDeliveryDate ,
                       @VATCode ,
                       @LineAmount ,
                       @LineAmount2 ,
                       RTRIM(@PriceList)+' ',
                       @NetPriceUse ,
                       @Quantity ,
                       RTRIM(@SalesPerson)+' ',
                       RTRIM(@CountryOfOrigin) +' ',
                       RTRIM(@Customer)+' ',
                       RTRIM(@Location) +' ',
                       RTRIM(@LotNumber) +' ',
                       RTRIM(@AddressNumber) +' ',
                       RTRIM(@OrderType) +' ',
                       @PurchasePrice ,
                       @PurchasePriceQty ,
                       RTRIM(@PurchasePriceUOM)+' ',
                       @Currency+' ',
                       RTRIM(@OurReferenceNumber) +' ',
                       RTRIM(@ReferenceType)+' ',
                       RTRIM(@Buyer) +' ',
                       @GrossWeight ,
                       @TareWeight ,
                       @NetWeight ,
                       RTRIM(@PublisherM3OrderNumber) +' ',
                       RTRIM(@TaxApplicable) +' ',
                       @SalesTaxAmount ,
                       RTRIM(@DeliveryMethod) +' ',
                       @AllocFlag ,
                       RTRIM(@ReasonCode) +' ',
                       @PublisherRecStatus ,
                       @PublisherReplicationStatus ,
                       @OrderLock ,
                       @EntryDate ,
                       @EntryTime ,
                       @ChangeDate ,
                       @ChangeNumber ,
                       RTRIM(@ChangedBy)+' ',
                       @rowguid
           END
    
    
       IF @OrderLock = 0 OR (@OrderLock = 1 AND @PublisherHighStatus >=
    79) --publisher winner
           BEGIN
                           insert into LogTable (Subscriber_DB, subscriber, str)
    values(@Subscriber_DB, @subscriber, 'Inside Branch 2')
              -- SET @log_conflict = 1
               SELECT  Company ,
               RTRIM(Division) +' ',
                       CounterNumber ,
                       RTRIM(OrderNumber)+' ',
                       LineNumber ,
                       LineSuffix ,
                       RTRIM(@InfoType) +' ',
                       RTRIM(@LineType)+' ',
                       RTRIM(@HighStatus)+' ',
                       RTRIM(Facility) +' ',
                       RTRIM(Warehouse) +' ',
                                           RTRIM(ItemNumber) +' ',
                       RTRIM(ReplacedItemNumber) +' ',
                       RTRIM(ItemName) +' ',
                       RTRIM(ItemDescription) +' ',
                       @OrderedQtyBasic ,
                       @BalanceQty ,
                       OrderedQtyAlt ,
                       @RemainingQtyBasic ,
                       RemainingQtyAlt ,
                       @AllocatedQtyBasic ,
                       AllocatedQtyAlt ,
                       @PickListQtyBasic ,
                       PickListQtyAlt ,
                       @DeliveredQtyBasic ,
                       DeliveredQtyAlt ,
                       @InvoicedQtyBasic ,
                       InvoicedQtyAlt ,
                       NumOfDecimal ,
                       RTRIM(AltUOM) +' ',
                       ConversionFactor ,
                       ConversionForm ,
                       RTRIM(SalesPriceUOM) +' ',
                       PriceAdjFactor ,
                       RTRIM(AliasNumber) +' ',
                       AliasCategory ,
                       RTRIM(AliasQualifier) +' ',
                       @SalesPrice ,
                       NetPrice ,
                       SalesPriceQty ,
                       RTRIM(PriceOrigin) +' ',
                       DiscountStatus1 ,
                       DiscountStatus2 ,
                       DiscountStatus3 ,
                       DiscountStatus4 ,
                       DiscountStatus5 ,
                       DiscountStatus6 ,
                       @Discount1 ,
                       Discount2 ,
                       Discount3 ,
                       Discount4 ,
                       Discount5 ,
                       Discount6 ,
                       DiscountAmount1 ,
                       DiscountAmount2 ,
                       DiscountAmount3 ,
                       DiscountAmount4 ,
                       DiscountAmount5 ,
                       DiscountAmount6 ,
                       InternalDiscount ,
                       OrderTotalDiscount ,
                       RequestedDeliveryDate ,
                       RequestedDeliveryTime ,
                       ConfirmDeliveryDate ,
                       VATCode ,
                       @LineAmount ,
                       @LineAmount2 ,
                       RTRIM(PriceList) +' ',
                       NetPriceUse ,
                       Quantity ,
                       RTRIM(SalesPerson)+' ',
                       RTRIM(CountryOfOrigin)+' ',
                       RTRIM(Customer)+' ',
                       RTRIM(@Location) +' ',
                       RTRIM(@LotNumber) +' ',
                       RTRIM(@AddressNumber) +' ',
                       RTRIM(OrderType) +' ',
                       PurchasePrice ,
                       PurchasePriceQty ,
                                           RTRIM(PurchasePriceUOM) +' ',
                       RTRIM(Currency) +' ',
                       RTRIM(OurReferenceNumber)+' ',
                       RTRIM(ReferenceType) +' ',
                       RTRIM(Buyer) +' ',
                       GrossWeight ,
                       TareWeight ,
                       NetWeight ,
                       RTRIM(M3OrderNumber) +' ',
                       RTRIM(@TaxApplicable) +' ',
                       @SalesTaxAmount ,
                       RTRIM(@DeliveryMethod) +' ',
                       @AllocFlag ,
                       RTRIM(@ReasonCode) +' ',
                       RecStatus ,
                       ReplicationStatus ,
                       @OrderLock ,
                       EntryDate ,
                       EntryTime ,
                       ChangeDate ,
                       ChangeNumber ,
                       RTRIM(ChangedBy) +' ',
                                       rowguid
               FROM    dbo.OrderLineRec
               WHERE   rowguid = @rowguid
           END
           insert into LogTable (Subscriber_DB, subscriber, str)
    values(@Subscriber_DB, @subscriber, @log_conflict)
    
    
    


     

     


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    • Proposed as answer by Stephanie Lv Tuesday, October 04, 2011 12:59 PM
    • Marked as answer by Stephanie Lv Friday, October 07, 2011 12:06 PM
    Wednesday, September 28, 2011 11:56 AM
    Moderator