locked
DateTime to Int64 RRS feed

  • Question

  •  

    user error and mismatch datatype in the database... thanks.

     

     

    • Moved by Max Wang_1983 Friday, April 22, 2011 11:15 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Tuesday, February 27, 2007 7:05 AM

Answers


  • Few things I noticed:

    1- The update_originator_id column on the table should be of type int
    2- You don't need the sync_last_received_anchor and sync_new_received_anchor columns on the base table or tombstone table.

    This should be it. To test the app, make several changes on the server and make sure that the update_originator_id for the changes is set to 0.

    Tuesday, February 27, 2007 5:42 PM
  •  

    The reason you are not getting the old data is because the tracking columns are not populated (set to null). In order to ensure that pre-existing rows will have a valid tracking value, you need to write your ALTER table statement as follows:

    ALTER TABLE [table_name]
    ADD [create_date] DateTime DEFAULT (GETUTCDATE()) WITH Values

    You will need to do the same for all tracking columns including the update_originator_id (default is 0)

    Thanks

    Tuesday, February 27, 2007 7:41 PM

All replies


  • Bill,

    Are you using the same client database file as the demo? If so, then try to delete it and restart the application. It is always good to start afresh after making changes to data types.
     
    If this did not work, try to reduce the size of your demo to only one table and experiment with the data types. The anchor value generated by the NewAnchor command must match that of the tracking column in the table.

    Tuesday, February 27, 2007 4:27 PM
  •  

    Rafik, Hi,

    I'm using a newly created db... not the pub db from the demo. Turns out I had a bigInt column in one of the tombstone tables on the server... once I changed the data types to DateTime the error was gone... to many tables... lol :)

    Thanks for the tip.... I have been deleting the the client db in between tests and some compiles and did notice if the anchor value did not match it would error out occassionaly.... Thanks a ton!

    So now I totally have the tables being created on the client side... very cool stuff!!

    Only thing is that my server data is not coming across and making it to the client db in the sync process.... still not 100% sure how to get the data to the client from the server.

    Thanks,

    Bill

     

    Tuesday, February 27, 2007 4:52 PM
  •  

    Cool! To debug sync issues, the profiler is your friend. You can manually execute the queries generated by the adapter builder on the server and see what is going on.

    Thanks

    Tuesday, February 27, 2007 5:08 PM
  •  

    EXAMPLE SOURCE ---- one table:

    private void buttonSynchronize_Click(object sender, EventArgs e)

    {

    try

    {

    //

    // 1. Create instance of the sync components (client, agent, server)

    // This demo illustrates direct connection to server database. In this scenario,

    // sync components - client provider, sync agent and server provider - reside at

    // the client side. On the server, each table might need to be extended with sync

    // related columns to store metadata. This demo adds three more columns to the

    // orders and order_details tables for bidirectional sync. The changes are illustrated

    // in demo.sql file.

    //

    //

    DbServerSyncProvider serverSyncProvider = new DbServerSyncProvider();

    SyncAgent syncAgent = new SyncAgent();

    syncAgent.ServerSyncProvider = serverSyncProvider;

    SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

    //

    // 2. Prepare server db connection and attach it to the sync agent

    //

    builder["Data Source"] = textServerMachine.Text;

    builder["integrated Security"] = true;

    builder["Initial Catalog"] = "ffgscrm";

    SqlConnection serverConnection = new SqlConnection(builder.ConnectionString);

    serverSyncProvider.Connection = serverConnection;

    //

    // 3. Prepare client db connection and attach it to the sync provider

    //

    string connString = "Data Source=" + dbPathTextBox.Text;

    if (false == File.Exists(dbPathTextBox.Text))

    {

    SqlCeEngine clientEngine = new SqlCeEngine(connString);

    clientEngine.CreateDatabase();

    clientEngine.Dispose();

    }

    SqlCeClientSyncProvider clientSyncProvider = new SqlCeClientSyncProvider(connString);

    syncAgent.ClientSyncProvider = (ClientSyncProvider)clientSyncProvider;

    //

    // 4. Create SyncTables and SyncGroups

    // To sync a table, a SyncTable object needs to be created and setup with desired properties:

    // TableCreationOption tells the agent how to initialize the new table in the local database

    // SyncDirection is how changes from with respect to client {Download, Upload, Bidirectional or Snapshot}

    //

    //

    SyncTable tableProposalDetail = new SyncTable("ProposalDetail");

    tableProposalDetail.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;

    tableProposalDetail.SyncDirection = SyncDirection.Bidirectional;

    //

    // Sync changes for both tables as one bunch, using SyncGroup object

    // This is important if the tables has PK-FK relationship, grouping will ensure that

    // and FK change won't be applied before its PK is applied

    //

    //

    SyncGroup orderGroup = new SyncGroup("AllChanges");

    tableProposalDetail.SyncGroup = orderGroup;

    syncAgent.SyncTables.Add(tableProposalDetail);

     

    //

    // 5. Create sync adapter for each sync table and attach it to the agent

    // Following DataAdapter style in ADO.NET, SyncAdapte is the equivelent for

    // Sync. SyncAdapterBuilder is a helper class to simplify the process of

    // creating sync commands.

    //

    //

    SqlSyncAdapterBuilder ProposalDetailBuilder = new SqlSyncAdapterBuilder();

    ProposalDetailBuilder.Connection = serverConnection;

    ProposalDetailBuilder.SyncDirection = SyncDirection.Bidirectional;

    // base table

    ProposalDetailBuilder.TableName = "ProposalDetail";

    ProposalDetailBuilder.DataColumns.Add("tblID");

    ProposalDetailBuilder.DataColumns.Add("SQLkey");

    ProposalDetailBuilder.DataColumns.Add("CreateDateTime");

    ProposalDetailBuilder.DataColumns.Add("Alias");

    ProposalDetailBuilder.DataColumns.Add("PropDNumb");

    ProposalDetailBuilder.DataColumns.Add("PropDRevNumb");

    ProposalDetailBuilder.DataColumns.Add("PropDLine");

    ProposalDetailBuilder.DataColumns.Add("PropDItem");

    ProposalDetailBuilder.DataColumns.Add("PropDMfg");

    ProposalDetailBuilder.DataColumns.Add("PropDCat");

    ProposalDetailBuilder.DataColumns.Add("PropDList");

    ProposalDetailBuilder.DataColumns.Add("PropDCost");

    ProposalDetailBuilder.DataColumns.Add("PropDGMDollar");

    ProposalDetailBuilder.DataColumns.Add("PropDGM");

    ProposalDetailBuilder.DataColumns.Add("PropDOGM");

    ProposalDetailBuilder.DataColumns.Add("PropDSellPrice");

    ProposalDetailBuilder.DataColumns.Add("PropDXSellPrice");

    ProposalDetailBuilder.DataColumns.Add("PropDHideValue");

    ProposalDetailBuilder.DataColumns.Add("PropDShipQty");

    ProposalDetailBuilder.DataColumns.Add("PropDOptionalItem");

    ProposalDetailBuilder.DataColumns.Add("PropDUOM");

    ProposalDetailBuilder.DataColumns.Add("PropDStatus");

    ProposalDetailBuilder.DataColumns.Add("PropDGMPercent");

    ProposalDetailBuilder.DataColumns.Add("PropDNotes");

    ProposalDetailBuilder.DataColumns.Add("PropDItemTotal");

    ProposalDetailBuilder.DataColumns.Add("create_timestamp");

    ProposalDetailBuilder.DataColumns.Add("update_timestamp");

    ProposalDetailBuilder.DataColumns.Add("update_originator_id");

    ProposalDetailBuilder.DataColumns.Add("sync_last_received_anchor");

    ProposalDetailBuilder.DataColumns.Add("sync_new_received_anchor");

     

    // tombstone table

    ProposalDetailBuilder.TombstoneTableName = "ProposalDetail_tombstone";

    ProposalDetailBuilder.TombstoneDataColumns.Add("tblID");

    ProposalDetailBuilder.TombstoneDataColumns.Add("SQLkey");

    ProposalDetailBuilder.TombstoneDataColumns.Add("CreateDateTime");

    ProposalDetailBuilder.TombstoneDataColumns.Add("Alias");

    ProposalDetailBuilder.TombstoneDataColumns.Add("PropDNumb");

    ProposalDetailBuilder.TombstoneDataColumns.Add("PropDRevNumb");

    ProposalDetailBuilder.TombstoneDataColumns.Add("PropDLine");

    ProposalDetailBuilder.TombstoneDataColumns.Add("PropDItem");

    ProposalDetailBuilder.TombstoneDataColumns.Add("PropDMfg");

    ProposalDetailBuilder.TombstoneDataColumns.Add("PropDCat");

    ProposalDetailBuilder.TombstoneDataColumns.Add("PropDList");

    ProposalDetailBuilder.TombstoneDataColumns.Add("PropDCost");

    ProposalDetailBuilder.TombstoneDataColumns.Add("PropDGMDollar");

    ProposalDetailBuilder.TombstoneDataColumns.Add("PropDGM");

    ProposalDetailBuilder.TombstoneDataColumns.Add("PropDOGM");

    ProposalDetailBuilder.TombstoneDataColumns.Add("PropDSellPrice");

    ProposalDetailBuilder.TombstoneDataColumns.Add("PropDXSellPrice");

    ProposalDetailBuilder.TombstoneDataColumns.Add("PropDHideValue");

    ProposalDetailBuilder.TombstoneDataColumns.Add("PropDShipQty");

    ProposalDetailBuilder.TombstoneDataColumns.Add("PropDOptionalItem");

    ProposalDetailBuilder.TombstoneDataColumns.Add("PropDUOM");

    ProposalDetailBuilder.TombstoneDataColumns.Add("PropDStatus");

    ProposalDetailBuilder.TombstoneDataColumns.Add("PropDGMPercent");

    ProposalDetailBuilder.TombstoneDataColumns.Add("PropDNotes");

    ProposalDetailBuilder.TombstoneDataColumns.Add("PropDItemTotal");

    ProposalDetailBuilder.TombstoneDataColumns.Add("create_timestamp");

    ProposalDetailBuilder.TombstoneDataColumns.Add("update_timestamp");

    ProposalDetailBuilder.TombstoneDataColumns.Add("update_originator_id");

    ProposalDetailBuilder.TombstoneDataColumns.Add("sync_last_received_anchor");

    ProposalDetailBuilder.TombstoneDataColumns.Add("sync_new_received_anchor");

    // tracking\sync columns

    ProposalDetailBuilder.CreationTrackingColumn = @"create_timestamp";

    ProposalDetailBuilder.UpdateTrackingColumn = @"update_timestamp";

    ProposalDetailBuilder.DeletionTrackingColumn = @"update_timestamp";

    ProposalDetailBuilder.UpdateOriginatorIdColumn = @"update_originator_id";

    SyncAdapter ProposalDetailSyncAdapter = ProposalDetailBuilder.ToSyncAdapter();

    serverSyncProvider.SyncAdapters.Add(ProposalDetailSyncAdapter);

     

    //

    // 6. Setup provider wide commands

    // There are two commands on the provider itself and not on a table sync adapter:

    // SelectNewAnchorCommand: Returns the new high watermark for current sync, this value is

    // stored at the client and used the low watermark in the next sync

    // SelectClientIdCommand: Finds out the client ID on the server, this command helps

    // avoid downloading changes that the client had made before and applied to the server

    //

    //

    // select new anchor command

    SqlCommand anchorCmd = new SqlCommand();

    anchorCmd.CommandType = CommandType.Text;

    anchorCmd.CommandText = "SELECT GetDate()";

    serverSyncProvider.SelectNewAnchorCommand = anchorCmd;

    // client ID command (give the client id of 1)

    // in remote server scenario (middle tear), this command will reference a local client table for the ID

    SqlCommand clientIdCmd = new SqlCommand();

    clientIdCmd.CommandType = CommandType.Text;

    clientIdCmd.CommandText = "SELECT 1";

    serverSyncProvider.SelectClientIdCommand = clientIdCmd;

    //

    // 7. Kickoff sync process

    //

    // Setup the progress form and sync progress event handler

    _progressForm = new ProgressForm();

    _progressForm.Show();

    clientSyncProvider.SyncProgress += new EventHandler<SyncProgressEventArgs>(ShowProgress);

    clientSyncProvider.ApplyChangeFailed += new EventHandler<ApplyChangeFailedEventArgs>(ShowFailures);

    serverSyncProvider.SyncProgress += new EventHandler<SyncProgressEventArgs>(ShowProgress);

    SyncStatistics syncStats = syncAgent.Synchronize();

    // Update the UI

    _progressForm.EnableClose();

    _progressForm = null;

    buttonRefreshOrders_Click(null, null);

    //buttonRefreshOrderDetails_Click(null, null);

    }

    catch (Exception exp)

    {

    MessageBox.Show(exp.Message);

    if (_progressForm != null)

    {

    _progressForm.EnableClose();

    _progressForm = null;

    }

    }

    }

     

    SQL SCRIPT ---- Server tables used above in source:

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProposalDetail]') AND type in (N'U'))

    BEGIN

    CREATE TABLE [dbo].[ProposalDetail](

    [tblID] [int] IDENTITY(1,1) NOT NULL,

    [SQLkey] [int] NOT NULL,

    [CreateDateTime] [datetime] NULL,

    [Alias] [nvarchar](50) NOT NULL,

    [PropDNumb] [nvarchar](10) NOT NULL,

    [PropDRevNumb] [nvarchar](30) NULL,

    [PropDLine] [numeric](4, 0) NULL,

    [PropDItem] [nvarchar](50) NULL,

    [PropDMfg] [nvarchar](3) NULL,

    [PropDCat] [nvarchar](75) NULL,

    [PropDList] [nvarchar](10) NULL,

    [PropDCost] [numeric](10, 2) NULL,

    [PropDGMDollar] [numeric](10, 2) NULL,

    [PropDGM] [numeric](10, 2) NULL,

    [PropDOGM] [numeric](10, 2) NULL,

    [PropDSellPrice] [numeric](10, 2) NULL,

    [PropDXSellPrice] [numeric](10, 2) NULL,

    [PropDHideValue] [nvarchar](1) NULL,

    [PropDShipQty] [numeric](9, 0) NULL,

    [PropDOptionalItem] [nvarchar](1) NULL,

    [PropDUOM] [nvarchar](6) NULL,

    [PropDStatus] [nvarchar](18) NULL,

    [PropDGMPercent] [numeric](10, 2) NULL,

    [PropDNotes] [nvarchar](max) NULL,

    [PropDItemTotal] [numeric](10, 2) NULL,

    [create_timestamp] [datetime] NULL,

    [update_timestamp] [datetime] NULL,

    [update_originator_id] [nvarchar](max) NULL,

    [sync_last_received_anchor] [datetime] NULL,

    [sync_new_received_anchor] [datetime] NULL,

    CONSTRAINT [PK_ProposalDetail] PRIMARY KEY CLUSTERED

    (

    [tblID] ASC

    )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

    ) ON [PRIMARY]

    END

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProposalDetail_tombstone]') AND type in (N'U'))

    BEGIN

    CREATE TABLE [dbo].[ProposalDetail_tombstone](

    [tblID] [int] IDENTITY(1,1) NOT NULL,

    [SQLkey] [int] NOT NULL,

    [CreateDateTime] [datetime] NULL,

    [Alias] [nvarchar](50) NOT NULL,

    [PropDNumb] [nvarchar](10) NOT NULL,

    [PropDRevNumb] [nvarchar](30) NULL,

    [PropDLine] [numeric](4, 0) NULL,

    [PropDItem] [nvarchar](50) NULL,

    [PropDMfg] [nvarchar](3) NULL,

    [PropDCat] [nvarchar](75) NULL,

    [PropDList] [nvarchar](10) NULL,

    [PropDCost] [numeric](10, 2) NULL,

    [PropDGMDollar] [numeric](10, 2) NULL,

    [PropDGM] [numeric](10, 2) NULL,

    [PropDOGM] [numeric](10, 2) NULL,

    [PropDSellPrice] [numeric](10, 2) NULL,

    [PropDXSellPrice] [numeric](10, 2) NULL,

    [PropDHideValue] [nvarchar](1) NULL,

    [PropDShipQty] [numeric](9, 0) NULL,

    [PropDOptionalItem] [nvarchar](1) NULL,

    [PropDUOM] [nvarchar](6) NULL,

    [PropDStatus] [nvarchar](18) NULL,

    [PropDGMPercent] [numeric](10, 2) NULL,

    [PropDNotes] [nvarchar](max) NULL,

    [PropDItemTotal] [numeric](10, 2) NULL,

    [create_timestamp] [datetime] NULL,

    [update_timestamp] [datetime] NULL,

    [update_originator_id] [nvarchar](max) NULL,

    [sync_last_received_anchor] [datetime] NULL,

    [sync_new_received_anchor] [datetime] NULL,

    CONSTRAINT [PK_ProposalDetail_tombstone] PRIMARY KEY CLUSTERED

    (

    [tblID] ASC

    )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

    ) ON [PRIMARY]

    END

     

    Tuesday, February 27, 2007 5:11 PM
  •  

    This stuff rocks man!!!!! Should the above sync the client db with data? If not... what's our next step?

    Thanks,

    bill

     

    Tuesday, February 27, 2007 5:15 PM

  • Few things I noticed:

    1- The update_originator_id column on the table should be of type int
    2- You don't need the sync_last_received_anchor and sync_new_received_anchor columns on the base table or tombstone table.

    This should be it. To test the app, make several changes on the server and make sure that the update_originator_id for the changes is set to 0.

    Tuesday, February 27, 2007 5:42 PM
  •  

    Rafik,

    WOW! I'm a sync'en fool! lol.. over the top! Thank you for showing me how... :)

    I removed the sync_last_received_anchor  and the sync_new_received_anchor columns on the server tables... and changed the update_originator_id to int with a default value of 0 and it works great!

    This works great for new additions but how do you get all existing data across to the client from the Server in the sync process for the initial client load?

    1. Is there a column value I can set? 
    2. Would changing the SyncDirection.Bidirectional to SyncDirection.Snapshot work?

    This is awesome!

    Thanks,

    Bill

     

    Tuesday, February 27, 2007 6:32 PM
  •  

    The reason you are not getting the old data is because the tracking columns are not populated (set to null). In order to ensure that pre-existing rows will have a valid tracking value, you need to write your ALTER table statement as follows:

    ALTER TABLE [table_name]
    ADD [create_date] DateTime DEFAULT (GETUTCDATE()) WITH Values

    You will need to do the same for all tracking columns including the update_originator_id (default is 0)

    Thanks

    Tuesday, February 27, 2007 7:41 PM
  •  

    Have the tracking columns all ready in place on all tables and will update accordingly... so awesome... i was totally hoping that was the answer and not any additional code changes! You guys did a great job on this one man... keep up the great work! :) Oh yeah... thank you again very much for the walk through... I really appreciate it.

    Bill

     

    Tuesday, February 27, 2007 7:53 PM
  •  

    I just used a simple...  UPDATE ProposalDetail set create_timestamp = '2/27/2007 10:25:00 AM' ... to update the NULL table row values in the database and set the defaults as you mentioned above and the sync adapters are singing and dancing!!! :)  Great Stuff!

    Thanks again Rafik!

     

    Tuesday, February 27, 2007 8:49 PM