Dynamics V9 Search for an entity field give a generic SQL error when lookup text is added

(0) Share

Report

Posted on by Paraya

I have migrated my 8.2 bd to 9 on premises, browsing the web to check all is working well, i found an error, when trying to put a contact, or parent account the search failed when a put some on the field, whitout text the search works.

eneric SQL error. ---> System.Data.SqlClient.SqlException: Incorrect syntax near ')'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 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 Microsoft.Crm.CrmDbConnection.InternalExecuteWithRetry[TResult](Func`1 ExecuteMethod, IDbCommand command)
at Microsoft.Crm.CrmDbConnection.<>c__DisplayClass104_0.<InternalExecuteReader>b__0()
at Microsoft.PowerApps.CoreFramework.ActivityLoggerExtensions.Execute[TResult](ILogger logger, EventI

*This post is locked for comments

I have the same question (0)

All responses (17)

Answers (0)

Sreevalli 3,256 on at

Like (0)

Report

Hi Paraya,

Where are you trying to search?

Was this reply helpful? Yes No
Paraya on at

Like (0)

Report

lookup for parent account or contact in account form

Was this reply helpful? Yes No
Sreevalli 3,256 on at

Like (0)

Report

Hi,

Do you have any Business rule or JS on customer field?

Was this reply helpful? Yes No
Paraya on at

Like (0)

Report

Account form has no scripts, or business rules.

Was this reply helpful? Yes No
Suggested answer

Sreevalli 3,256 on at

Like (0)

Report

Hi,

If you have recently upgraded to 9.0 and you believe that the issue is after upgrade then I suggest you to raise MS ticket, since there are some V9.0 known issues with search functionalities

Was this reply helpful? Yes No
Paraya on at

Like (0)

Report

Thank you, can you tell who raise the ticket please.

have a nice day

Was this reply helpful? Yes No
Suggested answer

Sreevalli 3,256 on at

Like (0)

Report

Hi!

Go to Office 365 Admin Center.

Click on Support -> New Service Request

community.dynamics.com/.../how-to-create-a-support-request-for-dynamics-365-issue-from-office-365-admin-portal

Was this reply helpful? Yes No
Paraya on at

Like (0)

Report

Finally get it working

The Generic Error was because, no index created for full-text search.

Thanks

Was this reply helpful? Yes No
Suggested answer

Sreevalli 3,256 on at

Like (0)

Report

Great, Happy CRMing!!

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Paraya, when you say it works, I assume you mean that you do not get the SQL error any more. But does the functionality of the full text search actually work? For example, if you create a contact with a last name with a space like "Van Horne", and then search for "Horne", will it find the record? We also ran into this problem when we upgraded from V8.2 to V9 and have not resolved it.

Was this reply helpful? Yes No