Hey guys,

I'm having an issue with creating an account on our Production database through the out-of-the-box iMIS create account page. The iMIS database was upgraded from version 10.6 a few months ago (we're now on 15.1.2). (It might also be worth noting that the same error happens when we use iBO to create a customer record)

This functionality works fine on my test databases that were originally created from the Demo DB - the error only happens on our production database.

Here is the error we're seeing: Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'AS'.

I have troubleshooted the issue to find what SQL code is being executed. Here is the SQL that is failing:

SELECT AS DUP_MATCH_KEY

As you can see, the syntax is incorrect - it seems to be missing some important information in the query.

I also ran a SQL trace on a working database to see what should be in that SQL statement. Here is what it shows:

SELECT UPPER(dbo.fn_asi_convert_foreign_string(SUBSTRING('M6K3L9',1,5) + (CASE WHEN 'Schwartz'<>'' THEN (SUBSTRING('Schwartz',1,4) + SUBSTRING('James',1,1)) ELSE SUBSTRING('imason inc.',1,5) END))) AS DUP_MATCH_KEY

Any help/suggestions to troubleshoot this would be greatly appreciated.

Thanks!
James

Here is the full error:

Incorrect syntax near the keyword 'AS'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'AS'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Incorrect syntax near the keyword 'AS'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1950890
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846875
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
System.Data.SqlClient.SqlDataReader.get_MetaData() +83
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +70
Asi.iBO.DataServer.ExecuteReader(CommandType commandType, String sql, SqlParameter[] commandParameters) +475
Asi.iBO.DataServer.ExecuteReader(CommandType commandType, String sql) +47
Asi.iBO.ContactManagement.CContact.SetDupMatchKey(DataServer server) +3455
Asi.iBO.ContactManagement.CContact.Validate(DataServer server) +220
Asi.iBO.ContactManagement.CContact.Validate() +119
Asi.Bae.iBOWrapper_Net.AccountIboWrapper.CreateNewAccount() +465

[Exception: Error in CreateNewAccount: ]
Asi.Bae.iBOWrapper_Net.AccountIboWrapper.CreateNewAccount() +682
Asi.Bae.Business.Customer.AccountPending.Register(String imisConnectionString) +2321
Asi.Bae.Web.CreateAccountModule.addaccountinfo.Page_Load(Object sender, EventArgs e) +2355
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35
System.Web.UI.Control.OnLoad(EventArgs e) +99
Asi.ContentManagerNet.DisplayPageBase.OnLoad(EventArgs e) +49
Asi.Web.UI.DisplayPageBase.OnLoad(EventArgs e) +45
System.Web.UI.Control.LoadRecursive() +50
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +627

Version Information: Microsoft .NET Framework Version:2.0.50727.3603; ASP.NET Version:2.0.50727.3082