none
Incorrect column definitions when provisioning a database that uses aspnet_Membership schema

    Question

  • I am getting incorrect columns when provisioning a database that uses the aspnet_Membership schema. For example, when I try to provision the aspnet_Roles table, the aspnet_Roles_tracking table is created with an ApplicationId column which is marked as a Primary Key, and a LoweredRoleName column which is also marked as a Primary Key. The source table, aspnet_Roles, has a primary key on the RoleId column. So, I can't understand how the tables columns are being confused like this. The XML data that is created and placed in the config_data column of the scope_config table looks like this:

      <Adapter Name="[aspnet_Roles]" GlobalName="[aspnet_Roles]" TrackingTable="[aspnet_Roles_tracking]" SelChngProc="[aspnet_Roles_selectchanges]" SelRowProc="[aspnet_Roles_selectrow]" InsProc="[aspnet_Roles_insert]" UpdProc="[aspnet_Roles_update]" DelProc="[aspnet_Roles_delete]" InsMetaProc="[aspnet_Roles_insertmetadata]" UpdMetaProc="[aspnet_Roles_updatemetadata]" DelMetaProc="[aspnet_Roles_deletemetadata]" BulkTableType="[aspnet_Roles_BulkType]" BulkInsProc="[aspnet_Roles_bulkinsert]" BulkUpdProc="[aspnet_Roles_bulkupdate]" BulkDelProc="[aspnet_Roles_bulkdelete]" InsTrig="[aspnet_Roles_insert_trigger]" UpdTrig="[aspnet_Roles_update_trigger]" DelTrig="[aspnet_Roles_delete_trigger]">
        <Col name="ApplicationId" type="uniqueidentifier" param="@P_1" pk="true" /> <<<==========HERE
        <Col name="RoleId" type="uniqueidentifier" param="@P_2" /> <<<==========THIS SHOULD BE THE PRIMARY KEY
        <Col name="RoleName" type="nvarchar" size="256" param="@P_3" />
        <Col name="LoweredRoleName" type="nvarchar" size="256" param="@P_4" pk="true" /> <<<==========HERE
        <Col name="Description" type="nvarchar" size="256" null="true" param="@P_5" />
        <Col name="ProjectId" type="uniqueidentifier" null="true" param="@P_6" />
      </Adapter>

    I am also getting this same behavior regarding the aspnet_Users table. Other tables in the provisioning seem to have been handled correctly. Has anyone seen this behavior before? Thanks for whatever help you can provide.


    rogdawg

    Sunday, September 23, 2012 9:34 PM

Answers

  • this looks like due to DataReader's GetSchemaTable which is used internally by Sync Fx to grab the table structure (see: http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/c1c113e2-32dc-4826-b6e0-17dff29c1baf)

    as a workaround, just set the PK yourself.

    e.g.,

    downloadOnlyScopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("aspnet_Users", (System.Data.SqlClient.SqlConnection)provider.Connection));

    //untag wrong PK information foreach(var pkColumn in downloadOnlyScopeDesc.Tables["aspnet_Users"].PkColumns) { downloadOnlyScopeDesc.Tables["aspnet_Users"].Columns[pkColumn.QuotedName].IsPrimaryKey = false; }

    //tag the correct PK downloadOnlyScopeDesc.Tables["aspnet_Users"].Columns["UserId"].IsPrimaryKey = true;




    Tuesday, September 25, 2012 2:48 AM
    Moderator

All replies

  • is this the out-of-the-box membership provider tables? can you post your provisioning code?

    i just did a quick test (same one i posted on your other thread) and I'm not getting this behaviour.

    Monday, September 24, 2012 2:54 AM
    Moderator
  • JuneT, thank you for your continuing help on this!

    I am using out-of-the-box membership provider tables, with a Projects table added. I will post the code for that table.

    Here is my provisioning code. It is very basic:

            private SqlSyncProvider ConfigureMembershipProvider()
            {
                string connStr = ConfigurationManager.ConnectionStrings["connMembership"].ConnectionString;
                SqlConnection conn = new SqlConnection(connStr);
                SqlSyncProvider provider = new SqlSyncProvider("Membership", conn);
                SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning((SqlConnection)provider.Connection);  
                if (!serverConfig.ScopeExists("Membership"))
                {
                    DbSyncScopeDescription downloadOnlyScopeDesc = new DbSyncScopeDescription("Membership");
                    downloadOnlyScopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("aspnet_Applications", (System.Data.SqlClient.SqlConnection)provider.Connection));
                    downloadOnlyScopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("aspnet_Users", (System.Data.SqlClient.SqlConnection)provider.Connection));
                    downloadOnlyScopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("aspnet_Membership", (System.Data.SqlClient.SqlConnection)provider.Connection));
                    downloadOnlyScopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("aspnet_Roles", (System.Data.SqlClient.SqlConnection)provider.Connection));
                    downloadOnlyScopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("aspnet_UsersInRoles", (System.Data.SqlClient.SqlConnection)provider.Connection));
                    downloadOnlyScopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Projects", (System.Data.SqlClient.SqlConnection)provider.Connection));
                    serverConfig.PopulateFromScopeDescription(downloadOnlyScopeDesc);
                    serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
                    serverConfig.Apply();
                }
                return provider;
            }

    • Edited by rknowles Monday, September 24, 2012 12:09 PM
    Monday, September 24, 2012 12:08 PM
  • and here is the code to create the Projects table:

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Projects](
    	[ProjectId] [uniqueidentifier] NOT NULL,
    	[ProjectName] [nvarchar](50) NULL,
     CONSTRAINT [PK_Projects] PRIMARY KEY CLUSTERED 
    (
    	[ProjectId] 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


    rogdawg

    Monday, September 24, 2012 12:10 PM
  • other relevant information:

    I am using SQL Server 2008 R2 with SQL Server Management Studio version 10.50.1617.0 and .NET Framework 2.0.50727.5456.

    Visual Studio 2010 Premium.

    My project is a WCF service with .NET Framework 4 as its target framework.

    I am using Sync Framework 2.1


    rogdawg

    Monday, September 24, 2012 5:23 PM
  • this looks like due to DataReader's GetSchemaTable which is used internally by Sync Fx to grab the table structure (see: http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/c1c113e2-32dc-4826-b6e0-17dff29c1baf)

    as a workaround, just set the PK yourself.

    e.g.,

    downloadOnlyScopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("aspnet_Users", (System.Data.SqlClient.SqlConnection)provider.Connection));

    //untag wrong PK information foreach(var pkColumn in downloadOnlyScopeDesc.Tables["aspnet_Users"].PkColumns) { downloadOnlyScopeDesc.Tables["aspnet_Users"].Columns[pkColumn.QuotedName].IsPrimaryKey = false; }

    //tag the correct PK downloadOnlyScopeDesc.Tables["aspnet_Users"].Columns["UserId"].IsPrimaryKey = true;




    Tuesday, September 25, 2012 2:48 AM
    Moderator
  • What a fantastic answer!

    Thank you for your follow-up on this and for the great information.

    I can't thank you enough.


    rogdawg

    Tuesday, September 25, 2012 2:24 PM