If you’re copying a query into Query Studio from Automation Studio (and you know it works), you’re probably frustrated at the random red error messages. But, don’t abandon Query Studio yet!
Query Studio is a great development and auditing tool because you don’t need a data extension for each query. This awesome benefit is also the cause of some common error messages. Below are some of the common error messages, and tips on how to avoid them.
ERROR MESSAGE: Something went wrong!
Solution: Always start with a re-fresh, re-paste.
Sounds simple, and it is. It’s the Query Studio equivalent of, “Did you turn it off and back on again?” I’d even try twice before debugging further.
ERROR MESSAGE: Automation failed due to system error.
Solution: Keep it short!
In general, think of Query Studio as a quick way to test certain *parts* of a larger query you’re working on in Automation Studio.
For instance, I might strip an Automation Studio query to its simplest select, join, and where clauses, and test only the “date conversion” criteria that I’m trouble shooting. The benefit is two-fold -- I’m helping Query Studio run faster with fewer errors, and I’m isolating the problem.
Here are some tips:
- Avoid sub queries if possible. Simple subqueries will run! (time out error seems to happen more with subqueries)
- Keep joins to a minimum. (system error message)
- Explicitly define fields in your select statement (no select * allowed error)
Solution: Use proper syntax!
The biggie here is to avoid using single quotation marks to name fields in a select statement.
This won’t work: Select Count(EmailAddress) as 'User Count'
This will work: Select Count(EmailAddress) as UserCount
Also, avoid spaces in your field and data extension names, whenever possible. Brackets can be used in a pinch for fields and data extensions with spaces.
ERROR MESSAGE: Unable to create temporary data extension. Error: options.uri is a required argument.
Solution: Brackets to save the day?
Options.URI error will sometimes occur when you can’t see a single other syntax error. For some reason, this query will occasionally fail with an error.
Before: Select Salesforce_Id from MyData
After a lot of refreshing and a bit of frustrated remarks, I added brackets. Even though there isn’t a space in my field name, this still helped!
After: Select [Salesforce_Id] from MyData
Boom, query starts!
When an unexpected error message pops up, these simple tips can help you avoid some frustration, and give you back more time in your day.