Anyone knows what is the cause of the error in my Stuff SQL? i run the query many times and keep receiving this error. This query will get all the ProductCode based on every customer and i want to display in into data extension.

enter image description here

Here's my query:


ProductCode = STUFF(
SELECT ',' + pcode.[ProductCode]

Asset_Salesforce as asset

LEFT JOIN Product2_Salesforce as pcode
ON pcode.Id =asset.Product2Id

LEFT JOIN Contact_Salesforce as con
ON asset.ContactId = con.Id

asset.ContactId = con.Id
pcode.Id = asset.Product2Id

), 1, 1, '')
FROM [Asset_Salesforce] as asset2

LEFT JOIN [Customer Data Layer_v2] as b2
ON b2.AccountId = asset2.AccountId
WHERE b2.AccountId != ''

GROUP BY b2.ContactId, asset2.Id
  • The timeout error's fairly self-explanatory. You're running a very expensive query and it's resulting in a timeout after 30 mins. You'd need to do some tuning. If you're not wanting to know about products with no asset or assets with no contact, why do an outer join? If you don't need Account_Salesforce fields in your output, why join to it? You have the account Id already available in your Contact_Salesforce data. If you're grouping by ContactID and AssetId, what's the point of using a DISTINCT?
    – Macca
    Apr 3, 2020 at 0:51
  • Try breaking your query down into multiple less expensive queries. For example, dump your contacts with assets into a staging table, then try doing your expensive STUFF and XML PATH operation in separate queries using smaller subsets of data.
    – Macca
    Apr 3, 2020 at 0:51
  • 1
    Consider the Primary Keys on your staging DE's. You can force the creation of indexes to help with performance.
    – Macca
    Apr 3, 2020 at 0:56

1 Answer 1


The reason for your timeout error is you are running an expensive query that is timing out after 30 minutes. Salesforce has a useful page on optimisation of query activities. Many of the points I think you'll find useful in tuning the query above, most notably the use of intermediary/staging tables and joining on Primary Key fields. Also consider the need for expensive operations like DISTINCT and limiting result sets by using INNER, rather than OUTER joins.

Without having your data to hand or knowing too much about the business rules you're looking to apply, it's a little hard to write hypothetical SQL, here, but here's an example of breaking down your automation.

1. Overwrite ContactsWithProducts (PK = ContactId,ProductCode)

    a.ContactId AS ContactId,
    p2.Id AS ProductCode    
    Product2_Salesforce p2 INNER JOIN 
    Asset_Salesforce a
        ON  p2.Id = a.Product2Id

2. Overwrite DataLayerContacts (No PK)

    c.Id AS ContactId,
    Contact_Salesforce c INNER JOIN 
    [Customer Data Layer_v2] cdl
        ON  c.AccountId = cdl.AccountId

3. Overwrite MyResultsTable (No PK)

        SELECT  ',' + cwp.ProductCode
        FROM    ContactsWithProducts cwp
        WHERE   cwp.ContactId = dlc.ContactId
        FOR XML PATH('')
    ,1,1,'') AS ProductCodes
    DataLayerContacts dlc
  • Hi @Macca thank you for the detailed explanation. i wanted to simplify my query and limit the result by 2 productCode per Contact. can you help me ?
    – JGB
    Apr 3, 2020 at 4:34
  • the only goal is i need to merge the ProductCode in each rows pero ContactId
    – JGB
    Apr 3, 2020 at 4:36
  • So, you eventually want a table containing two fields - "ContactId" and "ProductCodes" (comma delimited list)?
    – Macca
    Apr 3, 2020 at 5:03

You must log in to answer this question.

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