Cannot add new field to the SKU table

Yehuda Lando asked on February 24, 2015 18:27

Hi,

I'm trying to add a new field to the SKU table, but I get the following error: [FieldEditor.SaveSelectedField()]: Refreshing views related to this table failed. Please see Event log for more details.

The error in the error log is:

Message: [ TableManager.UpdateDocumentViews() ] An error occurred. Original exception: 
[DataConnection.HandleError]: 

Query: 
CREATE VIEW View_CONTENT_MenuItem_Joined AS 
SELECT View_CMS_Tree_Joined.*, CONTENT_MenuItem.* FROM View_CMS_Tree_Joined INNER JOIN CONTENT_MenuItem ON View_CMS_Tree_Joined.DocumentForeignKeyValue = CONTENT_MenuItem.[] WHERE (ClassName = 'CMS.MenuItem')

Caused exception: 
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

I use v8.1.17

Recent Answers

Tracey Sangster answered on February 24, 2015 20:16

Hi Yehunda,

What is the information of the field that you are trying to create? And what actions are you performing when you encounter this message? Is this when you are using the E commerce module in Admin and you try to save your work?

Tracey

0 votesVote for this answer Mark as a Correct answer

Yehuda Lando answered on February 24, 2015 20:30

It's just a normal text field with all the default settings. The field name is AlternativeParticipantsForm, but I also tried other names and got the same error. About the actions, I just go to the Modules application, and edit the SKU class under the E-commerce module. I did it before successfully, I'm not sure why it's happening now.

0 votesVote for this answer Mark as a Correct answer

Yehuda Lando answered on February 24, 2015 21:07

I'll have to check it more, but under a site running v8.1.14 it doesn't happen

0 votesVote for this answer Mark as a Correct answer

Tracey Sangster answered on February 24, 2015 21:41

Is everything else on the site functioning properly?

0 votesVote for this answer Mark as a Correct answer

Yehuda Lando answered on February 24, 2015 21:45

Yea

0 votesVote for this answer Mark as a Correct answer

Petar Kozjak answered on February 25, 2015 12:17 (last edited on February 25, 2015 12:18)

Hi,

if you want to add new column to SKU table go to Modules > E-commerce > SKU > Fields and add new field. This will add a custom column to COM_SKU table and you will see it in Kentico. This way you can extend any Kentico table. This will also update all views.

Hope this is what you are looking for.

0 votesVote for this answer Mark as a Correct answer

Tracey Sangster answered on February 25, 2015 14:39

Hi Petar,

I had her confirm the steps that she had taken, and it appears that she is doing this correctly (she also states that she has succeeded with the addition in an alternate install). I am thinking maybe a refresh of the macros signature. What do you think?

cheers, Tracey

0 votesVote for this answer Mark as a Correct answer

Yehuda Lando answered on February 25, 2015 17:32

I did exactly that Petar, and that is when I got the error. Thanks Tracey, I'll try refreshing the macros.

0 votesVote for this answer Mark as a Correct answer

Petar Kozjak answered on February 25, 2015 23:54

Hm... only option is to hotfix to latest version. There could be problem with views.

0 votesVote for this answer Mark as a Correct answer

Yehuda Lando answered on February 26, 2015 16:53

I just did hotfix to the latest version. I think that's when it started.

0 votesVote for this answer Mark as a Correct answer

Yehuda Lando answered on February 26, 2015 16:58

After it happens, and I try to delete the field, I get this error:

[DataConnection.ValidateTransaction]: Transaction is no longer usable. Query execution failed during this transaction which is not permitted. Original error: 
[DataConnection.HandleError]: 

Query: 
CREATE VIEW View_CMS_Tree_Joined AS 
SELECT CASE WHEN (DocumentIsArchived IS NULL OR DocumentIsArchived = 0) AND ((DocumentCheckedOutVersionHistoryID IS NULL AND DocumentPublishedVersionHistoryID IS NULL) OR (DocumentCheckedOutVersionHistoryID IS NOT NULL AND DocumentPublishedVersionHistoryID IS NOT NULL)) AND (DocumentPublishFrom IS NULL OR (DocumentPublishFrom IS NOT NULL AND DocumentPublishFrom <= getdate())) AND (DocumentPublishTo IS NULL OR (DocumentPublishTo IS NOT NULL AND DocumentPublishTo >= getdate())) THEN 1 ELSE 0 END AS Published, V.*, SKU.[SKUID], SKU.[SKUNumber], SKU.[SKUName], SKU.[SKUDescription], SKU.[SKUPrice], SKU.[SKUEnabled], SKU.[SKUDepartmentID], SKU.[SKUManufacturerID], SKU.[SKUInternalStatusID], SKU.[SKUPublicStatusID], SKU.[SKUSupplierID], SKU.[SKUAvailableInDays], SKU.[SKUGUID], SKU.[SKUImagePath], SKU.[SKUWeight], SKU.[SKUWidth], SKU.[SKUDepth], SKU.[SKUHeight], SKU.[SKUAvailableItems], SKU.[SKUSellOnlyAvailable], SKU.[SKUCustomData], SKU.[SKUOptionCategoryID], SKU.[SKUOrder], SKU.[SKULastModified], SKU.[SKUCreated], SKU.[SKUSiteID], SKU.[SKUPrivateDonation], SKU.[SKUNeedsShipping], SKU.[SKUMaxDownloads], SKU.[SKUValidUntil], SKU.[SKUProductType], SKU.[SKUMaxItemsInOrder], SKU.[SKUMaxPrice], SKU.[SKUValidity], SKU.[SKUValidFor], SKU.[SKUMinPrice], SKU.[SKUMembershipGUID], SKU.[SKUConversionName], SKU.[SKUConversionValue], SKU.[SKUBundleInventoryType], SKU.[SKUMinItemsInOrder], SKU.[SKURetailPrice], SKU.[SKUParentSKUID], SKU.[SKUAllowAllVariants], SKU.[SKUInheritsTaxClasses], SKU.[SKUInheritsDiscounts], SKU.[SKUShortDescription], SKU.[SKUEproductFilesCount], SKU.[SKUBundleItemsCount], SKU.[SKUInStoreFrom], SKU.[SKUReorderAt], SKU.[SKUTrackInventory], SKU.[TicketAmount], SKU.[SellUntil], SKU.[SellFrom], SKU.[AlternativeParticipantsForm], U.FullName AS NodeOwnerFullName, U.UserName AS NodeOwnerUserName, U.Email AS NodeOwnerEmail 
FROM View_CMS_Tree_Joined_Regular V  WITH (NOEXPAND) LEFT OUTER JOIN dbo.View_COM_SKU AS SKU ON V.NodeSKUID = SKU.SKUID LEFT OUTER JOIN dbo.CMS_User U ON V.NodeOwner = U.UserID
UNION ALL
SELECT CASE WHEN (DocumentIsArchived IS NULL OR DocumentIsArchived = 0) AND ((DocumentCheckedOutVersionHistoryID IS NULL AND DocumentPublishedVersionHistoryID IS NULL) OR (DocumentCheckedOutVersionHistoryID IS NOT NULL AND DocumentPublishedVersionHistoryID IS NOT NULL)) AND (DocumentPublishFrom IS NULL OR (DocumentPublishFrom IS NOT NULL AND DocumentPublishFrom <= getdate())) AND (DocumentPublishTo IS NULL OR (DocumentPublishTo IS NOT NULL AND DocumentPublishTo >= getdate())) THEN 1 ELSE 0 END AS Published, V.*, SKU.[SKUID], SKU.[SKUNumber], SKU.[SKUName], SKU.[SKUDescription], SKU.[SKUPrice], SKU.[SKUEnabled], SKU.[SKUDepartmentID], SKU.[SKUManufacturerID], SKU.[SKUInternalStatusID], SKU.[SKUPublicStatusID], SKU.[SKUSupplierID], SKU.[SKUAvailableInDays], SKU.[SKUGUID], SKU.[SKUImagePath], SKU.[SKUWeight], SKU.[SKUWidth], SKU.[SKUDepth], SKU.[SKUHeight], SKU.[SKUAvailableItems], SKU.[SKUSellOnlyAvailable], SKU.[SKUCustomData], SKU.[SKUOptionCategoryID], SKU.[SKUOrder], SKU.[SKULastModified], SKU.[SKUCreated], SKU.[SKUSiteID], SKU.[SKUPrivateDonation], SKU.[SKUNeedsShipping], SKU.[SKUMaxDownloads], SKU.[SKUValidUntil], SKU.[SKUProductType], SKU.[SKUMaxItemsInOrder], SKU.[SKUMaxPrice], SKU.[SKUValidity], SKU.[SKUValidFor], SKU.[SKUMinPrice], SKU.[SKUMembershipGUID], SKU.[SKUConversionName], SKU.[SKUConversionValue], SKU.[SKUBundleInventoryType], SKU.[SKUMinItemsInOrder], SKU.[SKURetailPrice], SKU.[SKUParentSKUID], SKU.[SKUAllowAllVariants], SKU.[SKUInheritsTaxClasses], SKU.[SKUInheritsDiscounts], SKU.[SKUShortDescription], SKU.[SKUEproductFilesCount], SKU.[SKUBundleItemsCount], SKU.[SKUInStoreFrom], SKU.[SKUReorderAt], SKU.[SKUTrackInventory], SKU.[TicketAmount], SKU.[SellUntil], SKU.[SellFrom], SKU.[AlternativeParticipantsForm], U.FullName AS NodeOwnerFullName, U.UserName AS NodeOwnerUserName, U.Email AS NodeOwnerEmail 
FROM View_CMS_Tree_Joined_Linked V  WITH (NOEXPAND) LEFT OUTER JOIN dbo.View_COM_SKU AS SKU ON V.NodeSKUID = SKU.SKUID LEFT OUTER JOIN dbo.CMS_User U ON V.NodeOwner = U.UserID

Caused exception: 
Invalid column name 'AlternativeParticipantsForm'.
Invalid column name 'AlternativeParticipantsForm'.
0 votesVote for this answer Mark as a Correct answer

Yehuda Lando answered on February 26, 2015 17:35

I just tried to upgrade to 8.2 and got this message again:

CREATE VIEW View_CONTENT_MenuItem_Joined AS 
SELECT View_CMS_Tree_Joined.*, CONTENT_MenuItem.* FROM View_CMS_Tree_Joined INNER JOIN CONTENT_MenuItem ON View_CMS_Tree_Joined.DocumentForeignKeyValue = CONTENT_MenuItem.[] WHERE (ClassName = 'CMS.MenuItem')
0 votesVote for this answer Mark as a Correct answer

Yehuda Lando answered on February 26, 2015 17:38

Where does the CONTENT_MenuItem.[] come from?

0 votesVote for this answer Mark as a Correct answer

Martin Danko answered on February 26, 2015 23:50

Hello Yehuda,

At this point, I would recommend you to restore the backups before applying the hotfix and verify if you are able to do that in non-hotfixed version.

Regards, Martin

1 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.