2

I have set up a data extension to capture a query and return it's results. In order to track the query, I am running it in automation studio. However, the query keeps failing. I have checked that the fields are consistent with those of the query, but still it fails.

Here is a copy of the query:

SELECT
SubscriberKey
, EmailAddress
, FirstName
, Username
, B.bet_market_name
, B.bet_market_selection
FROM [Email Audience - Marketing] E
JOIN (

    SELECT customer_id
    , bet_market_name
    , bet_market_selection

    FROM ( 

        SELECT customer_id
        , bet_market_name
        , bet_market_selection
        , ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY BT.bet_id ASC) AS rowNum
        FROM BetTransaction BT
        JOIN (

            SELECT 
                bet_market_selection
                , bet_id
                , bet_market_name
            FROM BetSelection BS
            WHERE amount >= 5 
            AND win_price >= 1.5
            AND bet_category_name = 'Football (Premier League)'

        ) BS
        ON BT.bet_id = BS.bet_id
        WHERE multi = 1
    ) as B
    WHERE rowNum = 1 
) as B 
ON E.customer_id = B.customer_id

SCHEMA FOR QUERY

7
  • Do you get an error message?
    – Ray Dehler
    Aug 9, 2016 at 18:15
  • Please post the schema for the target data extension -- including what fields are non-nullable and ones marked as primary keys. Aug 9, 2016 at 19:28
  • Why are you re-using the B alias? Aug 9, 2016 at 19:46
  • @AdamSpriggs I have added this
    – 0xsegfault
    Aug 9, 2016 at 22:15
  • @RayDehler That's the problem with marketing cloud..it does not give errors when it fails
    – 0xsegfault
    Aug 9, 2016 at 22:16

2 Answers 2

4

Typically if Query Activities fail, it's one of these 6 things:

  1. Primary key violation -- your query results in duplicate rows not allowed by the primary key
  2. Inserting a null value into a non-nullable field
  3. Inserting a value too long for the field (truncation)
  4. Timeout -- if your query doesn't complete within the 30-minute timeout window, it'll error out.
  5. Your target Data Extension no longer exists
  6. Data type conversion -- trying to insert a $12.34 string into a Decimal field

SFMC Support can tell you what the error is from the server log.

1
  • Just to ad one more case here for future reference: If you convert dates using "At Time Zone" the query validation will accept anything in the timezone string, but any typo (or extra space) will result in "Automation failed due to system error" Jun 25, 2020 at 11:19
2

Unfortunately at this time the only method to debug failing queries is to open a support case and advise support of which Automation the Query Activity is used in. Support can then check the logs to see what the error is.

FYI, in the near future Automation Studio will show you the actual error message for failed Activities in an Automation (yay!) — refer to screenshot below (I took this picture at a CNX16 presentation).

In the meantime, you can either:

  1. Open a support case and get Salesforce to check the error log
  2. Export your data from the Data Extensions and create tables locally on SQL Server or the free Express Edition, then run your query (this is what we typically do)
  3. Build a schema on SQL Fiddle and run your query there (ensure that you select MS SQL Server 2008 as this is what Marketing Cloud uses).

automation studio preview

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .