none
Additional Stored Procedure with Bi Directional Filtered Scope. RRS feed

  • Question

  • Hi,


    I am provisioning a scope with table CR_SERVICE_REQUEST. I need it Bi Directional.It is also filtered.

    But I am not able to get both the things simultaneously.

       If I use the code,I get the uniqueness.

    serverConfig.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);

    But I get  only one Stored Procedure .

    dbo.CR_SERVICE_REQUEST_selestchanges_some guid.

    While If I put serverConfig.SetCreateProceduresDefault(DbSyncCreationOption.Create);

    then I get all the Stored procedures.

    dbo.CR_SERVICE_REQUEST_selectchanges
    Sync.CR_SERVICE_REQUEST_delete
    Sync.CR_SERVICE_REQUEST_insert
    Sync.CR_SERVICE_REQUEST_selectrow
    Sync.CR_SERVICE_REQUEST_update

    But I loose the uniqueness of making selected changes of every additional scope.

    I want to implement both  the things.

    Regards,

    Sachin K



    Wednesday, May 8, 2013 6:39 AM

All replies

  • the insert/update/delete stored procedures are shared among scopes, why would you want them separately? are you synching different column counts for each scope?
    Wednesday, May 8, 2013 7:28 AM
    Moderator
  • Hi June,

    I have requirement  of where there will be multiple Clients with One Server.The Scope would be Bidirectional and filtered.The Column would be same with different value in where clause(or BILLING_UNIT_C ='4142' /BILLING_UNIT_C ='5886') with each scope with each client  .

    i.e Client A might have Scope with where clause as 4142 and Bidirectional while Client B have Scope with where Clause='5886' which is also bidirectional.

    Below is the source code implementation with different scopes with different where clause.

    serverConfig.SetCreateProceduresDefault(DbSyncCreationOption.Skip);
    serverConfig.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);

    They create different Stored Procedures but dont create insert update and delete SPs respectively.

    Please let me know for any other concerns.

    Regards,

    Sachin K

    Wednesday, May 8, 2013 9:06 AM
  • as I have mentioned, that's how it is. the insert, update and delete are shared by the scopes. those stored procedures don't need the filter value.

    and as I have mentioned in your other thread, you should be using filtered scope templates so that the filter value is not hard coded in the select changes stored proc.

    if you have a thousand scopes, I don't think you like to have a thousand stored procs.

    Wednesday, May 8, 2013 12:46 PM
    Moderator
  • Hi June,

    Can you provide me an example?

    I have tried from url http://jtabadero.wordpress.com/2010/09/02/sync-framework-provisioning/.

    I am getting issue at       serverProvWA.PopulateFromTemplate(scopeName, templScopeName);

    it says  An SqlParameter with ParameterName '@BILLING_UNIT_C' is not contained by this SqlParameterCollection.

    I can successfully create template.

    string xpath = null;
                        XPathNodeIterator scopeTmplNodeIt = null;
                        XPathNavigator scopeTmplNode = null;
                        xpath = DBSyncConstants.XPATH_SYNC_PROVIDER_TEMPLATE_CONFIG;
                        xpath += "[@templateName='" + scopeName + "']";
                        scopeTmplNodeIt = DBSyncXMLUtil.getNodeList(DBSyncXMLUtil.getDocumnetRoot(), xpath);
                        string templScopeName = "";
                        if (scopeTmplNodeIt.MoveNext())
                        {
                            scopeTmplNode = scopeTmplNodeIt.Current;
                            templScopeName = DBSyncXMLUtil.getAttributeValue(scopeTmplNode, "scopeName");                    
                        }
                        XPathNavigator tableNode = null;
                        XPathNodeIterator itTable = null;
                        string tableName = "";
                        itTable = DBSyncXMLUtil.getNodeList(scopeConfigNode, "Tables/TableName");                  
                        DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(templScopeName);
                        string colName = null;
                        string filterClause = "";
    
                        while (itTable.MoveNext())
                        {
                            tableNode = itTable.Current;
                            tableName = DBSyncXMLUtil.getAttributeValue(tableNode, "name");
                            Console.WriteLine("ScopeConfigHandler :: loadScopeTableInfo() :: tableName ==" + tableName);
                            filterClause = DBSyncXMLUtil.getNodeValue(tableNode, "FilterClause");                      
                            DbSyncTableDescription orderDescription = SqlSyncDescriptionBuilder.GetDescriptionForTable(tableName, conn);
                            scopeDesc.Tables.Add(orderDescription);
                            XPathNodeIterator colNodeIt = DBSyncXMLUtil.getNodeList(tableNode, "FilterColumns/Col");
                            XPathNavigator colNode = null;
                            while (colNodeIt.MoveNext())
                            {
                                colNode = colNodeIt.Current;
                                colName = DBSyncXMLUtil.getAttributeValue(colNode, "name");                         
                            }
                        }
                        XPathNodeIterator sqlNodeIt = DBSyncXMLUtil.getNodeList(tableNode, "FilterParameters/SqlParameter");
                        XPathNavigator sqlNode = null;
                        SqlParameter param = null;
                        string paramName = null;
                        string paramSize = null;
                        string paramType = null;
                        while (sqlNodeIt.MoveNext())
                        {
                            sqlNode = sqlNodeIt.Current;
                            paramName = DBSyncXMLUtil.getAttributeValue(sqlNode, "name");
                            paramType = DBSyncXMLUtil.getAttributeValue(sqlNode, "SqlDbType");
                            paramSize = DBSyncXMLUtil.getAttributeValue(sqlNode, "size");
                            if (paramSize != null)
                                param = new SqlParameter(paramName, DBSyncApplicationUtil.getSqlDbType(paramType), Convert.ToInt32(paramSize));
                            else
                                param = new SqlParameter(paramName, DBSyncApplicationUtil.getSqlDbType(paramType));
                          
                        }
    
    
    
                        scopeDesc.UserComment = "Filter template for Orders.OriginState";
                        DbSyncTableDescription ordersDescription = SqlSyncDescriptionBuilder.GetDescriptionForTable(tableName, conn);
                        //DbSyncTableDescription ordersDescription = SqlSyncDescriptionBuilder.GetDescriptionForTable("Orders", conn);
                        scopeDesc.Tables.Add(ordersDescription);
                        SqlSyncScopeProvisioning serverTemplate = new SqlSyncScopeProvisioning(conn, scopeDesc, SqlSyncScopeProvisioningType.Template);
                      
                        serverTemplate.SetUseBulkProceduresDefault(true);
                        serverTemplate.Tables[tableName].AddFilterColumn(colName);
                       
                        string originalfilterclause = filterClause;
    
                      
                        int firstindexof = filterClause.IndexOf("'");
                        filterClause = filterClause.Substring(0, firstindexof);
                        originalfilterclause = originalfilterclause.Substring(firstindexof, originalfilterclause.Length - firstindexof - 1);
    
                        filterClause = filterClause + paramName + ")";
    
                        serverTemplate.Tables[tableName].FilterClause = "[side].[BILLING_UNIT_C] ="+paramName+"";
                      
                        serverTemplate.Tables[tableName].FilterParameters.Add(param);
                      
                        serverTemplate.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);
                    
                        if(!serverTemplate.TemplateExists(templScopeName))
                        serverTemplate.Apply();
                      
                        SqlSyncScopeProvisioning serverProvWA = new SqlSyncScopeProvisioning(conn);
                        
                        serverProvWA.PopulateFromTemplate(scopeName, templScopeName);
                      
                        serverProvWA.Tables[tableName].FilterParameters[paramName].Value = originalfilterclause;
    
                      
                        serverProvWA.UserComment = "Orders data includes only WA.";
                      
                        serverProvWA.Apply();


    Thanks,

    Sachin K


    Thursday, May 9, 2013 11:51 AM
  • hard to tell what the actual scope definition just by looking at above...

    what's the value of paramName?

    try serverTemplate.Script() or ServerProvWA.Script() to see what the generated SQL look like...

    Thursday, May 9, 2013 11:28 PM
    Moderator