Summary:

Mail merges cannot be run for groups where more than one key attribute field has been added to the group search criteria. The same behaviour is seen for static or dynamic groups.

Symptoms:

An error message is displayed onscreen:

Mail Merge failed. Please contact your System Administrator.

The folllowing error is written to the scrmSQL.log:

ERROR: 02-Jan-2014 16:43:26.420 8. Statement.executeQuery(SELECT pers_salutation,pers_firstname,pers_lastname,comp_name FROM vpersonlist WITH (NOLOCK)   INNER JOIN vDynamicData Vcfo2 ON Pers_PersonId = Vcfo2.DData_EntityID AND Vcfo2.DData_Entity = 13 AND Vcfo2.DData_FieldID=56 AND (( COALESCE(Vcfo2.DData_ShortStr, '') = N'1' ) ) AND Vcfo2.DData_Order=Vcfo1.DData_Order AND Vcfo2.CatID=Vcfo1.CatID WHERE Pers_PersonId IS NOT NULL ORDER BY Pers_PersonId Asc) SELECT pers_salutation,pers_firstname,pers_lastname,comp_name FROM vpersonlist WITH (NOLOCK)   INNER JOIN vDynamicData Vcfo2 ON Pers_PersonId = Vcfo2.DData_EntityID AND Vcfo2.DData_Entity = 13 AND Vcfo2.DData_FieldID=56 AND (( COALESCE(Vcfo2.DData_ShortStr, '') = N'1' ) ) AND Vcfo2.DData_Order=Vcfo1.DData_Order AND Vcfo2.CatID=Vcfo1.CatID WHERE Pers_PersonId IS NOT NULL ORDER BY Pers_PersonId Asc
java.sql.SQLException: The multi-part identifier "Vcfo1.DData_Order" could not be bound.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1304)
at net.sf.log4jdbc.StatementSpy.executeQuery(StatementSpy.java:661)
at com.mchange.v2.c3p0.impl.NewProxyStatement.executeQuery(NewProxyStatement.java:35)
at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:440)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:395)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:455)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:463)
at com.sage.scrm.model.db.ReadQuery.execute(ReadQuery.java:361)
at com.sage.scrm.model.db.ReadQuery.execute(ReadQuery.java:295)
at com.sage.scrm.model.db.ReadQuery.execute(ReadQuery.java:309)
at com.sage.scrm.model.store.MailMergeStore.buildAndExecuteQuery(MailMergeStore.java:418)
at com.sage.scrm.model.store.MailMergeStore.getMailMergeData(MailMergeStore.java:528)
at com.sage.scrm.model.store.MailMergeStore.processMergeRequest(MailMergeStore.java:196)
at com.sage.scrm.model.store.MailMergeStore.getData(MailMergeStore.java:114)
at com.sage.scrm.controller.MailMergeController.requestAction(MailMergeController.java:121)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.springframework.web.servlet.mvc.multiaction.MultiActionController.invokeNamedMethod(MultiActionController.java:471)
at org.springframework.web.servlet.mvc.multiaction.MultiActionController.handleRequestInternal(MultiActionController.java:408)
at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:790)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:560)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.sage.scrm.controller.util.AbstractSessionRequestFilter.doFilter(AbstractSessionRequestFilter.java:127)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.sage.scrm.scrmcommons.controller.util.EncodingRequestFilter.doFilter(EncodingRequestFilter.java:81)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at com.googlecode.psiprobe.Tomcat60AgentValve.invoke(Tomcat60AgentValve.java:30)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Unknown Source)

Mail merges did not run at all for any number of key attribute fields when a similar test was carried out against v7.2. In that version, the following was written out to the crm.log:

ERROR [AsyncThread-1]: 02-Jan-2014 16:31:02.262 4. PreparedStatement.executeQuery() SELECT pers_salutation, pers_firstname, pers_lastname, comp_name FROM vpersonlist WITH (NOLOCK) INNER JOIN vDynamicData Vcfo1 WITH (NOLOCK) ON 45 AND (( COALESCE(Vcfo1.DData_ShortStr, '')=N'1' ) ) INNER JOIN vDynamicData Vcfo2 WITH (NOLOCK) ON Vcfo1.DData_Order AND Vcfo2.CatID=Vcfo1.CatID WHERE Pers_PersonId IS NOT NULL ORDER BY Pers_PersonId ASC
java.sql.SQLException: An expression of non-boolean type specified in a context where a condition is expected, near 'AND'.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:418)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:693)
at net.sf.log4jdbc.PreparedStatementSpy.executeQuery(PreparedStatementSpy.java:733)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:644)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:637)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:662)
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:142)
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:148)
at com.sage.crm.core.data.jdbc.JdbcRead.execute(JdbcRead.java:63)
at com.sage.crm.core.dao.ReadQuery.execute(ReadQuery.java:68)
at com.sage.crm.web.async.task.MailMergeTask.getData(MailMergeTask.java:613)
at com.sage.crm.web.async.task.MailMergeTask.generateJsonFileData(MailMergeTask.java:364)
at com.sage.crm.web.async.task.MailMergeTask.execute(MailMergeTask.java:150)
at com.sage.crm.web.async.TaskProcessor.process(TaskProcessor.java:80)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.springframework.expression.spel.support.ReflectiveMethodExecutor.execute(ReflectiveMethodExecutor.java:69)
at org.springframework.expression.spel.ast.MethodReference.getValueInternal(MethodReference.java:109)
at org.springframework.expression.spel.ast.CompoundExpression.getValueInternal(CompoundExpression.java:57)
at org.springframework.expression.spel.ast.SpelNodeImpl.getTypedValue(SpelNodeImpl.java:102)
at org.springframework.expression.spel.standard.SpelExpression.getValue(SpelExpression.java:102)
at org.springframework.integration.util.AbstractExpressionEvaluator.evaluateExpression(AbstractExpressionEvaluator.java:126)
at org.springframework.integration.util.MessagingMethodInvokerHelper.processInternal(MessagingMethodInvokerHelper.java:225)
at org.springframework.integration.util.MessagingMethodInvokerHelper.process(MessagingMethodInvokerHelper.java:125)
at org.springframework.integration.handler.MethodInvokingMessageProcessor.processMessage(MethodInvokingMessageProcessor.java:73)
at org.springframework.integration.handler.ServiceActivatingHandler.handleRequestMessage(ServiceActivatingHandler.java:64)
at org.springframework.integration.handler.AbstractReplyProducingMessageHandler.handleMessageInternal(AbstractReplyProducingMessageHandler.java:97)
at org.springframework.integration.handler.AbstractMessageHandler.handleMessage(AbstractMessageHandler.java:73)
at org.springframework.integration.endpoint.PollingConsumer.doPoll(PollingConsumer.java:70)
at org.springframework.integration.endpoint.AbstractPollingEndpoint$1.call(AbstractPollingEndpoint.java:146)
at org.springframework.integration.endpoint.AbstractPollingEndpoint$1.call(AbstractPollingEndpoint.java:144)
at org.springframework.integration.endpoint.AbstractPollingEndpoint$Poller$1.run(AbstractPollingEndpoint.java:207)
at org.springframework.integration.util.ErrorHandlingTaskExecutor$1.run(ErrorHandlingTaskExecutor.java:52)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)

Workaround / Resolution:

Mail merges will work where the group search criteria only has a single key attribute field. The error only occurs on multiple key attribute fields. It may be possible to work around this behaviour by editing the Search SQL data in the group summary screen. This has been raised to development for a fix.