locked
Sync Server View to Client Table RRS feed

  • Question

  • I am trying to download data from a view into a table on the client with Sync.

    From what I have read, this is possible by simply treating the view as a table.  Unfortunately I cannot get past the provisioning Apply() command.  I am getting a There is already an object named 'SscOrders' in the database error.

    Not much to it, but here is the code.  Any help is greatly appreciated!

    string scopeName = "viewTest";
    
    SqlConnection serverConn = new SqlConnection("...");
    
                    //provision server
    var serverProvision = new SqlSyncScopeProvisioning(serverConn);
                    serverProvision.SetCreateTableDefault(DbSyncCreationOption.CreateOrUseExisting);
    
    if (!serverProvision.ScopeExists(scopeName))
    {
       var serverScopeDesc = new DbSyncScopeDescription(scopeName);
    
      // add the server table
        var serverTableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("dbo.SscOrders", serverConn);
    
    
    
        serverTableDesc.GlobalName = "ERPInfoSource";
        serverScopeDesc.Tables.Add(serverTableDesc);
    
          serverScopeDesc.Tables["ERPInfoSource"].Columns["OL_OH_GLBL_ID"].IsPrimaryKey = true;
       serverProvision.PopulateFromScopeDescription(serverScopeDesc);
    
    serverProvision.ObjectPrefix = "viewTest_Prefix";
    // start the provisioning process
    try
    {
    
      serverProvision.Apply();
    
      Debug.WriteLine("Server Provision Successful for: " + serverScopeDesc + "\n");
    }
    catch (Exception ex)
    {
           Debug.WriteLine(ex.Message);
                            //MessageBox.Show(ex.Message);
    }


    • Edited by eskog Wednesday, March 4, 2015 6:43 PM
    Wednesday, March 4, 2015 6:43 PM

All replies

  • Ok, I have tried using this:

    serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);

    I then get a different error:

    The object 'dbo.SscOrders' does not exist or is invalid for this operation.


    Wednesday, March 4, 2015 9:01 PM
  • afaik, using View will not work in your case. the sync provider you're using uses triggers to record changes in the tracking table. you're view will not fire the trigger, thus no entry will be recorded in the tracking table which in turn will result to no changes detected.
    Thursday, March 5, 2015 1:31 AM
  • Ok, I switched it up following the instructions in the MSDN How to Download a Snapshot of Data to a Client 

    Im stuck on how to set up the client.

    It is not a CE database, so I cant use the provider in the example.

    Since the server/source is a View, I dont think I can use the SqlSyncProvider, as there is no scope.

    Also, the client table has a different name from the server view, so I assume that has to be specified somewhere?

    Thursday, March 5, 2015 5:38 PM
  • Basically, I need to download data from the [SscOrdersUnq] view into a table called [ERP_Info]

    The Sync Framework page says: 
    The DbServerSyncProvider supports tables from a server database for all kinds of synchronization. It supports views for snapshot and download-only synchronization.

    Am I correct in assuming this is possible?
    Here is the current attempt:

    //provision server
    var serverProvider = new DbServerSyncProvider();
    
    serverProvider.Connection = serverConn;
    
    //view
    SyncAdapter viewSyncAdapter = new SyncAdapter("SscOrdersUnq");
    SqlCommand viewIncrInserts = new SqlCommand();
    viewIncrInserts.CommandText = "SELECT [UNIT_ID] " +
                      ",[BUSINESS_ADMIN] " +
                      ",[ORDER_NUMBER] " +
                      ",[ORDER_LINE_NUMBER] " +
                      ",[CUSTOMER_NAME] " +
                      ",[PURCHASE_ORDER_NUM] " +
                      ",[WIP_ORDER_NUMBER] " +
                      ",[UNIT_NUMBER] " +
                      ",[MODEL_NUMBER] " +
                      ",[PRODUCT_LINE] " +
                      ",[SERIAL_NUMBER] " +
                      ",[ASSEMBLE_TO_LINE_NUMBER] " +
                      ",[STATUS] " +
                      ",[LAST_UPDATE_DATE] " +
                      ",[CREATEDATE] " +
                      " FROM [dbo].[SscOrdersUnq]";
    viewIncrInserts.Connection = serverConn;
                    viewSyncAdapter.SelectIncrementalInsertsCommand = viewIncrInserts;
                    serverProvider.SyncAdapters.Add(viewSyncAdapter);
    
    SyncAgent sa = new SyncAgent();
    
    sa.RemoteProvider = serverProvider;
    
    sa.LocalProvider = ???

    Thursday, March 5, 2015 5:52 PM
  • unfortunately, the older ClientSyncProvider only works with SQL CE. again, as mention, views will only work with snapshots, not with incremental syncs since there is no change tracking occurring.
    Friday, March 6, 2015 1:54 AM