locked
Microsoft Sync + Inner Join in Command RRS feed

  • Question

  • Hi Guys!

     

       I have an issue with the Microsoft Sync Framework!

     

       I have created a little test with works great, but when I start to having Inner Joins in my SQL command it start to come with the following error.

     

      e.Message "Unable to initialize the client database, because the schema for table 'Customer' could not be retrieved by the GetSchema() method of DbServerSyncProvider. Make sure that you can establish a connection to the client database and that either the SelectIncrementalInsertsCommand property or the SelectIncrementalUpdatesCommand property of the SyncAdapter is specified correctly." string

       My Code looks like this.

     

    SyncAdapter CustomerSync = new SyncAdapter();

    CustomerSync.TableName = "Customer";

    SqlCommand cmd = new SqlCommand();

    cmd.Connection = this.Connection as SqlConnection;

    cmd.CommandText = "SELECT Customer.* " +

    "FROM dbo.Customer INNER JOIN " +

    " dbo.CustomerEmployee ON dbo.Customer.ID = dbo.CustomerEmployee.CustomerID INNER JOIN" +

    " dbo.Employee ON dbo.CustomerEmployee.EmployeeID = dbo.Employee.ID" +

    "WHERE dbo.Employee.ID = 2378";

     

    cmd.CommandType = System.Data.CommandType.Text;

    CustomerSync.SelectIncrementalInsertsCommand = cmd;

    this.SyncAdapters.Add(CustomerSync);

     

      Is it a problem with the Sync Framework or am I doing something wrong.

     

      My Next issue is that I want to transfer custom parameters to the current SQL code.

     

    Hope you are able to give me a hint of wants wrong.

     

    Best Regards

      Lars

    • Moved by Max Wang_1983 Friday, April 22, 2011 5:08 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Tuesday, September 2, 2008 6:39 AM

All replies

  • Can you do this SELECT query using the same SQL connecting credential as you have in the sync applicaiton?

     

    Thanks.

    Tuesday, September 2, 2008 5:56 PM
    Answerer
  •  

    Hi L Zhou!

     

      I don't understand what you are trying to say?

     

      I can easily run the command in Microsoft SQL Server Management Studio and it return the correct values?

     

      If I just have the SQL Query as follows : Select * FROM Customer

         But when I start joining tables it starts to give me a SchemaException, which I don't understand.

     

      The Exception is as followed.

     

         Unable to initialize the client database, because the schema for table 'Customer' could not be retrieved by the GetSchema() method of DbServerSyncProvider. Make sure that you can establish a connection to the client database and that either the SelectIncrementalInsertsCommand property or the SelectIncrementalUpdatesCommand property of the SyncAdapter is specified correctly.

    Wednesday, September 3, 2008 6:01 AM
  •  

    Hi Again!

     

       I have found my error, in the Inner Join issue, I has to list all fields in the Customer Table, then it works.

     

       I have now got an issue when trying to pass parameters to a Stored Procedure, which wrap the SQL query listed above in.

       It takes a parameter @EmployeeID as parameter!

     

    SyncAdapter CustomerSync = new SyncAdapter();

    CustomerSync.TableName = "Customer";

    SqlCommand cmd = new SqlCommand();

    cmd.Connection = this.Connection as SqlConnection;

    cmd.Parameters.AddWithValue("@employeeID", 2378);

    cmd.CommandText = "stpGetCustomersTest";

    cmd.CommandType = System.Data.CommandType.StoredProcedure;

     

     

      I know get the following error:

     

      "Unable to set session parameters in DbServerSyncProvider. Cannot obtain the value for command parameter '@employeeID'."

     

      Are there anybody there could tell whats wrong?

    Wednesday, September 3, 2008 7:46 AM
  • The parameters must be set on the SyncAgent's SyncConfiguration.  So for example:

    syncAgent.SyncConfiguration.SyncParameters.Add("@employeeID", 2378);

    Hope this helps.

    Thanks-
    Wednesday, September 3, 2008 4:10 PM
  •  

    Hi

     

      Now I have tested it with the SyncAgent.Configuration.SyncParameters.Add("@employeeID", 2378);

     

      I've got this error message instead:

     

    Unable to enumerate changes at the DbServerSyncProvider for table 'Customer' in synchronization group 'Customer'.

     

      I have the following code in my SyncAgent class.

     

    public class SynchronizeDataFromServer : SyncAgent

    {

    public SynchronizeDataFromServer()

    {

    Init();

    this.StateChanged += new EventHandler<SessionStateChangedEventArgs>(SynchronizeDataFromServer_StateChanged);

    this.SessionProgress += new EventHandler<SessionProgressEventArgs>(SynchronizeDataFromServer_SessionProgress);

    }

    void SynchronizeDataFromServer_SessionProgress(object sender, SessionProgressEventArgs e)

    {

    }

    void SynchronizeDataFromServer_StateChanged(object sender, SessionStateChangedEventArgs e)

    {

    }

    public void StartSync()

    {

    try

    {

    SyncStatistics s = this.Synchronize();

    }

    catch (Exception e)

    {

    int n = 0;

    }

    }

    private void Init()

    {

    try

    {

    this.LocalProvider = new SqlCeClientSyncProvider(

    "Data Source=" + MS_Server_Test_Sync.Properties.Settings.Default.FilePath +

    "\\" + MS_Server_Test_Sync.Properties.Settings.Default.DatabaseName + ".sdf;" +

    "Password= " + MS_Server_Test_Sync.Properties.Settings.Default.Password + ";", true);

    SyncTable Customer = new SyncTable();

    Customer.TableName = "Customer";

    Customer.SyncDirection = SyncDirection.DownloadOnly;

    Customer.CreationOption = TableCreationOption.UseExistingTableOrFail;

    // this.Configuration.SyncParameters.Add(new SyncParameter("@EmployeeID", 2378));

    // this.Configuration.SyncParameters.Add("@EmployeeID", 2365);

    this.Configuration.SyncParameters.Add("@Employee", 2365);

    SyncTable Equipment = new SyncTable();

    Equipment.TableName = "EquipmentAtCustomer";

    Equipment.SyncDirection = SyncDirection.DownloadOnly;

    Equipment.CreationOption = TableCreationOption.UseExistingTableOrFail;

    SyncTable SalesStatistics = new SyncTable();

    SalesStatistics.TableName = "SalesStatistics";

    SalesStatistics.SyncDirection = SyncDirection.DownloadOnly;

    SalesStatistics.CreationOption = TableCreationOption.UseExistingTableOrFail;

    this.Configuration.SyncTables.Add(Customer);

    this.Configuration.SyncTables.Add(Equipment);

    this.Configuration.SyncTables.Add(SalesStatistics);

    this.RemoteProvider = new FromServer();

    }

    catch (Exception ex)

    {

    string s = ex.Message;

    }

    }

    }

     

     

    In the FromServer class

    --------------------------------------------------------------------------------------------------------------------

    public class FromServer : DbServerSyncProvider

    {

    public FromServer()

    {

    Init();

    }

    private void Init()

    {

    SqlConnectionStringBuilder str = new SqlConnectionStringBuilder();

    str.InitialCatalog = "MangoDB";

    str.UserID = "SmartUser";

    str.Password = "Smartuser";

    str.ConnectTimeout = 60;

    str.DataSource = "10.65.25.72";

    str.PacketSize = 4096;

    //str.IntegratedSecurity = true;

    this.Connection = new SqlConnection(str.ToString());

    //this.SelectNewAnchorCommand = new SqlCommand("SELECT @@DBTS", this.Connection as SqlConnection);

    SyncAdapter CustomerSync = new SyncAdapter();

    CustomerSync.TableName = "Customer";

    SqlCommand cmd = new SqlCommand();

    cmd.Connection = this.Connection as SqlConnection;

    cmd.CommandText = "stpGetCustomersTest2";

    cmd.CommandType = System.Data.CommandType.StoredProcedure;

     

    CustomerSync.SelectIncrementalInsertsCommand = cmd;

    this.SyncAdapters.Add(CustomerSync);

    }
    Thursday, September 4, 2008 6:28 AM
  • Lars-

    Does the "stpGetCustomersTest2" stored procedure execute fine if you run it in Management Studio?  Could your provide more detail as to the arguments and body of this stored procedure?

    Thanks-
    Thursday, September 4, 2008 6:39 PM
  •  

    Hi!

     

      The stpGetCustomersTest2 works fine in Management Studion

     

      The Stored procedure look like this.

     

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    go

     

     

     

    CREATE PROCEDURE [dbo].[stpGetCustomersTest2]

    @EmployeeID int

    AS

    SELECT dbo.Customer.ID, dbo.Customer.CustomerNumber, dbo.Customer.Name, dbo.Customer.CustomerGroup, dbo.Customer.Address, dbo.Customer.Postbox,

    dbo.Customer.ZipCode, dbo.Customer.PostAddress, dbo.Customer.Telephone, dbo.Customer.Telephone2, dbo.Customer.Telefax, dbo.Customer.Email,

    dbo.Customer.ResponsibleRole, dbo.Customer.Planogram, dbo.Customer.Information, dbo.Customer.CustomerStrategy, dbo.Customer.Warehouse,

    dbo.Customer.DeliveryDay, dbo.Customer.OrderDeadline, dbo.Customer.Turnover, dbo.Customer.ContactPerson, dbo.Customer.ContactEmployeeInfo,

    dbo.Customer.CustomerCategory, dbo.Customer.AreaType, dbo.Customer.AreaCode, dbo.Customer.Kommune, dbo.Customer.Fylke, dbo.Customer.Status,

    dbo.Customer.EarlierCustomerID, dbo.Customer.Payment, dbo.Customer.CampaignCustomer, dbo.Customer.ChannelID, dbo.Customer.CompanyID,

    dbo.Customer.Chain1ID, dbo.Customer.Chain2ID, dbo.Customer.Chain3ID, dbo.Customer.Chain4ID, dbo.Customer.AssortmentID, dbo.Customer.OrderType,

    dbo.Customer.OrderDeliveryTime, dbo.Customer.CallFrequencySR, dbo.Customer.CallFrequencyMRC, dbo.Customer.Barrels, dbo.Customer.VATregistration,

    dbo.Customer.Region, dbo.Customer.Competitorid, dbo.Customer.Competitor_dealout, dbo.Customer.IsscomId, dbo.Customer.Created, dbo.Customer.Updated,

    dbo.Customer.LegalOwner, dbo.Customer.BusinessHours, dbo.Customer.OpeningHours, dbo.Customer.ServiceTech, dbo.Customer.TeleAgent,

    dbo.Customer.KeyAccount

    FROM dbo.Customer INNER JOIN

    dbo.CustomerEmployee ON dbo.Customer.ID = dbo.CustomerEmployee.CustomerID INNER JOIN

    dbo.Employee ON dbo.CustomerEmployee.EmployeeID = dbo.Employee.ID

    WHERE (dbo.Employee.ID = @EmployeeID)

    Friday, September 5, 2008 7:33 AM
  • Lars-

    Your proc looks syntactically correct.  On the
    Unable to enumerate changes at the DbServerSyncProvider for table 'Customer' in synchronization group 'Customer'. exception that you get, what is the InnerException?   That will provide more insight into what is wrong since this message is a result of a DbException that will be contained in the InnerException of the DataSyncException that is being raised.

    Thanks-
    Friday, September 5, 2008 6:00 PM
  • Hi !

     

     

     The inner exception is the following.

     

      e.InnerException.Message "Procedure or function 'stpGetCustomersTest2' expects parameter '@employeeID', which was not supplied." string

       I have the parameter with in the following.

     

    SyncTable Customer = new SyncTable();

    Customer.TableName = "Customer";

    Customer.SyncDirection = SyncDirection.DownloadOnly;

    Customer.CreationOption = TableCreationOption.UseExistingTableOrFail;

     

    this.Configuration.SyncParameters.Add("@EmployeeID", 2365);

     

    Monday, September 8, 2008 3:12 PM
  • Maybe you have already done this but have you added the parameter to the command on the adapter. I think you had it previously with value. The pattern is that the command on the adapter has the parameter and the Configuration.SyncParameters collection provides the value. When executing the command the provider takes the value for the parameter from the Configuration.SyncParameters collection and binds it to the matching parameter on the command. There may be case sensitivity also going on so keep an eye for that. Check with profiler if the command executed on the database has the parameter set with the right value.

     

    thanks

    Sudarshan

    Tuesday, September 9, 2008 8:33 PM
    Moderator
  •  

    Hi Sudarshan!

     

      As I understand you reply, I should have both the SyncParameter on and on the current SqlCommand?

     

      Is this correct?

    private void Init()

    {

    try

    {

    this.LocalProvider = new SqlCeClientSyncProvider(

    "Data Source=" + MS_Server_Test_Sync.Properties.Settings.Default.FilePath +

    "\\" + MS_Server_Test_Sync.Properties.Settings.Default.DatabaseName + ".sdf;" +

    "Password= " + MS_Server_Test_Sync.Properties.Settings.Default.Password + ";", true);

    SyncTable Customer = new SyncTable();

    Customer.TableName = "Customer";

    Customer.SyncDirection = SyncDirection.DownloadOnly;

    Customer.CreationOption = TableCreationOption.UseExistingTableOrFail;

    this.Configuration.SyncParameters.Add("@EmployeeID", 2365);

     

    this.Configuration.SyncTables.Add(Customer);

    this.RemoteProvider = new FromServer();

    }

     

    And on the ServerSide

    private void Init()

    {

    SqlConnectionStringBuilder str = new SqlConnectionStringBuilder();

    str.InitialCatalog = "MangoDB";

    str.UserID = "SmartUser";

    str.Password = "Smartuser";

    str.ConnectTimeout = 60;

    str.DataSource = "10.65.25.72";

    str.PacketSize = 4096;

    //str.IntegratedSecurity = true;

    this.Connection = new SqlConnection(str.ToString());

    //this.SelectNewAnchorCommand = new SqlCommand("SELECT @@DBTS", this.Connection as SqlConnection);

    SyncAdapter CustomerSync = new SyncAdapter();

    CustomerSync.TableName = "Customer";

    SqlCommand cmd = new SqlCommand();

    cmd.Connection = this.Connection as SqlConnection;

    cmd.CommandText = "stpGetCustomersTest2";

    cmd.CommandType = System.Data.CommandType.StoredProcedure;

     

    //cmd.Parameters.Add(new SqlParameter("@EmployeeID", 2378));

    CustomerSync.SelectIncrementalInsertsCommand = cmd;

    this.SyncAdapters.Add(CustomerSync);

     

    Should I enable this command as well and what should I put in it?

     

    Hope you can answer this question

    Wednesday, September 10, 2008 10:34 AM
  • Yes that is correct. You need to add the parameter to the command so that the server provider can then pass the value during sync. During sync the value set on Configuration.SyncParameters collection will be sent to the server provider and will be passed as parameter to the stored proc.

     

    thanks

    Sudarshan

     

    Tuesday, September 16, 2008 5:00 PM
    Moderator