Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Where are the SQL Server 2008 R2 Visual Basic APIs for IVBCustomResolver, IReplRowChange, and IConnectionInfo (Code examples?)

已答覆 Where are the SQL Server 2008 R2 Visual Basic APIs for IVBCustomResolver, IReplRowChange, and IConnectionInfo (Code examples?)

All Replies

  • Friday, November 30, 2012 1:01 AM
    Moderator
     
     Answered
    Sign In to Vote
    1

    There is no link for SQL Server 2008 R2.

    I also have not seen much example code for COM-based resolvers except for the links you provided and the samples that ship with SQL Server 2000.  My guess is the API has not changed much if at all.

    I will do some testing over the next day or two and see if I can come up with any content worth posting.

    Brandon Williams (blog | linkedin)

     
  • Friday, November 30, 2012 6:33 AM
    Moderator
     
     Answered
    Sign In to Vote
    1

    Taking a closer look at this, I believe the sample provided at Paul Ibison's site is good (http://www.replicationanswers.com/CustomResolver.asp), especially if you will be developing in VB.NET.  It provides everything you need to get started.

    If I do post some content, I'll demonstrate the advantages/disadvantages of COM-based resolvers vs. business logic handler resolvers and an example of a COM-based resolver written in C#.

    Brandon Williams (blog | linkedin)

     
  • Friday, November 30, 2012 5:19 PM
     
     
    Sign In to Vote
    0

    The below link has constants for SQL Server 2000 and not SQL Server 2008 R2. Do you have a link for SQL Server 2008 R2?

    http://technet.microsoft.com/en-us/library/aa902635(v=SQL.80).aspx

    For example, REPOLE_SERVER_MINOR_VERSION has information for only SQL Server 2000.

    Here is example code, http://www.replicationanswers.com/CustomResolver.asp. Do you have others online.

    I'm still only investigating things!

    On first look, I feel this way offers a well documented 2000 API, fantastic.

    First, you use the three .NET utility tools on the Microsoft file called replrec.dll. But, an intermediate step involves modifying one of the created files, and you have to know how to do this. Conclusion, there is still a lot of guess-work involved though in what you have to program. To seriously start programming, I need more descriptive examples that demonstrate the various methods of this API.

    I'm also going to look into using the Business logic handler resolver using C#. So, far it appear this will involve more guess-work than the COM based resolver. I need more discriptive examples that demonstrate much more of this second API.





     
  • Friday, November 30, 2012 5:38 PM
    Moderator
     
     
    Sign In to Vote
    1

    Actually the business logic handler framework is more well documented than the COM API.  The entire business logic handler framework API is documented and is up to date.

    Execute Business Logic During Merge Synchronization

    Microsoft.SqlServer.Replication.BusinessLogicSupport Namespace

    I will start writing up some COM-based examples soon.

    Brandon Williams (blog | linkedin)

     
  • Sunday, December 02, 2012 12:06 AM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    1

    Okay, I am convinced you will need to utilize a COM-based resolver to accurately accomplish your goal(s) as you require to know if particular columns have been updated and resolve accordingly.  A COM-based resolver will offer column-level version information, a business logic resolver will not.

    Here is an example COM-based resolver to detect if your integer field is involved in an update-update conflict, checks if they have been updated to the same value on both source and destination, and if so, increments the value by 1 and saves the change.

    using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Text;
using SQLResolver_import;
using System.Runtime.InteropServices;

namespace COMConflictResolver_CSharp
{
    [Guid(COMCustomResolver.ClassId)]
    public class COMCustomResolver : ICustomResolver
    {
        private const int MAX_BUFFER_SIZE = 1048576;
        private const int MAX_NAME_LENGTH = 128;

        public const string ClassId = "825818F7-3531-4524-8B07-72343EFDC8AB";
        public const string InterfaceId = "CECFBB8F-584F-4733-9373-B69AFA6F117F";
        public const string EventsId = "5D55BA40-A438-4FD4-BA8B-05095DC89948";

        public COMCustomResolver()
            : base()
        {
        }

        public void GetHandledStates(ref uint ResolverBM)
        {
            ResolverBM = (uint)SQLResolver_import.REPOLE_CHANGE_TYPE.REPOLEUpdateConflicts;
        }

        public void Reconcile(IReplRowChange pRowChange, int dwFlags, IReplRowChange pvReserved)
        {
            uint cntColumns = 0;
            StringBuilder strColumnName = null;
            string columnName = null;

            bool blnIntegerIncremented = false;
            SQLResolver_import.REPOLE_COLSTATUS_TYPE ColStatus = default(SQLResolver_import.REPOLE_COLSTATUS_TYPE);
            uint intBufferLenActual = 0;
            int destinationIntegerColumnValue;
            int sourceIntegerColumnValue;
            int finalIntegerColumnValue;
            IntPtr myBuffer = Marshal.AllocHGlobal(MAX_BUFFER_SIZE);
            string strMsg = null;

            pRowChange.GetNumColumns(out cntColumns);

            for (uint i = 1; i <= cntColumns; i++)
            {
                //Get the column status of each column
                pRowChange.GetColumnStatus(i, out ColStatus);

                // If the column has been updated at both the Publisher and Subscriber
                if ((ColStatus == SQLResolver_import.REPOLE_COLSTATUS_TYPE.REPOLEColumn_UpdatedWithConflict))
                {
                    columnName = " ".PadRight(MAX_NAME_LENGTH);
                    pRowChange.GetColumnName(i, strColumnName, MAX_NAME_LENGTH);
                    columnName = strColumnName.ToString().TrimEnd();

                    // is this our integer column?
                    if ((string.Compare(columnName, "MyIntegerColumn", true) == 0))
                    {
                        // get the column values
                        pRowChange.GetDestinationColumnValue2(i, myBuffer, MAX_BUFFER_SIZE, out intBufferLenActual);
                        destinationIntegerColumnValue = myBuffer.ToInt32();
                        pRowChange.GetSourceColumnValue2(i, myBuffer, MAX_BUFFER_SIZE, out intBufferLenActual);
                        sourceIntegerColumnValue = myBuffer.ToInt32();

                        // verify the integer column was updated to the same value, ie. source = 120 and destination = 120
                        if (destinationIntegerColumnValue == sourceIntegerColumnValue)
                        {
                            // increment integer column by 1 and set the resolved data
                            finalIntegerColumnValue = sourceIntegerColumnValue + 1;
                            myBuffer = (IntPtr)finalIntegerColumnValue;
                            pRowChange.SetColumn2(i, myBuffer, MAX_BUFFER_SIZE);
                            blnIntegerIncremented = true;
                        }
                    }
                }
                else if ((ColStatus == SQLResolver_import.REPOLE_COLSTATUS_TYPE.REPOLEColumn_UpdatedNoConflict))
                {
                    // For columns that have not been updated - do nothing.
                    pRowChange.CopyColumnFromSource(i);
                }
                else if ((ColStatus == SQLResolver_import.REPOLE_COLSTATUS_TYPE.REPOLEColumn_NotUpdated))
                {
                }
            }

            // Log conflict and call the UpdateRow method to commit all the column value changes.
            if (blnIntegerIncremented)
            {
                strMsg = "Integer column - update-update conflict - column value incremented by 1";
            }
            else
            {
                strMsg = "Integer column - no conflict - column value not incremented by 1";
            }

            pRowChange.LogConflict(1, REPOLE_CONFLICT_TYPE.REPOLEConflict_ColumnUpdateConflict, 0, strMsg, 0);
            pRowChange.UpdateRow();
            Marshal.FreeHGlobal(myBuffer);
        }

        public void RemoteReconcile(SQLResolver_import.IReplRowChange pRowChange, uint dwFlags, SQLResolver_import.IReplRowChange pvReserved)
        {
        }
    }
}

    The solution is not complete nor has it been tested or debugged.  Let me know if you need any help if you end up going this route.

    Brandon Williams (blog | linkedin)

     
  • Monday, December 03, 2012 6:09 PM
     
     
    Sign In to Vote
    0

    Questions:

    (1) How would I test all of your code. What database should I create. What tables should it have. Should the C# code directly refer to a table. What should I see happen when I run it. 

    I know I have to create at least two SQL Server instances and select the radio button "Use a custom resolver (registered at the Distributor):", but what other selections should I make. I certainly shouldn't select "Microsoft SQLServer Stored Procedure Resolver."

    Image from book

    If this existed in an ideal baby step by baby step descriptive fashion, this would be an excellent tutorial, and satisfy many requirements. This type of tutorial is what I look for when I do my work.  

    (2) Do you, yourself, compile this C# code or is it done automatically.

    (3) What changes do I have to make to be able to use this library in C# or Visual Basic. http://technet.microsoft.com/en-us/library/aa902635(v=SQL.80).aspx. Can it be used with C++.

    (4) What is the ClassId, InterfaceId, and EventsId.

    (5) How does you code change with the possibility of also having a conflict between two subscribers.

    (6) How do you handle the conflict of also dealing with a specific text field. Le'ts say, we have a conflict of a specific text field. Ideally, I'd like to concatenate both together and save the concatenation at both SQL Server instances.

    (7) What other ways can I test your C# code without using SQL Server Management Studio. Is it possible. Do you do it.

    Thanks,

     
  • Monday, December 03, 2012 6:30 PM
    Moderator
     
     Answered
    Sign In to Vote
    1

    1)  I would test and debug using the debugging symbol file (.pdb) or by writing to an output file.  The code does not need to directly refer to a table.  After the resolver has been deployed and registered at the distributor you will see the name of your resolver appear in the custom resolver list.

    2)  Yes, you compile the code.

    3)  You can use this library in C# or Visual Basic by following the steps outlined here:  http://www.replicationanswers.com/CustomResolver.asp.  It can also be done in C++ following the steps outlined here:  How to: Implement a COM-Based Custom Conflict Resolver for a Merge Article.

    4)  ClassId, InterfaceId, EventsId

    5)  It depends.  I would need a specific example to address that.

    6)  That can be done easily.  String concatenation is a feature available in almost every language.

    Brandon Williams (blog | linkedin)

     
  • Monday, December 03, 2012 6:49 PM
     
     
    Sign In to Vote
    0

    1)  I would test and debug using the debugging symbol file (.pdb) or by writing to an output file.  The code does not need to directly refer to a table.  After the resolver has been deployed and registered at the distributor you will see the name of your resolver appear in the custom resolver list.

    2)  Yes, you compile the code.

    3)  You can use this library in C# or Visual Basic by following the steps outlined here:  http://www.replicationanswers.com/CustomResolver.asp.  It can also be done in C++ following the steps outlined here:  How to: Implement a COM-Based Custom Conflict Resolver for a Merge Article.

    4)  ClassId, InterfaceId, EventsId

    5)  It depends.  I would need a specific example to address that.

    6)  That can be done easily.  String concatenation is a feature available in almost every language.

    (2) Where can I get a free download to compile the C# code.

    (5) Two subscribers that both increment the same field by 1 causes a conflict. The correct way to handle this would be to both servers to add an additional 1 to what they have. Your code has this but for Publisher and Subscriber.

    (6) Can you give me the specific details.

     
  • Monday, December 03, 2012 7:01 PM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    1

    2)  Visual Studio Express is free:  http://www.microsoft.com/visualstudio/eng/products/visual-studio-express-products

    5)  The code would be the same.

    6)

    // strings to concatenate
string string1 = "This is";
string string2 = "so easy.";

// concatenate strings
string str = string1 + string2;

    Brandon Williams (blog | linkedin)

     
  • Monday, December 03, 2012 7:13 PM
     
     
    Sign In to Vote
    0

    Because I develop my own code, the way I generally program is within my code 99.99% of the time I refer to variables that I directly create. In other programs, I find a variable in the code some where and I, usually, go to the top of the code and to look for the variable's definition. Is your code written this way, or are there things referred to that are not directly in the code you present.

    Thanks,

     
  • Monday, December 03, 2012 7:18 PM
    Moderator
     
     Answered
    Sign In to Vote
    1

    I have no idea what you just asked.

    I would say the best thing for you to do would be to start reading the documentation, prototype, set breakpoints, step through the code, and take a look at what you have to work with.

    Brandon Williams (blog | linkedin)

     
  • Monday, December 03, 2012 9:21 PM
     
     
    Sign In to Vote
    0

    I installed Visual Studio Express and added the C# code to a new (Windows Forms Application Visual C#) project. What should I do. I don't appear to have the Visual Studio .NET command prompt.

    When I hover the mouse over the below declaration, I get the below error!

    using SQLResolver_import;

    The type or namespace name 'SQLResolver_import' could not be found (are you mising a using directive or an assembly reference?)

    'SQLResolver_import' is created from replrec.dll: http://www.replicationanswers.com/CustomResolver.asp

    It (vcvarsall.bat) doesn't come with VS 2012 Express. http://social.msdn.microsoft.com/Forums/en-US/Vsexpressvb/thread/9f4b8961-63eb-4062-bf3c-12f0126f0e80/

    I do have VS 2008, and the VS command prompt (vcvarsall.bat) comes with it. Tomorrow, if I do create the file (SQLResolver_import.dll) where is the best place for it.

    Thank you,



     
  • Monday, December 03, 2012 10:31 PM
    Moderator
     
     Answered
    Sign In to Vote
    1
    Copy the .dll into your project folder.  Then in Visual Studio Solution Explorer right-click your project -> Add reference...  Browse to the .dll and click OK.

    Brandon Williams (blog | linkedin)