Summary:

In CRM 7.1 when attempting to send an Emarketing email blast using a CRM group that is sorted by a particular column the email blast will fail.

Symptoms:

You will see an error message on screen:

E-Mail blast creation failed
GET failed on http://localhost:15009/crm71j/$service/getEmarketingRecipients?waveItemID=48&groupID=186&emailField=Pers_EmailAddress&pageNumber=1&recipientsPerPage=1000: status=500

The spsyncdefault.log shows:

bad SQL grammar [SELECT TOP 1001 addr_addressid,comp_companyid,addr_country,pers_lastname,comp_name,pers_firstname,pers_personid,pers_phonefullnumber,pers_emailaddress,pers_secterr FROM vsearchlistperson WITH (NOLOCK) WHERE (comp_name LIKE N'%3g%' ESCAPE '|') AND Pers_PersonId IS NOT NULL and (Capt_Family = 'addr_country' or addr_country is NULL) ORDER BY Pers_PersonId]; nested exception is java.sql.SQLException: Invalid column name 'Capt_Family'.</sdata:message>
<sdata:stackTrace>org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT TOP 1001 addr_addressid,comp_companyid,addr_country,pers_lastname,comp_name,pers_firstname,pers_personid,pers_phonefullnumber,pers_emailaddress,pers_secterr FROM vsearchlistperson WITH (NOLOCK) WHERE (comp_name LIKE N'%3g%' ESCAPE '|') AND Pers_PersonId IS NOT NULL and (Capt_Family = 'addr_country' or addr_country is NULL) ORDER BY Pers_PersonId]; nested exception is java.sql.SQLException: Invalid column name 'Capt_Family'.
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:233)

Cause:

When a group is ordered it puts a Capt_family expression in the where clause.  You will need to remove this because the custom_captions table is not joined in the query.

Resolution:

Fixed in 7.2d

Workaround:

Remove any fields from the "sort on" section of the group and the email blast will send as expected.