Query Activity Failing

Question

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

Answer 1

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.

Answer 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).