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

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

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.

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

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.