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]
Improve this question
5
  • 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
    – 0xsegfault
    Commented Jul 20, 2017 at 15:43
  • Try taking the primary key restraint out of the target de to test it
    – 0xsegfault
    Commented Jul 20, 2017 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
    Commented Jul 20, 2017 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
    Commented Jul 20, 2017 at 16:34
  • @Data_Kid its a record id and it will be unique for all
    – Manoj Chandran
    Commented Jul 21, 2017 at 5:32

1 Answer 1

Reset to default
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?

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

You must log in to answer this question.

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