2

I have a nightly file that pulls all susbscriber bounces through an automation that recently began adding carriage returns to it and won't be fixed until the next release. Support gave me the steps for a work around below, but I need help with the SQL code. I have VERY limited SQL skills but below is what I have. Syntax checking says I have the below errors. Please help!

  • An unmatched parenthesis occurs in the query.
  • Error while parsing query. Please check the syntax
SELECT CLIENTID, SENDID, SUBSCRIBERKEY, EMAIL, LISTID, EVENTDATE, EVENTTYPE, BOUNCECATEGORY, SMTPCODE, BOUNCEREASON, BATCHID, TRIGGEREDSENDCUSTOMERKEY, PROMOID
FROM EEMSBouncesDaily_New 

REPLACE(REPLACE(CLIENTID,CHAR(10),'_'),CHAR(13),'_')) CLIENTID AND 
REPLACE(REPLACE(SENDID,CHAR(10),'_'),CHAR(13),'_')) SENDID AND
REPLACE(REPLACE(SUBSCRIBERKEY,CHAR(10),'_'),CHAR(13),'_')) SUBSCRIBERKEY AND
REPLACE(REPLACE(EMAIL,CHAR(10),'_'),CHAR(13),'_')) EMAIL AND
REPLACE(REPLACE(LISTID,CHAR(10),'_'),CHAR(13),'_')) LISTID AND
REPLACE(REPLACE(EVENTDATE,CHAR(10),'_'),CHAR(13),'_')) EVENTDATE AND
REPLACE(REPLACE(EVENTTYPE,CHAR(10),'_'),CHAR(13),'_')) EVENTTYPE AND
REPLACE(REPLACE(BOUNCECATEGORY,CHAR(10),'_'),CHAR(13),'_')) BOUNCECATEGORY AND
REPLACE(REPLACE(SMTPCODE,CHAR(10),'_'),CHAR(13),'_')) SMTPCODE AND
REPLACE(REPLACE(BOUNCEREASON,CHAR(10),'_'),CHAR(13),'_')) BOUNCEREASON AND
REPLACE(REPLACE(BATCHID,CHAR(10),'_'),CHAR(13),'_')) BATCHID AND
REPLACE(REPLACE(TRIGGEREDSENDCUSTOMERKEY,CHAR(10),'_'),CHAR(13),'_')) TRIGGEREDSENDCUSTOMERKEY AND
REPLACE(REPLACE(PROMOID,CHAR(10),'_'),CHAR(13),'_')) PROMOID

SFMC DIRECTIONS As stated on the phone you will need to create a query that queries the data extension and removes the carriage returns from the data. I have provided an overview of the steps below.

  1. You will need to create a data extension to transfer the data from the current data extension being targeted by the Data Extract

  2. Create a Query that retrieves the information from the Sendlog Data Extension and removes carriage returns within the Data, storing this information within the Data Extension created in step one.

You will need to create a query that selects the data from the Data Extension in question, which contains the statement:

REPLACE(REPLACE(*NameOfColumn*,CHAR(10),'_'),CHAR(13),'_')) *NameOfColumn* 

This will replace all carriage returns in that field. This will then populate a resulting data extension in which you will be able to setup your current data extract to target. Using this method, you will ensure that carriage returns are not within the data that you are extracting. If you need assistance in creating the query, I would recommend visiting our Developer site salesforce.stackexchange.com, where you may post questions to our Developers directly at no cost to you. Please let me know if I can be of further assistance. Have a wonderful day!

  1. You will need to change the targeted data extension within the Data Extract activity from the original data extension to the DE created in Step 1. This will cause the data extract to retrieve the corrected data instead of the original data extension which contains carriage returns.

  2. You will need to create a automation that runs these steps in order.

The Automation Steps should follow this order:

Step 1. Query Activity to remove carriage returns 
Step 2. Data Extract Activity 
Step 3. File Transfer Activity 

This should correct your data issues and ensure that you receive the proper information.

  • What's your question? – Adam Spriggs Jul 1 '16 at 14:08
  • You generally should not have this much trouble importing data. SFMC has special file transfer options to handle line-termination scenarios. – Adam Spriggs Jul 1 '16 at 14:10
  • I am trying to extract a file of bounced subscribers which has been running fine for some time until a SFMC update occurred and caused carriage returns in the data. SFMC support gave me the above work around but they didn't provide me much individualized support on how to build the SQL code. I need the query to look up the data extension that stores the data (EEMSBouncesDaily_New) and replace the field data without the carriage return into a new data extension that this query would feed before the automation would transfer the new data extension to my ftp. – Jennifer Jul 1 '16 at 16:35
1
REPLACE(REPLACE(*NameOfColumn*,CHAR(10),'_'),CHAR(13),'_')) *NameOfColumn* 

In the above form and in every line of your code, the above has an unmatched parens. There's an extra parens at the end of the above line.

I don't know anything about SFMC, but you appear to have an extra parentheses in each line of your code (the very last one). I recommend you try removing it to see if it fixes your issue.

EDIT:

My "gut" is telling me to suggest trying something like this:

REPLACE((REPLACE(CLIENTID,CHAR(10),'_'),CHAR(13),'_') CLIENTID AND 
(REPLACE(SENDID,CHAR(10),'_'),CHAR(13),'_') SENDID AND
(REPLACE(SUBSCRIBERKEY,CHAR(10),'_'),CHAR(13),'_') SUBSCRIBERKEY AND
(REPLACE(EMAIL,CHAR(10),'_'),CHAR(13),'_') EMAIL AND
(REPLACE(LISTID,CHAR(10),'_'),CHAR(13),'_') LISTID AND
(REPLACE(EVENTDATE,CHAR(10),'_'),CHAR(13),'_') EVENTDATE AND
(REPLACE(EVENTTYPE,CHAR(10),'_'),CHAR(13),'_') EVENTTYPE AND
(REPLACE(BOUNCECATEGORY,CHAR(10),'_'),CHAR(13),'_') BOUNCECATEGORY AND
(REPLACE(SMTPCODE,CHAR(10),'_'),CHAR(13),'_') SMTPCODE AND
(REPLACE(BOUNCEREASON,CHAR(10),'_'),CHAR(13),'_') BOUNCEREASON AND
(REPLACE(BATCHID,CHAR(10),'_'),CHAR(13),'_') BATCHID AND
(REPLACE(TRIGGEREDSENDCUSTOMERKEY,CHAR(10),'_'),CHAR(13),'_') TRIGGEREDSENDCUSTOMERKEY AND
(REPLACE(PROMOID,CHAR(10),'_'),CHAR(13),'_') PROMOID) 

The alternative being to also try removing the fieldname after the last parens (or even doing both the above and removing it after). For some reason, that seems unnecessary to me, but again, I'm not familiar with their code and haven't seen anything you may not have included in your post. It seems odd there would be nested REPLACE statements like that followed with the fieldname, first with options and then later without with more ANDs tacked on. It seems like there's potential to only need one outer replace statement. Its at least worth trying.

  • Thanks crmprogdev, removing the extra parenthesis removed one of my errors. Now I only have one error left- Error while parsing query. Please check the syntax – Jennifer Jul 1 '16 at 16:32
  • See my edit to see if it gets you any further down the road. Good luck to you. – crmprogdev Jul 1 '16 at 17:23
0

I realize this question is a few months old but I just came across a similar issue and noticed the solution was incomplete.

Here's how to remove carriage returns from what I believe to be relevant fields Using the "_Job" data view as an example (not all fields will have carriage returns e.g. jobid as it's an integer)

SELECT
JobID,
EmailID,
AccountID,
AccountUserID,
FromName,
FromEmail,
SchedTime,
PickupTime,
DeliveredTime,
EventID,
IsMultipart,
JobType,
JobStatus,
ModifiedBy,
ModifiedDate,
EmailName,
CAST(REPLACE(REPLACE(CAST(EmailSubject AS nvarchar(MAX)),CHAR(10),'_'),CHAR(13),'_') as ntext) AS EmailSubject,
IsWrapped,
TestEmailAddr,
Category,
BccEmail,
OriginalSchedTime,
CreatedDate,
CharacterSet,
IPAddress,
SalesForceTotalSubscriberCount,
SalesForceErrorSubscriberCount,
SendType,
CAST(REPLACE(REPLACE(CAST(DynamicEmailSubject AS nvarchar(MAX)),CHAR(10),'_'),CHAR(13),'_') as ntext) AS DynamicEmailSubject,
SuppressTracking,
SendClassificationType,
SendClassification,
ResolveLinksWithCurrentData,
DeduplicateByEmail,
TriggererSendDefinitionObjectID,
TriggeredSendCustomerKey
FROM _Job

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.