Forums (Obsolete)

Member

kevin.clark-gaprc - 3/28/2011 12:24:11 PM

Kentico backend system SQL error.

Whenever I try to run a certain user control in debug mode I get the following SQL error. It appears the subquery has a misplaced "DESC" in it.

[DataConnection.ExecuteQuery]: Query: DELETE FROM [CMS_EventLog] WHERE ([EventID] NOT IN (SELECT TOP [EventID] DESC [EventID] FROM [CMS_EventLog] WHERE SiteID = 1 ORDER BY [EventID] DESC)) AND (SiteID = 1): caused exception: Incorrect syntax near 'EventID'.


Kentico Consulting	kentico_mirekr - 3/28/2011 1:17:08 PM RE:Kentico backend system SQL error. Hi, We are sorry for this inconvenience. However, this bug was already fixed in the latest hotfix for 5.5 version - devnet.kentico.com/Bugtracker/Hotfixes.aspx. Could you please download and apply the hotfix? Best regards, Miro Remias.


Member	kevin.clark-gaprc - 3/28/2011 1:51:51 PM RE:Kentico backend system SQL error. We recently applied 5.5 R2, so do I need to get hotfix 5.5R2. 15 to fix this problem?

Kentico Consulting

kentico_mirekr - 3/28/2011 3:05:18 PM

RE:Kentico backend system SQL error.

Hi,

Are you sure that you have upgraded the web site from 5.5 to 5.5R2 correctly according to upgrade instructions?

If so, you should end up with following query text in CMS_Query table when running following SQL command:

SQL command:

SELECT [QueryText] FROM [CMS_Query] where QueryName like 'deleteolderlogs'

Result of QueryText:

DELETE FROM [CMS_EventLog] WHERE ([EventID] NOT IN (SELECT ##TOPN## [EventID] FROM [CMS_EventLog] WITH (NOLOCK) WHERE ##WHERE## ORDER BY ##ORDERBY##)) AND (##WHERE##)

Could you please confirm that there is such query text in this table in your case? If not, please update the record with correct query text.

Thank you.
Best regards,
Miro Remias.


Member	kevin.clark-gaprc - 3/29/2011 9:58:06 AM RE:Kentico backend system SQL error. reapplied the 5.5R2 SQL and my last error has gone, but now i'm getting the error listed below when running in debug mode Adding the specified count to the semaphore would cause it to exceed its maximum count.


Member	kevin.clark-gaprc - 3/29/2011 3:03:20 PM RE:Kentico backend system SQL error. I reapplied 5.5R2 and my errors have gone away! Thanks!


Kentico Consulting	kentico_mirekr - 4/11/2011 8:31:13 AM RE:Kentico backend system SQL error. Hi, You haven’t posted the exception that you were getting, but hopefully everything is working just fine for you now. If not, please let me know. Best regards, Miro Remias.

Member

Webcoda - 3/21/2012 6:35:41 AM

RE:Kentico backend system SQL error.

Hi,

I just started getting the same error every time I try to login to CMSDESK.
This started to happen out of the blue, no code or config changes.

We are running 4.4R2 and have recently upgraded to the latest hotfiix 44.

-----------------------------------------

This is what I get when select 'deleteolderlogs' query from dbo.CMS_Query

DELETE FROM [CMS_EventLog] WHERE ([EventID] NOT IN (SELECT TOP ##ORDERBY## [EventID] FROM [CMS_EventLog] WHERE ##WHERE## ORDER BY [EventID] DESC)) AND (##WHERE##)

-----------------------------------------

An unhandled exception occurred and the process was terminated.

Application ID: /LM/W3SVC/23/ROOT

Process ID: 1380

Exception: System.Exception

Message: [DataConnection.ExecuteQuery]: Query: DELETE FROM [CMS_EventLog] WHERE ([EventID] NOT IN (SELECT TOP [EventID] DESC [EventID] FROM [CMS_EventLog] WHERE SiteID = 1 ORDER BY [EventID] DESC)) AND (SiteID = 1): caused exception: Incorrect syntax near 'EventID'.

StackTrace: at CMS.DataProviderSQL.DataConnection.ExecuteQuery(String queryText, Object[,] queryParams, QueryTypeEnum queryType)
at CMS.DataEngine.GeneralConnection.RunQuery(QueryParameters query)
at CMS.DataEngine.GeneralConnection.ExecuteQuery(QueryParameters query)
at CMS.DataEngine.GeneralConnection.ExecuteQuery(String queryName, Object[,] parameters, String where, String orderBy, Int32 topN)
at CMS.EventLog.EventLogCleaner.Run()
at CMS.GlobalHelper.CMSThread.Run()
at System.Threading.ExecutionContext.runTryCode(Object userData)
at System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode code, CleanupCode backoutCode, Object userData)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

--------------------------------

Thank you for your help.
Oleg


Kentico Support	kentico_jurajo - 3/21/2012 6:52:06 AM RE:Kentico backend system SQL error. Hi, Could you please clarify the version number? There was no 4.4R2 release :-) I guess you meant 5.5R2, right? Could you please post the exact version including the build number? It is strange since this issue was fixed in version 5.5 in hotfix 26 and I have never seen it after the hotfix. Best regards, Juraj Ondrus


Member	Webcoda - 3/21/2012 7:13:10 AM RE:Kentico backend system SQL error. Yes, sorry about the typo, it is 5.5 build 44. We have upgraded it last week. The strangest thing is that this started happening today. I have a feeling something may be not right with the site e.g. some files / scripts are out of sync. I have re-run the SQL hotfix but it did not fix the problem. Thanks, Oleg


Kentico Support	kentico_jurajo - 3/24/2012 10:29:26 PM RE:Kentico backend system SQL error. Hi, Re-running the script can make things even worse :-) If you have some recent backups, I would recommend to restore them and re-apply the whole hotfix again. Best regards, Juraj Ondrus