none
Are external data source execute query strings truncated?

    Question

  • I'm getting failures when attempting to run U-SQL scripts that send long query strings to an external SQL Server data source via SELECT * FROM EXTERNAL <data source name> EXECUTE <long query string>.

    Seems like SQL Server receives a malformed query string even though the same query works when run via SQL Server Management Studio.

    When I shorten the query string the query succeeds so it seems like Data Lake Analytics truncates the string before sending it. I think the strings are being truncated to around 1000 characters.

    Note that I am submitting the query from an Azure Data Factory project in Visual Studio with Azure SDK, Azure Data Factory SDK, and Azure Data Lake SDK installed.

    Is this by design? I would expect Data Lake Analytics to reject the query if it is too long for it to handle rather than sending a truncated version to the external data source.

    Monday, May 15, 2017 5:43 PM

Answers

  • This seems similar to a known issue for which fix is on the way. If that is the case (not sure since no error message provided), the workaround until the fix is deployed is to surround the query string with brackets, e.g.

    SELECT * FROM EXTERNAL <data source name> 
    EXECUTE @"(<original_query_string>)" 

    • Marked as answer by AlanCStone Thursday, May 18, 2017 4:27 PM
    Thursday, May 18, 2017 10:46 AM

All replies

  • This seems similar to a known issue for which fix is on the way. If that is the case (not sure since no error message provided), the workaround until the fix is deployed is to surround the query string with brackets, e.g.

    SELECT * FROM EXTERNAL <data source name> 
    EXECUTE @"(<original_query_string>)" 

    • Marked as answer by AlanCStone Thursday, May 18, 2017 4:27 PM
    Thursday, May 18, 2017 10:46 AM
  • The added parentheses fixed the issue. Thank you!

    I'm going to add the errors (3 are listed by the VS ADF tool) here for the curious:

    SYSTEM - E_CQO_SYSTEM_INTERNAL_ERROR

    CQO: Internal Error - Optimizer internal error.  Assert: csExtRowsetInfoNew != nullptr in managed\scopecqo.cpp:2235

    Internal optimizer error.

    USER - E_CSC_USER_DATASOURCECONNECTIONFAILURE

    Failed to connect to data source.

    Failed to connect to data source: '<data source name>', with error(s): 'Incorrect syntax near the keyword 'SELECT'.
    Incorrect syntax near the keyword 'AS'.'


    USER - W_CSC_USER_EXTERNALSTATISTICSNOTFOUND

    Failed to retrieve statistics from the data source.

    at token 'EXTERNAL', line 16
    near the ###:
    **************
    rtTimeString string = @startTime.ToString("yyyy-MM-dd");
    DECLARE @endTimeString string = @endTime.ToString("yyyy-MM-dd");

    @activeLocks =
        SELECT *
        FROM  ### EXTERNAL <snipped>

    Thursday, May 18, 2017 4:34 PM