Kentico 12 staging applciation syntax error

jeff McDaniel asked on March 25, 2019 18:14

Hello,

I am attempting to stage some pages on Kentico 12. They are a custom page type we have created and the page type exists on both the source and destination server. It seems Kentico's vanilla staging application is having a ton of trouble pushing these pages up.

It is failing on synchronization and this is the error. Anyone have any ideas of how to troubleshoot this?

SyncServer.ServerError: Exception occurred: [DataConnection.HandleError]: Query: SELECT * FROM Evolution_Stories WHERE IN ( SELECT [DocumentForeignKeyValue] FROM CMS_Document WHERE [DocumentNodeID] = @DocumentNodeID AND [DocumentCulture] = @DocumentCulture ) Caused exception: Incorrect syntax near the keyword 'IN'.

Thanks for any help/suggestions!

Correct Answer

jeff McDaniel answered on March 25, 2019 22:15

Hey Brenden,

I couldn't find anything so I rolled back my DB to a copy of what I took earlier today. Something got corrupted and went very very wrong. I couldn't find anything in the recycle bin for that page type, so I figured it would be easier to just roll back my changes and start over.

As for the fix, I was able to just create the page type on the destination with the same fields, then pushing the pages worked. It was a bit of manual work but saved a ton of time since no matter what happened, Kentico would not be able to push that page type up.

1 votesVote for this answer Unmark Correct answer

Recent Answers

jeff McDaniel answered on March 25, 2019 18:18 (last edited on March 25, 2019 18:26)

I've tried re-signing macros, deleting/resyncing the page type, and importing the page type.

Interesting enough, if I delete the page type from the destination and try to re-sync it, I get this error.

SyncServer.ServerError: Exception occurred: [DataConnection.HandleError]: Query: ALTER TABLE [Evolution_Stories] DROP CONSTRAINT [PK_Evolution_Stories]; ALTER TABLE [Evolution_Stories] ADD CONSTRAINT [PK_Evolution_Stories] PRIMARY KEY ([StoriesID],[NodeID]) Caused exception: Column name 'NodeID' does not exist in the target table or view. Could not create constraint or index. See previous errors.

However, if I export/import the page type it imports correctly and all fields are there.

If I try to import the entire site, this error happens when trying to import those Stories Page type

SELECT TOP 1 *
FROM View_CMS_Tree_Joined AS V WITH (NOLOCK, NOEXPAND) INNER JOIN Evolution_Stories AS C WITH (NOLOCK) ON [V].[DocumentForeignKeyValue] = [C] AND V.ClassName = N'Evolution.Stories' LEFT OUTER JOIN COM_SKU AS S WITH (NOLOCK) ON [V].[NodeSKUID] = [S].[SKUID]
WHERE [NodeSiteID] = @NodeSiteID AND ([NodeAliasPath] = @NodeAliasPath AND [DocumentCulture] = @DocumentCulture)

Caused exception: 
Invalid column name 'C'.

0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on March 25, 2019 19:16

Sounds like you have a corrupt database. I'd suggest deleting that custom page type and then looking for any remnants of it in your database manually. What I've seen in the past is there is some sort of copy of that class created in the cms_class table and a table with a similar name created. There could also be some version history stored with this old orphaned name as well.

1 votesVote for this answer Mark as a Correct answer

jeff McDaniel answered on March 25, 2019 19:37

Hey Brenden,

Thanks for the suggestion. I deleted the version history and the pages from the db. However, If I try to delete the custom page type I get the message The selected item cannot be deleted because it's used by other records. You need to remove it from the related records first.

It does not have any details under it for certain pages. Does this mean I should delete it from CMS_Class as well?

0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on March 25, 2019 19:54

It means there are records in the recycle bin holding the page type captive. Go to the recycle bin app and view and destroy them there.

1 votesVote for this answer Mark as a Correct answer