Visions In Code

Hello guys

I have been lucky to work with Sitecore Commerce (8.2.1 Update-2), what I really like is that it’s so integrated with Sitecore. And next version(will be released in the beginning of next year) will be even better 🙂

But In this post I would like to share with you how to avoid an annoying error I’ve been struggling with for a while. (I got the error when I was registering/creating a commerce user, I got it both in the Sitecore User Manager and in code).

Here is the annoying error:

Exception from HRESULT: 0x80040E14

Description: An unhandled exception occurred. 

Exception Details: System.Runtime.InteropServices.COMException: Exception from HRESULT: 0x80040E14

Source Error: 

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace: 


[COMException (0x80040e14): Exception from HRESULT: 0x80040E14]
   CommerceServer.Core.Interop.Profiles.ProfileServiceClass.GetProfileByKey(String bstrKeyMemberName, Object sValue, String bstrType, Boolean bReturnError) +0
   CommerceServer.Core.Runtime.Profiles.Profile..ctor(ProfileContext profileService, String keyName, String keyValue, String profileType) +98

[CommerceProfileSystemException: Failed to retrieve profile.]
   CommerceServer.Core.Runtime.Profiles.Profile..ctor(ProfileContext profileService, String keyName, String keyValue, String profileType) +373
   CommerceServer.Core.Runtime.Profiles.ProfileContext.GetProfile(String keyName, String keyValue, String profileType) +451
   Sitecore.Commerce.Connect.CommerceServer.Profiles.Pipelines.GetProfileProcessor.Process(GetProfileArgs args) +99

And after blood, sweat, and tears I finally succeeded to find a solution. It turns out that the error occurs when the columns in the UserObject table(in your PREFIXNAME_profiles database) does not match with your commerce server profile system (in your Commerce Server manager):

In my case, it was missing fields in the UserObject table:
u_external_id
u_comment
u_preferred_shipping_method
u_default_shopper_list

So add your missing fields, like this:

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'userobject' AND COLUMN_NAME = 'u_external_id')
BEGIN
 ALTER TABLE dbo.UserObject ADD u_external_id nvarchar(256) NOT NULL
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'userobject' AND COLUMN_NAME = 'u_preferred_shipping_method')
BEGIN
 ALTER TABLE dbo.UserObject ADD u_preferred_shipping_method nvarchar(50) NULL
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'userobject' AND COLUMN_NAME = 'u_default_shopper_list')
BEGIN
 ALTER TABLE dbo.UserObject ADD u_default_shopper_list nvarchar(50)  NULL
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'userobject' AND COLUMN_NAME = 'u_comment')
BEGIN
 ALTER TABLE dbo.UserObject ADD u_comment nvarchar(256) NULL
END
GO

Don’t forget to index u_external_id:

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[UserObject]') AND name = N'IX_UserObject_ExternalId')
 DROP INDEX [IX_UserObject_ExternalId] ON [dbo].[UserObject]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[UserObject]') AND name = N'IX_UserObject_ExternalId')
CREATE UNIQUE NONCLUSTERED INDEX [IX_UserObject_ExternalId] ON [dbo].[UserObject]
(
 [u_external_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Finally, the error is gone and we can continue our journey with Sitecore Commerce.

That’s all for now folks 🙂