Could not find stored procedure 'mdpsp_sys

Could not find stored procedure 'mdpsp_sys_LoadMetaClassByName'. error when installing commerce

Vishal Panday

Vote:

Ok Guys,

please bear with me. I'm new into the commerce part.

So i have the CMS site running.

I followed the steps for installing commerce: https://world.episerver.com/documentation/developer-guides/commerce/getting-started/creating-your-project/

I created a new project in my VS solution for commerce manager.
I installed the commerce and commerce integration nuget into the CMS project in VS.
I installed the commercemanager nuget package into the Commerce Project in VS.
I created in my IIS a new web for the Manager added bindings etc. I added the binding in the appsettings
I added in the Commerce Project web.config the correct SQL connectionstrings to both my CMS and Commerce db. (EPiServerDB and EcfSqlConnection ) basically the same as in the CMS project web.config.

So when i start the commercemanager site or even the CMS site i get the following error:

Could not find stored procedure 'mdpsp_sys_LoadMetaClassByName'.

What am i missing here?

This is just local. I still need to deploy this to a DXC and already experiencing problems :-)

#194011

Edited, Jun 11, 2018 13:45

Quan Mai

Vote:

It looks like your "Commerce" database is an empty one. I'd suggest to run Episerver.Commerce.Core.sql which can be found at packages\EPiServer.Commerce.Core.<version>\tools to initialize your database

#194014

Jun 11, 2018 13:56

Vishal Panday

Vote:

Hi Quan... yes its an empty db... I thought during initialization the database schema is being excuted... I dont see anything in the documentation to seperatly initialize the database.

Anyways, i excuted the sql script on the commerceDb. I see the tables added and the storedprocedures.

When calling the commercemanager url now i get the following.

The database schema for 'CMS' has not been updated to version '7052.0', current database version is '7056.0'. Update the database manually by running the cmdlet 'Update-EPiDatabase' in the package manager console or set updateDatabaseSchema="true" on episerver.framework configuration element.

But the message is strange. My version is later then the version it wants. Which is weird. Everything is up to date. I have all the latest nuget packages. I even have the UpdateDatabaseSchema=true in the episerver.framework, just for it to selfadjust everything.

#194016

Jun 11, 2018 14:17

Quan Mai

Vote:

Your CMS db is now newer than your assemblies. You would have to upgrade your CMS nuget packages and then run update-epidatabase cmdlet.

#194017

Jun 11, 2018 14:20

Vishal Panday

Vote:

Ohh yes Quan... i thought it installed the latest CMS nuget packages for the CommerceManager project , but that was not the case..

Ok i have a feeling we are getting closer. Updated everything, but now i get the following on both the CMS site aswell as the commercemanager site ... grrr :-)

The remote server returned an error: (401) Unauthorized.

What am i missing now?

#194019

Edited, Jun 11, 2018 14:32

Quan Mai

Vote:

This is with your ServiceBus configuration and have nothing to do with Episerver

#194021

Jun 11, 2018 14:33

Vishal Panday

Vote:

You are right Quan. I disabled the AzureEvent for now, because it was added by the packages and not used.

Before i continue, thanks for your swift help. You are awesome.

Now eveything started up. Also asked me for migration when logging into the CMS. I did the migration. I see the Commerce button in the CMS. The Catalog i see fine, but when i click the Commerce Manager in the CMS, it gives me the following.

The site has migration steps that have not been completed. Unless EPiServer.Commerce.Internal.Migration.MigrationSettings.AutoMigrate is enabled, the steps needs to be started on a site that has the EPiServer.Commerce.UI package installed. The value of EPiServer.Commerce.Internal.Migration.MigrationSettings.AutoMigrate can be set from an initialization module by using the service locator, or can be set from the configuration file by adding an appSetting element with key 'AutoMigrateEPiServer' and value 'true' or 'false'

I added the AutoMigrateEpiserver appsetting in the CMS's web.config. But still get the message...

#194022

Jun 11, 2018 14:50

Quan Mai

Vote:

You started your CM site too soon, before all migration steps completed. You should restart it (IIS reset or something) and it should be fine.

#194024

Jun 11, 2018 14:53

Vishal Panday

Vote:

Yes you are right Quan.. thanks for all the help.. It looks like everything works now, so i can continue :-)

#194029

Jun 11, 2018 15:50

Vishal Panday

Vote:

Hi Quan,

one more thing. I have a admin user which is member of both the Admins and Webadmins group. I can login to the CMS with that user.

But cannot login to the Commerce Manager with it.

Could it be something with my roleproviders. Now it looks like this. This the web.config of the CMS site. So do i need to do more?

 <profile defaultProvider="DefaultProfileProvider">
      <properties>
        <add name="Address" type="System.String" />
        <add name="ZipCode" type="System.String" />
        <add name="Locality" type="System.String" />
        <add name="Email" type="System.String" />
        <add name="FirstName" type="System.String" />
        <add name="LastName" type="System.String" />
        <add name="Language" type="System.String" />
        <add name="Country" type="System.String" />
        <add name="Company" type="System.String" />
        <add name="Title" type="System.String" />
        <add name="CustomExplorerTreePanel" type="System.String" />
        <add name="FileManagerFavourites" type="System.Collections.Generic.List`1[System.String]" />
        <add name="EditTreeSettings" type="EPiServer.Personalization.GuiSettings, EPiServer.Cms.AspNet" />
        <add name="ClientToolsActivationKey" type="System.String" />
        <add name="FrameworkName" type="System.String" />
      </properties>
      <providers>
        <add name="DefaultProfileProvider" type="System.Web.Providers.DefaultProfileProvider, System.Web.Providers, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="EPiServerDB" applicationName="/" />
      </providers>
    </profile>
    <membership defaultProvider="MultiplexingMembershipProvider" userIsOnlineTimeWindow="10" hashAlgorithmType="HMACSHA512">
      <providers>
        <clear />
        <add name="MultiplexingMembershipProvider" type="EPiServer.Security.MultiplexingMembershipProvider, EPiServer.Framework.AspNet" provider1="SqlServerMembershipProvider" provider2="WindowsMembershipProvider" />
        <add name="WindowsMembershipProvider" type="EPiServer.Security.WindowsMembershipProvider, EPiServer.Cms.AspNet" deletePrefix="BUILTIN\" searchByEmail="true" />
        <add name="SqlServerMembershipProvider" type="System.Web.Providers.DefaultMembershipProvider, System.Web.Providers, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="EPiServerDB" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" applicationName="/" />
      </providers>
    </membership>
    <roleManager enabled="true" defaultProvider="MultiplexingRoleProvider" cacheRolesInCookie="true">
      <providers>
        <clear />
        <add name="MultiplexingRoleProvider" type="EPiServer.Security.MultiplexingRoleProvider, EPiServer.Framework.AspNet" provider1="SqlServerRoleProvider" provider2="WindowsRoleProvider" providerMap1="SqlServerMembershipProvider" providerMap2="WindowsMembershipProvider" />
        <add name="WindowsRoleProvider" applicationName="/" type="EPiServer.Security.WindowsRoleProvider, EPiServer.Cms.AspNet" />
        <add name="SqlServerRoleProvider" type="System.Web.Providers.DefaultRoleProvider, System.Web.Providers, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="EPiServerDB" applicationName="/" />
      </providers>
    </roleManager>
    <anonymousIdentification enabled="true" />

My <Episerver.framework> had the commerceadmins role added

<virtualRoles addClaims="true">
      <providers>
        <add name="Administrators" type="EPiServer.Security.WindowsAdministratorsRole, EPiServer.Framework" />
        <add name="Everyone" type="EPiServer.Security.EveryoneRole, EPiServer.Framework" />
        <add name="Authenticated" type="EPiServer.Security.AuthenticatedRole, EPiServer.Framework" />
        <add name="Anonymous" type="EPiServer.Security.AnonymousRole, EPiServer.Framework" />
        <add name="CmsAdmins" type="EPiServer.Security.MappedRole, EPiServer.Framework" roles="WebAdmins, Administrators, TAMTAM\DEV Server Admins" mode="Any" />
        <add name="CmsEditors" type="EPiServer.Security.MappedRole, EPiServer.Framework" roles="WebEditors" mode="Any" />
        <add name="Creator" type="EPiServer.Security.CreatorRole, EPiServer" />
        <add name="PackagingAdmins" type="EPiServer.Security.MappedRole, EPiServer.Framework" roles="Admins, WebAdmins, Administrators" mode="Any" />
        <add name="VisitorGroupAdmins" type="EPiServer.Security.MappedRole, EPiServer" roles="WebEditors, Admins, WebManagers, CmsAdmins" mode="Any" />
        <add name="SearchAdmins" type="EPiServer.Security.MappedRole, EPiServer.Framework" roles="WebEditors, CmsAdmins, WebAdmins, Administrators, Admins" mode="Any" />
        <add name="CommerceAdmins" type="EPiServer.Security.MappedRole, EPiServer.Framework" roles="WebAdmins, Administrators, Admins" mode="Any" />
      </providers>
    </virtualRoles>

#194051

Edited, Jun 12, 2018 9:50

Quan Mai

Vote:

The login permission is granted to Administrator group, so you would have to add that user to that group, or grant permission to Admins and/or WebAdmins group  <img src="https://i.imgur.com/iT2qCc7.png" width="1148" alt="" height="502" />

#194052

Jun 12, 2018 9:53

Vishal Panday

Vote:

Aha... but from my previous image you can see that i cannot select the Administrators group for my admin user. (its not in the list)

Because the administrators group is from the windowsRoleProvider. And i cannot add another one(from adminster groups) because it says it already exists.

Before i go fiddle with the permissions and add the other group (webadmins) to all the permissions (like you showed), i was wondering if there is another way of adding my 'admin' user to the adminstrators group.

#194055

Jun 12, 2018 10:09

Vishal Panday

Vote:

ok i managed to make my admin user part of the administrators group. (by adding the administrators group).

But i still get a login failed in the manager.

#194064

Jun 12, 2018 15:21

Quan Mai

Vote:

What error message are you getting?

#194065

Jun 12, 2018 15:24

Vishal Panday

Vote:

Well nothing spectacular: 'Login failed. Please try again.'

If you could also check my previous post where i added my web.config(CMS site) where i show my roleprovider and membership. I believe everything is correct there right. Thats the way it was and i didnt touch it.

#194066

Jun 12, 2018 15:34

Quan Mai

Vote:

Try with email address instead... Does the Membership and role configuration in CM look the same as in CMS site?

#194067

Jun 12, 2018 15:42

Quan Mai

Vote:

Try with email address instead... Does the Membership and role configuration in CM look the same as in CMS site?

#194068

Jun 12, 2018 15:42

Vishal Panday

Vote:

No the membership and rol configuration of the CM site looks like this:

Totally different. So which one is it using? The one from CMS site or the one from Commerce site?

 <membership defaultProvider="SqlServerMembershipProvider" hashAlgorithmType="HMACSHA512">
      <providers>
        <add name="SqlServerMembershipProvider" type="Mediachase.Commerce.Customers.Profile.Providers.CustomerSqlMembershipProvider, Mediachase.Commerce" connectionStringName="EcfSqlConnection" requiresQuestionAndAnswer="false" applicationName="ECApplication" requiresUniqueEmail="true" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" passwordStrengthRegularExpression="" />
        <add connectionStringName="EcfSqlConnection" applicationName="ECApplication" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="true" passwordFormat="Hashed" passwordStrengthRegularExpression="" minRequiredPasswordLength="4" minRequiredNonalphanumericCharacters="0" name="CMSMembershipProvider" type="Mediachase.Commerce.Customers.Profile.Providers.CustomerSqlMembershipProvider, Mediachase.Commerce" />
      </providers>
    </membership>
    <profile enabled="true" defaultProvider="CMSProfileProvider" inherits="Mediachase.Commerce.Customers.Profile.CustomerProfile, Mediachase.Commerce">
      <providers>
        <add name="CMSProfileProvider" type="Mediachase.Commerce.Customers.Profile.Providers.CustomerSqlProfileProvider, Mediachase.Commerce" connectionStringName="EcfSqlConnection" />
      </providers>
      <properties />
    </profile>
    <roleManager enabled="true" cacheRolesInCookie="true" defaultProvider="SqlServerRoleProvider" cookieName="ECF.ASPXROLES" cookiePath="/" cookieTimeout="30" cookieRequireSSL="false" cookieSlidingExpiration="true" createPersistentCookie="false" cookieProtection="All">
      <providers>
        <add name="SqlServerRoleProvider" connectionStringName="EcfSqlConnection" applicationName="ECApplication" type="Mediachase.Commerce.Customers.Profile.Providers.CustomerSqlRoleProvider, Mediachase.Commerce" />
      </providers>
    </roleManager>

#194069

Jun 12, 2018 15:45

Quan Mai

Vote:

They should be the same. otherwise how could you share the members and logins ?

#194070

Jun 12, 2018 15:48

Vishal Panday

Vote:

OK i'm really getting confused now. :-)

The CMS site web.config looks like this:

<profile defaultProvider="DefaultProfileProvider">      
      <providers>
        <add name="DefaultProfileProvider" type="System.Web.Providers.DefaultProfileProvider, System.Web.Providers, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="EPiServerDB" applicationName="/" />
      </providers>
    </profile>
    <membership defaultProvider="MultiplexingMembershipProvider" userIsOnlineTimeWindow="10" hashAlgorithmType="HMACSHA512">
      <providers>
        <clear />
        <add name="MultiplexingMembershipProvider" type="EPiServer.Security.MultiplexingMembershipProvider, EPiServer.Framework.AspNet" provider1="SqlServerMembershipProvider" provider2="WindowsMembershipProvider" />
        <add name="WindowsMembershipProvider" type="EPiServer.Security.WindowsMembershipProvider, EPiServer.Cms.AspNet" deletePrefix="BUILTIN\" searchByEmail="true" />
        <add name="SqlServerMembershipProvider" type="System.Web.Providers.DefaultMembershipProvider, System.Web.Providers, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="EPiServerDB" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" applicationName="/" />
      </providers>
    </membership>
    <roleManager enabled="true" defaultProvider="MultiplexingRoleProvider" cacheRolesInCookie="true">
      <providers>
        <clear />
        <add name="MultiplexingRoleProvider" type="EPiServer.Security.MultiplexingRoleProvider, EPiServer.Framework.AspNet" provider1="SqlServerRoleProvider" provider2="WindowsRoleProvider" providerMap1="SqlServerMembershipProvider" providerMap2="WindowsMembershipProvider" />
        <add name="WindowsRoleProvider" applicationName="/" type="EPiServer.Security.WindowsRoleProvider, EPiServer.Cms.AspNet" />
        <add name="SqlServerRoleProvider" type="System.Web.Providers.DefaultRoleProvider, System.Web.Providers, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="EPiServerDB" applicationName="/" />
      </providers>
    </roleManager>
    <anonymousIdentification enabled="true" />

And you said that it should be the same in the CommerceManager site web.config. So i changed what is was (see previous post) to exactly the same as above.

But still no login using the admin account.

If i leave the CommerceManager web.config on the CMSProfileProvider as it was... Then the default admin/store account works, but that is different account then my admin account from the cms. Is the CMSProfileProvider getting stuff from the commerce database instead of the CMS database?

#194078

Jun 13, 2018 8:12

Quan Mai

Vote:

My memory is rusty but I think it's the way around. You should have the configuration like in CM

#194080

Jun 13, 2018 8:52

Vishal Panday

Vote:

i did the following used the default admin/store account and changed the password and recreated the other accounts, because those were in the other sql membershipprovider i guess.

 <profile defaultProvider="CMSProfileProvider">      
      <providers>
        <add name="DefaultProfileProvider" type="System.Web.Providers.DefaultProfileProvider, System.Web.Providers, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="EPiServerDB" applicationName="/" />
        <add name="SqlProfileProvider" type="System.Web.Profile.SqlProfileProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="EcfSqlConnection" />
        <add name="CMSProfileProvider" type="Mediachase.Commerce.Customers.Profile.Providers.CustomerSqlProfileProvider, Mediachase.Commerce" connectionStringName="EcfSqlConnection" />
      </providers>
    </profile>
    <membership defaultProvider="MultiplexingMembershipProvider" userIsOnlineTimeWindow="10" hashAlgorithmType="HMACSHA512">
      <providers>
        <clear />
        <add name="MultiplexingMembershipProvider" type="EPiServer.Security.MultiplexingMembershipProvider, EPiServer.Framework.AspNet" provider1="SqlServerMembershipProvider" provider2="WindowsMembershipProvider" />
        <add name="WindowsMembershipProvider" type="EPiServer.Security.WindowsMembershipProvider, EPiServer.Cms.AspNet" deletePrefix="BUILTIN\" searchByEmail="true" />
        <!--<add name="SqlServerMembershipProvider" type="System.Web.Providers.DefaultMembershipProvider, System.Web.Providers, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="EPiServerDB" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" applicationName="/" />-->
        <add name="SqlServerMembershipProvider" type="Mediachase.Commerce.Customers.Profile.Providers.CustomerSqlMembershipProvider, Mediachase.Commerce" connectionStringName="EcfSqlConnection" requiresQuestionAndAnswer="false" applicationName="ECApplication" requiresUniqueEmail="true" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" passwordStrengthRegularExpression="" />
        <add connectionStringName="EcfSqlConnection" applicationName="ECApplication" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="true" passwordFormat="Hashed" passwordStrengthRegularExpression="" minRequiredPasswordLength="4" minRequiredNonalphanumericCharacters="0" name="CMSMembershipProvider" type="Mediachase.Commerce.Customers.Profile.Providers.CustomerSqlMembershipProvider, Mediachase.Commerce" />
      </providers>
    </membership>
    <roleManager enabled="true" defaultProvider="MultiplexingRoleProvider" cacheRolesInCookie="true">
      <providers>
        <clear />
        <add name="MultiplexingRoleProvider" type="EPiServer.Security.MultiplexingRoleProvider, EPiServer.Framework.AspNet" provider1="SqlServerRoleProvider" provider2="WindowsRoleProvider" providerMap1="SqlServerMembershipProvider" providerMap2="WindowsMembershipProvider" />
        <add name="WindowsRoleProvider" applicationName="/" type="EPiServer.Security.WindowsRoleProvider, EPiServer.Cms.AspNet" />
        <!--<add name="SqlServerRoleProvider" type="System.Web.Providers.DefaultRoleProvider, System.Web.Providers, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="EPiServerDB" applicationName="/" />-->
        <add name="SqlServerRoleProvider" connectionStringName="EcfSqlConnection" applicationName="ECApplication" type="Mediachase.Commerce.Customers.Profile.Providers.CustomerSqlRoleProvider, Mediachase.Commerce" />
        <add connectionStringName="EcfSqlConnection" applicationName="ECApplication" name="CMSRoleProvider" type="Mediachase.Commerce.Customers.Profile.Providers.CustomerSqlRoleProvider, Mediachase.Commerce" />
      </providers>
    </roleManager>

#194090

Jun 13, 2018 14:01

Eric

Vote:

Hi, when does episerver commerce support the createdatabaseschema="true" in web.config the same say as CMS? At the moment we need to run the initialize-epidatabase command to fix the problem. And this will run the sql scripts. Woiuld be nice if that flag could support creating an empty commerce db as well.

#196811

Sep 12, 2018 9:52

Quan Mai

Vote:

We will look into it, can't promise but if it is a fairly simple fix then we will do it soon

#196813

Sep 12, 2018 10:32

Eric

Vote:

Great, and thanks in advance :)

#196819

Sep 12, 2018 15:27

Quan Mai

Vote:

I looked into it, and it is tricky to get right. Given the priority and resources available, we decided to leave it for now. Will come back when we have more time and/or become smarter

#198164

Oct 23, 2018 9:19