locked
Sync Services and SQL Server 2008 Change Tracking, help! RRS feed

  • Question

  • I'm trying to implement a project (prototype) using Sync Services for ADO.Net and SQL Server 2008. Change Tracking in SQL Server 2008 is a compelling feature that fixes some huge issues I'd have with trying to alter the schemas of existing tables to accomodate sync'ing otherwise.

     

    I cannot find any end to end code samples that implement this feature. The closest I can find is here:

     

    http://blogs.msdn.com/agujjar/archive/2008/01/09/sync-services-with-sql-2008.aspx

     

    and it is a partial example. This is a powerful product combination that I want/need to use on an upcoming project but right now I can't prototype for management because I can't get a hello world Change Tracking-based sync working. Can anybody point me to some documentation with examples that will compile and execute at runtime without error? I'm surprised that the sync team isn't all over this approach in their demos because it looks like it cuts down on code and certainly cuts out implementation complexity and hassle. Anybody out there?

     

    Thanks

    Shane

    • Moved by Max Wang_1983 Friday, April 22, 2011 8:18 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Friday, March 21, 2008 12:03 AM

Answers

All replies

  • Hi Shane,

     

    Sorry, no answer, but I'm very interested in the answers to this question. Like you I'm a little surprised in the quietness about this. I'm probably missing some point here. I would think that the Sync Services wizard would detect a SQL 2008 DB with change tracking and automaticly change the code in the CommandText to use ChangeTable().

    Maybe the team is waiting until this feature of SQL2008 is stable enough to implement? Maybe this team has an earlier release date in mind than SQL2008 and therefore can't put this in definitifly? Maybe they can tell here?

     

    On the other hand I'm wondering why you can't work with the sample you mentioned. You could change the CommandTexts and have it use Change Tracking, don't you?

     

    Greetings,

     

    Mark

    Friday, March 21, 2008 9:34 PM
  • Mark thanks for your reply and suggestion to use the sample and modify it with the Change Tracking code in the referenced article. I believe that is indeed the message from the author in the article, unfortunately my total lack of comprehension makes that task difficult. I have made several attempts to modify the code to accomodate change tracking to no avail. I frequently get error messages from sync services claiming "can't get schema" and telling me that there's a problem somewhere in my commands. That's as clear as the error messages get; ultimately useless.

     

    My limited knowledge is the key here. I find the instructive in the article to somehow mash the given partial code listing into the foreign sample project the height of irony. There are so many usage combinations of the various sync classes throughout the limited documentation already that adding one more random variable isn't helping anybody. I wasted two days trying to make sense of it. I hate those kinds of instructives "it's so easy! So easy in fact that I won't even bother posting code that will compile! That's how easy it is."

     

     Mark Giesen wrote:

    Hi Shane,

     

    Sorry, no answer, but I'm very interested in the answers to this question. Like you I'm a little surprised in the quietness about this. I'm probably missing some point here. I would think that the Sync Services wizard would detect a SQL 2008 DB with change tracking and automaticly change the code in the CommandText to use ChangeTable().

    Maybe the team is waiting until this feature of SQL2008 is stable enough to implement? Maybe this team has an earlier release date in mind than SQL2008 and therefore can't put this in definitifly? Maybe they can tell here?

     

    On the other hand I'm wondering why you can't work with the sample you mentioned. You could change the CommandTexts and have it use Change Tracking, don't you?

     

    Greetings,

     

    Mark

    Sunday, March 23, 2008 5:53 PM
  • Hi Shane,

     

    I'll be trying to change some sample code from the Sync Services Wizard code, to the Change Tracking variant this week. If I succeed, I'll be more than happy to send it to you. Keep faith!

    I still think the Sync Services Wizard will be producing this Change Tracking variant in the near future, but I wish someone from MS could tell mee how near.

     

    Greetings'.

    Monday, March 24, 2008 1:05 PM
  • Have a look at Windows Mobile Line of Business Solution Accelerator 2008. This demonstrates a 3 tier Sync solution against a SQL Server 2008 database using Change Tracking

     

    Tuesday, March 25, 2008 7:54 AM
  • Hi Erik,

     

    I did just that last night. I'm not to worried about getting this to work with SQL 2008, I'm just very surprised the ADO.NET Sync Services wizard doesn't take this SQL 2008 feature into account. Any insight on that?

     

    Tuesday, March 25, 2008 8:00 AM
  • You should not be surprised, as SQL Server 2008 was not released when the Wizard was ready (in November 2007). SQL Server 2008 is not expected until Q3 2008. You can probably expect a Visual Studio Service Pack, that will change this state of affairs.

     

    Tuesday, March 25, 2008 8:23 AM
  • "very surprised" was a little excessive :-). I already thought it might be something like that. I'm surprised though, about the quietness about the fact that Synchronization has become so much easier with SQL 2008. I'd suspect more demo's online. Maybe I'm too early in adopting this and it's all still in the making.

     

    Tuesday, March 25, 2008 8:45 AM
  • I got word that "yes" there will be sync wizard integration for SQL Server 2008 Change Tracking in Visual Studio 2008 SP1. When that arrives however remains anyone's guess. In the meantime, I'm still waiting for any kind of functional sample code. I have contacted three senior MS people on that one and have not received any real help yet.

     

    Tuesday, March 25, 2008 11:54 AM
  • The Windows Mobile Line of Business Solution Accelerator 2008 Erik mentions is working code. I'm using that and SQL 2008 BOL to get it working.

     

    Tuesday, March 25, 2008 1:25 PM
  • I exchanged emails with Steve Lasker who pointed me to Liam Cavanagh who pointed me to this SQL Server 2008 documentation and sample for using Sync Services for ADO.Net with SQL Change Tracking.

     

    http://msdn2.microsoft.com/en-us/library/cc305322(SQL.100).aspx

     

    HUGE move in the right direction. One minor problem: I cannot get this thing to compile. There are numerous DLL reference/versioning issues. CAN anybody get this thing to compile and if so PLEASE point me to the exact Sync Service and SQL Server CE dlls and versions you used to do so.

     

    Thanks and good luck. I need some right now.

    Wednesday, March 26, 2008 8:21 PM
  • Hi Shane,

     

    We're on the same track. I read this, this weekend, but hadn't tried it yet. I just did and it compiled. There are some steps you need to take, that are not completely documented (or I haven't found it yet).

    • Start a new C# Console application
    • Open Program.cs, delete all and replace it with the complete C# code from you link.
    • Right click the References folder in you project and add: Microsoft.Synchronization.dll, Microsoft.Synchronization.Data.dll, Microsoft.Synchronization.Data.Server.dll, and Microsoft.Synchronization.Data.SqlServerCe.dll. Use versions 0.94.0.0, 1.0.0.0, 1.0.0.0 and 3.5.0.0.
    • Add one more reference: System.Data.SqlServerCe version 3.5.0.0
    • You need to add the Utility class to your Program.cs. You can find it at http://msdn2.microsoft.com/en-us/library/bb726022(SQL.100).aspx. There is one } to many, so delete the last one after pasting.
    • Now you can build the solution succesfully (I could).
    • Now you're ready to create the test DB. Go to http://msdn2.microsoft.com/en-us/library/bb726041(SQL.100).aspx and copy the middle script (SQL Server Change Tracking for Offline Scenarios) and place it in a new text file in your project with the sql extension.
    • Create a connection to your SQL2008 server without selecting a DB, VS will select Master, and thats fine.
    • Run the script.
    • Change "localhost" in the beginnen of the method main to you server\instance
    • Now run you application.

    In the bin folder the application wil create a SQLCE DB at startup, followed by some sync actions. No error here (anymore). I might have misted a step, in that case sorry, I tried to be precise.

     

    Good luck and keep me posted of any findings.

     

    Mark

    Wednesday, March 26, 2008 9:19 PM
  • Mark,

     

    Thank you for your detailed response (particularly the DLL version listing). I have unfortunate news. I had already performed EXACTLY the same steps you listed. I think you might be watching me. I can now compile the project (I played around with switching out multiple versions of the sync DLLs and finally came up with a version that ALMOST worked). Then I had to switch out a property ref in the sample code - the sample code makes use of SyncSession.SyncClientIdBinary while my SyncSession class doesn't contain SyncSession.SyncClientIdBinary!!!!!! GREAT! I prayed that replacing that property ref with a simple SyncSession.SyncClientId would work, but ALAS it does not.

     

    Does your code execute properly? Do you get the expected output? My referenced sync DLL versions are identical to the ones you've posted, but I could easily believe that there is something wrong on that front. I need binaries that work!!!

     

    Thanks again for your diligence Mark.

    Shane

     

    Thursday, March 27, 2008 2:38 AM
  • Hi Shane,

     

    Your error looks familiar :-). It means you have the 2.0.0.0 version of Microsoft.Synchronization.Data added as reference, not 1.0.0.0. Change that (and recheck the rest), and run the app. It does work without any other changes. I haven't checked the outcome of the app, it runs and writelines clear english. That was enough for me last night, it was bedtime. I'll be playing with it again tonight. Maybe you have some news for me by then.

     

    Mark

    Thursday, March 27, 2008 6:03 AM
  • Mark - I know you're right. It is definitely a dll versioning issue. Here's my problem; I AM referencing the 1.0.0.0 version!!!!! Uggh. Here's the path to the Microsoft.Synchronization.Data dll in my project:

     

    C:\Program Files\Microsoft Synchronization Services\ADO.NET\v1.0\Microsoft.Synchronization.Data.dll

     

    Is that path exactly similar to yours? When I right click and get the properties of the DLL in explorer, here's the more detailed version info:

     

    size: 111,984 bytes

    date modified: 8/14/2007

    file version 1.0.0.0

     

    How does that match with yours??? It is redundant to say thank you at this stage, but thank you.

     

    Shane

     

     Mark Giesen wrote:

    Hi Shane,

     

    Your error looks familiar :-). It means you have the 2.0.0.0 version of Microsoft.Synchronization.Data added as reference, not 1.0.0.0. Change that (and recheck the rest), and run the app. It does work without any other changes. I haven't checked the outcome of the app, it runs and writelines clear english. That was enough for me last night, it was bedtime. I'll be playing with it again tonight. Maybe you have some news for me by then.

     

    Mark

    Thursday, March 27, 2008 1:57 PM
  • Hi Shane,

     

    You have older versions then I have, so that is your problem. Did you install the CTP2 of the Sync Framework? I think your versions of the dll's are installed with VS2008. I'm not sure if the 1.0 files are replaced with the CTP2, but I wouldn't know how I got them otherwise. Look at the product version instead of the file version. My version is 1.0.1113.0 and it was signed at januari 27 2008.

     

    Greetings,

     

    Mark

    Thursday, March 27, 2008 7:10 PM
  • Mark - anybody - I do not have version 1.0.1113.0. I have installed the CTP2 of the sync framework. I have three copies of Microsoft.Synchroization.Data.dll and none of them read that version num. I'm borderline here. I NEED to get this functional. Is anybody from MS listening? Can somebody get me the right binaries of the sync framework to work with the SQL Server 2008 example referenced in this thread? Anybody???

     

    Friday, March 28, 2008 12:40 AM
  • Hi Shane,

     

    Indeed, all the extra's installed have the 1.0.0.0 version. But good news, I did find out where it came from: SQL Server 2008 february CTP. This setup includes the SQL Server Compact 3.5 SP1 Beta installation. So the quick solution is: download this SP1 and install it (it is separately available at http://www.microsoft.com/downloads/details.aspx?FamilyId=68539FAE-CF03-4C3B-AEDA-769CC205FE5F&displaylang=en). The better solution is deinstall your SQL2008 version (probably older CTP?) and install the February CTP.

    If you do have the February CTP installed, it might be that unchecking the MS Sync Framework at setup did not upgrade your version of CE, but this is guessing.

     

    Hope this gets you back on track again,

     

    Mark

    Friday, March 28, 2008 1:18 PM
  • Booya. That was it Mark. My infinite thanks. In some ways I'm done with this thread. In others I'm just getting started. If you have a progress report on how your experimenting goes, I'd love to hear about it. I will try to offer the same from my side. I hope that my previous post was the last help request I'll ever need to write. Somehow I doubt it.

     

    Cheers to you.

    Shane

    Friday, March 28, 2008 1:53 PM
  • If this was your last cry, you're either a genius, or in for a boring live! So, for your sake, I hope to here you cry some more.

     

    Friday, March 28, 2008 2:52 PM