locked
sqlexpress sync service sample Problem RRS feed

  • Question

  • I have been successful at getting the example to work with the sample databases.  I am now trying to get it to work with my database.  I commented out the orders_details code and am just using the code for the orders table.  I changed all references to the orders table to my table.  When I press the synchronize button I get an error message stating that System.ArgumentException: Table anme 'mytablename' is not valid.  This might be caused by one or more of the following issues:  unable to find a SyncAdapter for the specified SyncTable; the table name is null or empty; or the table name contains the keywords SET, FMTONLY, and OFF.

     

    The table name does not contain the key words so that is not the issue and the table name is not null.  As I step though the code it appears that the SyncAdapter gets set to the table name.  Please help.

     

    Thanks,

    Trey

    • Moved by Hengzhe Li Friday, April 22, 2011 5:27 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Tuesday, July 29, 2008 2:03 PM

Answers

  •  

    Actually, I just redid the entire project against my tables and was able to make it synchronize from client-server. However,  server-client is not working. Any thoughts on this would be appreciated.

     

    I believe the error that I received that I talked about on my post was with not seeing the dataset initially and not including my table instead of the orders table. Everything else that I redid was the same as the first time, so more than likely the absence of my table in the dataset was the cause of the error.

    Monday, August 4, 2008 4:55 PM

All replies

  •  

    I received this same error as well with this sample project but I do not recall how I resolved it.  I do recall that I had not added the extra columns to each table in the database and I had not added the update and delete triggers for each table so you might want to check that if you haven't already.

     

     

    Tuesday, July 29, 2008 4:10 PM
  • I've done all I know to do on this.  Is there anybody that can help me?

     

    Wednesday, July 30, 2008 7:10 PM
  • I haven't been able to get my app to work either with my database.  I am getting the error:

     

    Unable to set session parameters in DbServerSyncProvider. Cannot obtain the value for command parameter

     

    No one seems to know anything about this.

    Wednesday, July 30, 2008 8:38 PM
  • when you change it to your database, have you change the server database to yours ? and also are the proper stored procedures, queries changed approprately so all commands can be switched to your db and tables ?

     

    also please note the current sample does NOT support intial schema creation, both server and client ( on sqlexpress ) db and tables should be created and set for tracking before the app is run.

     

    if the above two check still cannot find the cause, would you please use sql profiler to track what query was sent to sql server by the provider ?

     

    thanks

    Yunwen

    Thursday, July 31, 2008 12:44 AM
    Moderator
  • I am still getting the same error.

     

    I have made all the necessary changes to the table names and column names.  I am using the same database as the sample with my table in there.  I have two columns in my table.  I replaced all occurences of the orders table with my table and changed all occurences of the orders table columns to my columns, both in the app and in the database including stored procedures.  It appears that it does not recognize my table as a SyncAdapter.  I assume that there maybe column mappings or schema that does not match what it is looking for. When I debug it gets the table name and sets it to the SyncAdapter, but it shows that the adapter count = 0, which means the adapter did not get set.  In the sample the count = 1 for the orders table.

     

     

    How do I set for tracking as you mentioned?  Also, I tried the profiler but no queries run since the table name doesn't get recognized.

     

    Thursday, July 31, 2008 3:45 PM
  • I had that problem and I think it was because the table was not being included in the metadata so that's why the table was not recognized by the time the code got to the "GetChanges" or "ApplyChanges" methods.

     

    I checked the following to assure that they were working properly and made changes so you might check the following:

     

    1. Create SyncTable for each table:

                SyncTable tableOrders = new SyncTable("orders");
                tableOrders.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
                tableOrders.SyncDirection = SyncDirection.Bidirectional;

     

    2. Create SyncGroup:

     

    SyncGroup orderGroup = new SyncGroup("AllChanges");

    3. Add each SyncTable to the SyncGroup

     

    tableOrders.SyncGroup = orderGroup;

     

    4. add each table to the SyncAgent

     

    syncAgent.Configuration.SyncTables.Add(tableOrders);

     

    5. make sure each table name is in anchor table

     

    6. make sure all tables have the fields with proper triggers: 

    create_timestamp bigint NULL DEFAULT (@@dbts+(1)),

    update_timestamp timestamp NOT NULL,

    update_originator_id int NULL DEFAULT ((0))

     

    Thursday, July 31, 2008 7:12 PM
  •  

    this is good stuff. thanks for sharing this out.

     

    Thanks

    Yunwen

    Thursday, July 31, 2008 11:42 PM
    Moderator
  • I've done all this and I still get the error message.  Is there anything else that you can think of that I might have overlooked?  Thanks.

     

    Friday, August 1, 2008 2:03 PM
  • Could you please run SQL Profiler and post the queries being executed against the server and client. In addition, posting some code samples from your application would help with troubleshooting as well.

     

    Thanks,

     

    Sean Kelley

    Program Manager

    Microsoft

     

    Friday, August 1, 2008 4:48 PM
    Moderator
  • Here are the queries from the profiler:

     

    Select * from tblPartLocDesc

    Select * from tblPartLocDesc

    exec sp_reset_connection

    SELECT Guid FROM guid

    SELECT Guid FROM guid

    SELECT ReceivedAnchor FROM anchor WHERE TableName = 'tblPartLocDesc'

    SELECT ReceivedAnchor FROM anchor WHERE TableName = 'tblPartLocDesc'

    SELECT SentAnchor FROM anchor WHERE TableName = 'tblPartLocDesc'

    SELECT SentAnchor FROM anchor WHERE TableName = 'tblPartLocDesc'

    SELECT ReceivedAnchor FROM anchor WHERE TableName = 'tblPartLocDesc'

    SELECT ReceivedAnchor FROM anchor WHERE TableName = 'tblPartLocDesc'

    exec sp_executesql N'Select @sync_new_received_anchor = @@DBTS',N'@sync_new_received_anchor timestamp output',@sync_new_received_anchor=@p3 output

     

    Below is some of the code that I changed from the sample.  I also changed the stored procedures, but have not included those.  The insert query related to the button click on the sample works fine with my table.  It's just the sync part that I am having trouble with.

    SyncTable MytableOrders = new SyncTable("tblPartLocDesc");

     

    SyncGroup orderGroup = new SyncGroup("AllChanges");

    MytableOrders.SyncGroup = orderGroup;

    syncAgent.Configuration.SyncTables.Add(MytableOrders);

     

    SyncAdapter adapterOrders = new SyncAdapter("tblPartLocDesc");

     

    // insert row com

    SqlCommand insOrdersCmd = new SqlCommand();

    insOrdersCmd.CommandType = CommandType.StoredProcedure;

    insOrdersCmd.CommandText = "sp_orders_applyinsert";

    insOrdersCmd.Parameters.Add("@LocDesc", SqlDbType.NVarChar);

    insOrdersCmd.Parameters.Add("@MarketID", SqlDbType.Int);

    insOrdersCmd.Parameters.Add("@" + SyncSession.SyncClientIdHash, SqlDbType.Int);

    insOrdersCmd.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Binary, 8);

    insOrdersCmd.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;

     

    adapterOrders.InsertCommand = insOrdersCmd;

    // update row command

    SqlCommand updOrdersCmd = new SqlCommand();

    updOrdersCmd.CommandType = CommandType.StoredProcedure;

    updOrdersCmd.CommandText = "sp_orders_applyupdate";

    updOrdersCmd.Parameters.Add("@LocDesc", SqlDbType.NVarChar);

    updOrdersCmd.Parameters.Add("@MarketID", SqlDbType.Int);

    updOrdersCmd.Parameters.Add("@" + SyncSession.SyncClientIdHash, SqlDbType.Int);

    updOrdersCmd.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Binary, 8);

    updOrdersCmd.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;

    //!! Removed for debugging. Uncomment when fixed.

    // updOrdersCmd.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit);

    adapterOrders.UpdateCommand = updOrdersCmd;

    // delete row command

    SqlCommand delOrdersCmd = new SqlCommand();

    delOrdersCmd.CommandType = CommandType.StoredProcedure;

    delOrdersCmd.CommandText = "sp_orders_applydelete";

    delOrdersCmd.Parameters.Add("@LocDesc", SqlDbType.NVarChar);

    delOrdersCmd.Parameters.Add("@" + SyncSession.SyncClientIdHash, SqlDbType.Int);

    delOrdersCmd.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Binary, 8);

    delOrdersCmd.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;

    adapterOrders.DeleteCommand = delOrdersCmd;

     

    SyncAdapter adapterOrders = new SyncAdapter("tblpartlocdesc");

    //SyncAdapter adapterOrders = new SyncAdapter("orders");

    // delete row com

    SqlCommand delOrdersCmd = new SqlCommand();

    delOrdersCmd.CommandType = CommandType.StoredProcedure;

    delOrdersCmd.CommandText = "sp_orders_applydelete";

    delOrdersCmd.Parameters.Add("@LocDesc", SqlDbType.NVarChar);

    delOrdersCmd.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Binary, 8);

    delOrdersCmd.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;

    adapterOrders.DeleteCommand = delOrdersCmd;

    // insert row com

    SqlCommand insOrdersCmd = new SqlCommand();

    insOrdersCmd.CommandType = CommandType.StoredProcedure;

    insOrdersCmd.CommandText = "sp_orders_applyinsert";

    insOrdersCmd.Parameters.Add("@LocDesc", SqlDbType.NVarChar);

    insOrdersCmd.Parameters.Add("@MarketID", SqlDbType.Int);

    insOrdersCmd.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Binary, 8);

    //!! Removed for debugging. Uncomment when fixed.

    // insOrdersCmd.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit);

    insOrdersCmd.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;

    adapterOrders.InsertCommand = insOrdersCmd;

    Friday, August 1, 2008 5:12 PM
  •  

    I noticed in the code above the adapterOrders was declared twice. I hope this is just a cut/paste error. but please make sure the tableName string you used for the sync Adapter and for the syncTable are the same. In side the provider, there is a case sensitive comparion for those tableName strings to determine which adapter to use.

     

    SyncAdapter adapterOrders = new SyncAdapter("tblPartLocDesc");

    SyncAdapter adapterOrders = new SyncAdapter("tblpartlocdesc");

     

    if the problem still persist, is that possible you share out all the code here by putting the file somewhere that we can access ?

     

    thanks

    Yunwen

    Sunday, August 3, 2008 4:07 AM
    Moderator
  • Actually inside the sample code the SyncAdapter is defined twice which is why I included it twice.

     

    Monday, August 4, 2008 11:41 AM
  •  

    Actually, I just redid the entire project against my tables and was able to make it synchronize from client-server. However,  server-client is not working. Any thoughts on this would be appreciated.

     

    I believe the error that I received that I talked about on my post was with not seeing the dataset initially and not including my table instead of the orders table. Everything else that I redid was the same as the first time, so more than likely the absence of my table in the dataset was the cause of the error.

    Monday, August 4, 2008 4:55 PM
  • Glad to see this was resolved.

     

    However, I don't have a clue why you can sync from client to server but not server to client. this seems a bit odd to me. if you can provide more info we can nail down this further.

     

    thanks

    Yunwen

     

    Monday, August 4, 2008 6:18 PM
    Moderator
  •  

    we just identified a bug in the sample. could you modify the code as Mahesh put in this post http://forums.microsoft.com/sync/ShowPost.aspx?PostID=3530045&SiteID=75 to see if this addressed your this issue ?

     

    thanks

    Yunwen

    Monday, August 4, 2008 10:18 PM
    Moderator
  •  polymorphicx wrote:

    I had that problem and I think it was because the table was not being included in the metadata so that's why the table was not recognized by the time the code got to the "GetChanges" or "ApplyChanges" methods.

     

    I checked the following to assure that they were working properly and made changes so you might check the following:

     

    1. Create SyncTable for each table:

                SyncTable tableOrders = new SyncTable("orders");
                tableOrders.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
                tableOrders.SyncDirection = SyncDirection.Bidirectional;

     

    2. Create SyncGroup:

     

    SyncGroup orderGroup = new SyncGroup("AllChanges");

    3. Add each SyncTable to the SyncGroup

     

    tableOrders.SyncGroup = orderGroup;

     

    4. add each table to the SyncAgent

     

    syncAgent.Configuration.SyncTables.Add(tableOrders);

     

    5. make sure each table name is in anchor table

     

    6. make sure all tables have the fields with proper triggers: 

    create_timestamp bigint NULL DEFAULT (@@dbts+(1)),

    update_timestamp timestamp NOT NULL,

    update_originator_id int NULL DEFAULT ((0))

     

     

    As a further note to this, I received the following error AGAIN on a second sync project:

    Table name 'Building' is not valid. This might be caused by one or more of the following issues:  unable to find a SyncAdapter for the specified SyncTable; the table name is null or empty; or the table name contains the keywords SET, FMTONLY, and OFF.

     

    This time, however, I made note of the fix  -- the resolution was to go into the method "AddClientSyncAdapters" and add adapters FOR EACH table I was working with.  For example:

     

    Code Snippet

    private void AddClientSyncAdapters(MyClientSyncProvider clientSyncProvider)

    {

    SyncAdapter adapterBuilding = new SyncAdapter("Building");

    ....

    clientSyncProvider.SyncAdapters.Add(adapterBuilding);

    }

     

     

    I have many tables but the "Building" table was not added here and this caused the error. 

    Wednesday, August 6, 2008 8:05 PM
  • Thanks for the input.  I don't know if you read my other post but I can't get the sync to work from server-client, only client to server. Do you have any suggestions on this?

     

     

    Thanks

     

    Wednesday, August 6, 2008 8:16 PM
  • Did you try the suggestions made in this thread above?:  http://forums.microsoft.com/sync/ShowPost.aspx?PostID=3530045&SiteID=75

     

    Otherwise, make sure that you have SyncAdapters for both client and server.  The stored procedures are also different between client and server.

     

    Good luck.
    Thursday, August 7, 2008 1:31 PM
  •  

    Yes,  I did try that code but it did not work since it is for other than bidirectional.  As far as my application goes, all I did was add a table to the sample databases and then replaced all occurences within the application code and stored procedures for the orders table and columns with my table and columns.  I commented out the occurences of the orders_details table so that it is not used.  I added my table to the anchor table.  For instance since there was a syncadapter for the orders table, one for the client and one for the server, I replace "orders" with "mytablename".  I thoroughly checked all replacement locations including the column names and column types. 

    I then am able to insert into my client table from the button on the form.  When I press the synchronize button the application syncs my server table with the client table.  I then switch to the datagridview on the form for the server.  I press the insert button and a record gets inserted into my server table.  I then press the synchronize button and nothing happens, no error and no synced data in my client table.  I have debugged and stepped through and it appears to step through everything as it should.  Right now I am stumped.  So any and all suggestions are welcome.

    Thursday, August 7, 2008 3:35 PM
  • I am trying to get  download only sync to work. I modified my ApplyChanges() according to a previous post .
    I also get the dreaded error : 
    "...unable to find a SyncAdapter for the specified SyncTable; the table name is null or empty;
    or the table name contains the keywords SET, FMTONLY, and OFF."

    I added the  SqlExpressClientSyncAdapter class to a project based on http://msdn.microsoft.com/en-us/library/bb726015.aspx

    ----------------------------------------------------------------------------------------------------------------------
    Here is my code : 
           SqlSyncAdapterBuilder modelBuilder = new SqlSyncAdapterBuilder(serverConn);

                modelBuilder.TableName = "dbo.Model";
                modelBuilder.TombstoneTableName = modelBuilder.TableName + "_Tombstone";
                modelBuilder.SyncDirection = SyncDirection.DownloadOnly;
                modelBuilder.CreationTrackingColumn = "InsertTimestamp";
                modelBuilder.UpdateTrackingColumn = "UpdateTimestamp";
                modelBuilder.DeletionTrackingColumn = "DeleteTimestamp";
                
                SyncAdapter modelSyncAdapter = modelBuilder.ToSyncAdapter();
                modelSyncAdapter.TableName = "Model";
                this.SyncAdapters.Add(modelSyncAdapter);
    ------------------------------------------------------------------------------------------------------------------

    Here is my SQL table Schema:

    CREATE TABLE MODEL(
     [ModelID] [int] IDENTITY(1,1) NOT NULL,
     [ModelName] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

     [UpdateTimestamp] [timestamp] NOT NULL,
     [InsertTimestamp] [binary](8) NULL DEFAULT (@@dbts+(1)),
     [UpdateId] [uniqueidentifier] NOT NULL DEFAULT ('00000000-0000-0000-0000-000000000000'),
     [InsertId] [uniqueidentifier] NOT NULL DEFAULT ('00000000-0000-0000-0000-000000000000'),

     CONSTRAINT [MODELInd_pubind] PRIMARY KEY CLUSTERED
    (
     [ModelID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    go

    --------------------------------------------------------------------------------------------------------------------
    Do I need a update trigger  ? Do I need a  'update_originator_id' column ?
    I would love to see more samples showing how to use the SqlExpressClientSyncAdapter class .
    Also , where can I find more info on the client metadata files (Guid, anchor) ?

    Thanks,Peter

    Friday, February 13, 2009 12:52 AM
  • HI,

    We also faced these kind of errors initially.

    May i know the version of sync framework you downloaded.

    Please make sure you have the following dll with this version number in order to test  sync with SQL Server/Express 2005 to SQL Server/Express 2005,

    Microsoft.Synchronization.Data.dll - File Version 2.0.1215.0
    Microsoft.Synchronization.Data.Server.dll - File Version 2.0.1215.0
    Microsoft.Synchronization.dll - 1.0.1215.0

    Im not sure about this, in some forums they have said that once the sync process ends up with any error, it will not perform the sync again until we reset the anchor values.

    So please try by creating the client and server Db again with the latest Dlls and give a try.

    please let me know the result.

    Regards,
    jawahar S



    Thursday, March 5, 2009 6:39 AM