Random sql error in one store. Error Number:102,State:1,Class:15

[gnossosp]

Random error with one (of four) uses of store on an installation. DNN v. 09.07.02 (0) OpenStore 4.1.2;
Clearing DNN Cache and re starting application seems to stop issue. Issue appears at random on different machines and browsers (windows, tablets, linux, phones) and not at same time. One can view site OK and other can not. Thoughts?

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '{'. Incorrect syntax near the keyword 'and'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, String commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(String connectionString, String spName, Object[] parameterValues) at Nevoweb.DNN.NBrightBuy.Components.SqlDataProvider.SqlDataProvider.GetList(Int32 portalId, Int32 moduleId, String typeCode, String sqlSearchFilter, String sqlOrderBy, Int32 returnLimit, Int32 pageNumber, Int32 pageSize, Int32 recordCount, String typeCodeLang, String lang) at Nevoweb.DNN.NBrightBuy.Components.NBrightBuyController.GetList(Int32 portalId, Int32 moduleId, String typeCode, String sqlSearchFilter, String sqlOrderBy, Int32 returnLimit, Int32 pageNumber, Int32 pageSize, Int32 recordCount, String typeCodeLang, String lang) at Nevoweb.DNN.NBrightBuy.Components.NBrightBuyController.GetDataList(Int32 portalId, Int32 moduleId, String entityTypeCode, String entityTypeCodeLang, String cultureCode, String strFilters, String strOrderBy, Boolean debugMode, String selUserId, Int32 returnLimit, Int32 pageNumber, Int32 pageSize, Int32 recordCount) at Nevoweb.DNN.NBrightBuy.Components.Products.ProductFunctions.ProductAjaxViewList(NBrightInfo ajaxInfo, String pagemid, String page, Boolean firstRender) ClientConnectionId:7cd0fbed-a547-4346-b03c-8dcff1038e0f Error Number:102,State:1,Class:15

[DNNMonster]

Are you using a custom template that modifies the sql statements?

[gnossosp]

thanks for responding. No customization whatsoever, except I have linked optional text in the Categories item.

[DNNMonster]

Is it portal specific?

[gnossosp]

Yes. There are four portals using the store and this is the only one exhibiting issues. At first I believed the owner was at fault due to the use of an underscore character in the file names.

[gnossosp]

Oh no. It is happening with other portals now. Clearing the cache and re starting server/application corrects the error.

[DNNMonster]

Any additional error info?

[gnossosp]

Only thing that is consistent - from admin log- Message:Incorrect syntax near '{'. Incorrect syntax near the keyword 'and'.

[gnossosp]

I have also run the "Store Validation" command from tools. It doesn't seem to complete.

[DNNMonster]

Make sure your SQL server is healthy and responsive. It's possible you're bumping into resource issues. You should get a nice wonderfully green message after the validation completes. If you don't then you should check the logs to see if there is an error.

..

[DNNMonster]

This looks like the installation needs to have SQL profiler running in an acceptable environment so that one can expose the query that is getting executed and then we can get to root. If that is possible on your side then I'm pretty sure that's the right path forward. Others can chime in if they have some better insights.

[gnossosp]

Not sure if I can. I don't have ownership rights to sql server.

I noticed that there are a large number of files in the NBSTemp directory... I think there were over 19,000. I've removed these files and re started store. Seems like a lot. Is that supposed to be or is there some mechanism that cleans up the temp directory?

[DNNMonster]

Is the store in debug mode?

[gnossosp]

No.

[DNNMonster]

Is the Store Validation process returning the green success message?

[gnossosp]

I've run it and it times out. I've increased the script timeout to 120000 seconds and it is currently running...

[gnossosp]

After running Store Validation, I was logged out and did not see the completed message, however it did not appear to throw any errors.

[gnossosp]

Validation (finally) completed successfully.

[DNNMonster]

I am not sure what is happening. It may be more than one thing. If you repeat the Store Validation process it may "catch up" and clean up possible db issues. It doesn't really solve the cause though. We may have had an issue with orphans in a previous versions and this may be affecting you but I'm throwing spaghetti at the wall now. I hate sounding like a broken record but upgrading would be a wise move. If there was a bug in an earlier version it could get corrected by the upgrade.

[gnossosp]

Ah- I was not paying attention to upgrades. I thought I was good, but am on 4.1.2

I'll put in 4.1.7 and let you know if there are any issues. Thanks again.

[gnossosp]

Issue with upgrade to 4.1.7. Would it be OK if I start a new thread?

[DNNMonster]

Makes sense.