ObjectSchema & ObjectPrefix Ignored in SyncFx 2.1 on SelectChanges SP. RRS feed

  • Question

  • We are in the process of upgrading from SyncFx 2.0 to 2.1. We have a hub-spoke environment so we have a central database and we have client databases. So we're looking for differences both when we upgrade a database on a client and when we do a new install.

    For SyncFx 2.0, if you used a ObjectPrefix of P and an ObjectSchema of S, then the names of the stored procedures created during provisioning with SqlSyncScopeProvisioning.Apply were dbo.P_tableName_selectchanges, dbo.P_tableName_insert, dbo.P_tableName_insertmetadata etc. So you can see that the schema name was ignored. But all of the names are consistent. Without knowing any different at the time, this was just what it was (BTW, the tracking tables were names dbo.P_tableName_tracking so no schema there; in fact just the scope_info and scope_config tables had both schema and prefix added).

    So now we do the same thing using SyncFx 2.1. Here's what we find:

    1. The SPs are named S.P_tableName_insert, S.P_tableName_insertmetadata (i.e.) the schema name is now used. Except...
    2. The SP for select changes is called dbo.tableName_selectchanges, so it is totally inconsistent with the rest of the names in 2.1.
    3. The tracking tables are all named S.P_tableName_tracking, so different than 2.0 but consistent in 2.1.
    4. The scope_config, scope_info and (the new) schema_info tables are all named S.P_tableName. So again consistent in 2.1

    So we have v2.1 improving the naming of everything except the dbo.tableName_selectingchanges which has gone backwards.

    This all matters to us because we chose (poorly apparently) in the beginning over 2 years ago to use ObjectSchema and ObjectPrefix to separate our name space from SyncFx. And we were able to derive names because they were, if not right, then consistent.

    Now this mess. The worst thing about all of this is that not only is are the changes not documented but also it means that new and upgraded databases have a different set of names. Uggh.

    Monday, June 25, 2012 11:09 PM

All replies

  • steve,

    have you tried setting the objectschema and objectprefix after you have added the tables to the scope?

    Monday, June 25, 2012 11:51 PM
  • Hi June,

    By examination of the SqlServerSyncProvisioning object in debug just before the Apply() call, the ObjectSchema and ObjectPrefix properties are already set to the values that we want.


    Tuesday, June 26, 2012 2:10 PM
  • June, I looked at the code in Reflector and although I see that it appears to want to update the names with these values, the net result is that it does not. Whatever bug that exists in setting the full name of the selectchanges is present doing it this way.

    Tuesday, June 26, 2012 6:52 PM
  • if you look at the property setter for objectschema and objectprefix, you will find that it actually loops thru the table collection to set the value (the same thing with the SetCreatexxx methods)

    so if you set the value before adding the tables, it turns out the tables dont inherit the value.

    in my case,  i always set these values just before the call to Apply().

    Tuesday, June 26, 2012 11:52 PM
  • June,

    Yes, I agree with your analysis of what is happening in the property setters. The problem is that there is a bug in the Apply().

    What I meant by the "net result" was that I set the values immediately before the Apply() invocation, as you suggested. But it makes no difference, the results are that the selectchanges SP is incorrectly named.

    Within the SqlSyncTableProvisioning.Apply() method, you will find the following code:

                    this.SelectChangesProcName = this.GetSelectChangesProcName(trans);
                    this._procedureHelper.CreateSelectIncrementalChanges(trans, (this._createProceduresForAdditionalScope == DbSyncCreationOption.Create) ? this._createProceduresForAdditionalScope : this._createProcedures);

    Prior to the first statement, the value of SelectChangesProcName is "ObjectSchema.ObjectPrefix_tableName_selectchanges" but after the call, the value is "dbo.tableName_selectchanges".

    IMO, the problem is that the property SelectChangesProcName uses the local this._procedureHelper.SelectChangesProcName as its underlying value whereas the method GetSelectChangesProcName uses this._SelectChangesProcName. The former has the correct expanded value, the latter has the the default value.

    I can see this by debugging it using the VS Pro version of .NET Reflector with all optimization turned off in the JIT compiler.

    Maybe you can post what you believe is a working codeset since we appear to have differing results.

    Wednesday, June 27, 2012 12:39 AM
  • this._SelectChangesProcName value is actually set to this._procedureHelper.SelectChangesProcName in the SqlSyncTableProvisioning ctor and thats the only place i see it being set.

    i'll dig deep later when i find time.

    Wednesday, June 27, 2012 2:00 AM
  • found some time to test this and i think it maybe a bug.

    the only way i can force it to use the object prefix and object schema is to set

    SetCreateProceduresDefault = Skip


    SetCreateProceduresForAdditionalScopeDefault = Create

    but then your selectchanges sp will have that nasty guid suffix.

    to add, the selectchanges name is set initially with the default object prefix and schema name as empty and the only time it gets reset is inside the call to this.GetSelectChangesProcName. in which case if its not an additional selectchanges sp, it returns the existing value which doesnt have the object prefix and object schema.

    • Proposed as answer by Yahmez Friday, August 31, 2012 4:33 AM
    • Unproposed as answer by Yahmez Friday, August 31, 2012 4:34 AM
    Wednesday, June 27, 2012 3:50 AM
  • Hi - I don't mean to muddy the waters, but if anyone else out there is getting this,
    and supposing you don't have any better ideas you could take the approach of 'correcting' the affected database objects.

    This requires getting the selectchange procedures under the correct schema.

    In theory you can transfer them with over with  "ALTER SCHEMA sync TRANSFER dbo.yourtable_selectchanges"

    ...In practice with permissions available, I ended up recreating the select procedures under the target schema

    [getting the currect proc text]

    cmd.CommandText = String.Format(@"SELECT OBJECT_DEFINITION(OBJECT_ID(N'dbo.{0}_selectchanges'))", tbl);..then do the appropriate replace on the CREATE PROCEDURE [schema] to get the name right... the other issue was that the scope_config table needed to be amended.  After these steps it *seems* to work okay.

    Friday, August 31, 2012 5:02 AM
  • Yahmez,

    This issue is not that there are no workarounds. The issue is that you shouldn't have to.

    There is a bug here on a very basic piece of provisioning functionality. It is not some esoteric thing, it is "Does the support for object prefix and object schema work?", something that should be in any basic set of functional regression tests of provisioning. But clearly it is not, which says something about how this thing is tested before release.

    In my opinion, the best workaround is to use the SqlSyncScopeProvisioning.Script() method to obtain the schema for the provisioning, make the edits to that schema to fix the problems and use that script for provisioning rather than using SqlSyncScopeProvisioning.Apply(). You can even do this in code.

    But like I said, you shouldn't have to do this.


    Friday, August 31, 2012 5:32 AM