Skip to main content

Errors When Validating SQL and Creating Datasets

Updated

The following are the error prompts and descriptions you may encounter when creating a dataset and validating the data.

SQL.jpg

Error Prompt Description
"Login Failed For User"

This indicates that the User ID, Password, and other information you entered for connecting to SQL Agent integration may be incorrect. Please check.
"Can Not Open Database"

This indicates that the Database Type, Database Name, and other information you entered for connecting to SQL Agent integration may be incorrect. Please check.
"No Connection Could Be Made"

This indicates that the SQL Agent integration may not have connected successfully. Please check. If you need to reconnect, see SQL Agent Integration Setup.
"Validate SQL Time Out" This indicates that there is an issue with your SQL. Please optimize your SQL and resubmit.

"The Server Was Not Found"

This indicates that the server information you entered for connecting to SQL Agent integration may be incorrect. Please check.

"ORDER BY items must appear in the select list if SELECT DISTINCT is specified"

When encountering the error, modify your SQL as follows:

  1. Add an ORDER BY clause to the query statement.
  2. Execute the query as a subquery using the following format:
    SELECT * FROM (query order by A ) AS alias

The following is a SQL example that may result in the error:

SELECT DISTINCT ta.userid,
ta.FILED1,
ta.FILED2
FROM tableA ta 
LEFT JOIN tableB tb 
ON ta.userid = tb.UserName;

The modified version is as follows:

SELECT * FROM (
SELECT DISTINCT ta.userid,
ta.FILED1,
ta.FILED2
FROM tableA ta
LEFT JOIN tableB tb 
ON ta.userid = tb.UserName
ORDER BY ta.userid
) AS alias;

 

"Incorrect syntax near the keyword 'ORDER'. Invalid usage of the option NEXT in the FETCH statement."

When encountering the current error, modify your query to a subquery using the following syntax:

SELECT * FROM (query) AS alias

 

The system will continue to display these error messages on the set sync schedule until the root cause is fixed and the sync is finally successful.