1

I am trying to pull the data from multiple data extensions to another DE and i am using union to achieve it.Also I have subscriber key as a primary key in the target data extension.However i get system error at times and don't get any error messages.And this error is not constant it occurs sometimes and not all times.All three tables contains different data no duplicates. Below is my sql query

SELECT [uid] As [Subscriber Key],
   [Email] As [Email Address],[Email Opt Out],
   CASE WHEN [Email Opt Out] = 0 THEN 'useroptedin'
        ELSE 'useroptedout'  END AS Status
FROM [sfcontacts]  

Union

SELECT 
   [uid] As [Subscriber Key],[Person Account: Email] As [Email Address],[Person Account: Email Opt Out],
   CASE WHEN [Person Account: Email Opt Out] = 0 THEN 'useroptedin'
        ELSE 'useroptedout' END AS Status
FROM [sfcontacts1] 

Union

SELECT 
   [uid] As [Subscriber Key],[Person Account: Email] As [Email Address],[Person Account: Email Opt Out],
   CASE WHEN [Person Account: Email Opt Out] = 0 THEN 'useroptedin' 
        ELSE 'useroptedout' END AS Status
FROM [sfcontacts2]
  • 1
    How are you generating the UUID? It's a "random" generator you might have a primary key violation when the same uid is added to the DE – Samuel Dare Jul 20 '17 at 15:43
  • Try taking the primary key restraint out of the target de to test it – Samuel Dare Jul 20 '17 at 15:49
  • You have different fields in your sfcontacts than in 1 and 2 - [Email Opt Out] vs. [Person Account: Email Opt Out]. Perhaps make 1 & 2 use [Person Account: Email Opt Out] As [Email Opt Out]? This should retain more consistency as it is now all in same field – Gortonington Jul 20 '17 at 16:33
  • 1
    also - put the Query in an automation, Automation Studio can give more insight into the errors when you hover over it in Activity tab. – Gortonington Jul 20 '17 at 16:34
  • @Data_Kid its a record id and it will be unique for all – Manoj Chandran Jul 21 '17 at 5:32
1

Most of the time I come across this nondescript system error occurring intermittently, it's usually a result of contention with other queries running in this or other Automations.

Try isolating any other Automations that may have Query Activities reading from your sfcontact tables or writing to your target data extension and time these queries not to coincide with this Automation's schedule.

Tuning this query may also help a little to reduce its time to execute and the risk it will collide with other Automations. If UUID is unique across all three tables, "UNION ALL" will reduce the work SQL Server is having to do by sorting etc to get the UNIQUE part of what a plain "UNION" involves.

A second possibility to investigate is data casting/conversion issues. Do the data types of all your source Data Extensions and your target data extension match? Could you have values in any of these that could not be cast to the target datatype safely?

  • Also check for fields that might be non-nullable while your unions potentially have a null field. – Tom Callahan Feb 17 at 6:48

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.