Forum Discussion

Ariunbold_Chimeddorj's avatar
Ariunbold_Chimeddorj
Copper Contributor
Jan 05, 2021

Sql server 2019 polybase: 100001;Failed to generate query plan.

Hello all, I have enabled the polybase on my sql server. When i try to create external data source, below error throws. What is the cause of this error? Error: 100001;Failed to generate query plan...
  • Ariunbold_Chimeddorj's avatar
    Ariunbold_Chimeddorj
    Copper Contributor
    Feb 03, 2021
    Yes, it is enabled on the server. I can query external tables but external table contains column with decimal type, it throws this error. Workaround is cast the decimal columns when execute the select statement.

    Ex: select cast(a as float) from external_table;
  • stephanesql's avatar
    stephanesql
    Copper Contributor
    Jan 25, 2021

    Hi

    I got the same error when creating the external data source while trying Polybase on my laptop.

    The external data source is a local PostgreSQL 13 (running on WSL).

    I installed the Postgresql ODBC driver and using a DSN named PostgreSQL35W_ro (tested ok, and I could connect it with for example Power BI in ODBC). I have no problem to connect using Azure Data Studio also.

     

    I'm using the following statement (after creating a key in the database and credentials):

     

    CREATE EXTERNAL DATA SOURCE my_postgresql_source
WITH (
LOCATION = 'odbc://localhost:5432',
CONNECTION_OPTIONS = 'DSN=PostgreSQL35W_ro',
CREDENTIAL = postgresql_creds
);

    I tried also : 

    CREATE EXTERNAL DATA SOURCE my_postgresql_source WITH
    (
        LOCATION = 'odbc://localhost:5432',
        CONNECTION_OPTIONS = 'Driver={PostgreSQL Unicode(x64)}; Database=mabase',
        CREDENTIAL = postgresql_creds,
        PUSHDOWN = ON
    );

    It gives the same error.

    My SQL Server version is Developper Edition Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64) running  on Windows 10.

     

    It there something wrong in my configuration ?

    Thanks.

    • Ariunbold_Chimeddorj's avatar
      Ariunbold_Chimeddorj
      Copper Contributor
      Feb 03, 2021
      I'm having this error when my source table have decimal columns. My workaround is cast the decimal to float, it works for me.

      Ex: select cast(a as float) from external_table
    • MarkE1475's avatar
      MarkE1475
      Copper Contributor
      Feb 02, 2021

      I'm receiving the same error with the same version of sql.  I'm attempting to create a SQL external data source though.  I have an open support ticket with Microsoft.  I'll update this thread if we discover a resolution.  

      • MarkE1475's avatar
        MarkE1475
        Copper Contributor
        Feb 10, 2021

        MarkE1475 We have a resolution for our issue '100001; Failed to generate query plan' when attempting to create External Data Source.  We had to reinstall the latest CU.  Steps to replicate the error:

        1) Install SQL 2019 base version.

        2) Upgrade the latest CU.

        3) Install Polybase.

        4) Attempt to create External Data Source - receive error.

        5) ***Resolution*** - Reinstall CU.

         

        EricChizzle I believe this will resolve your issue with Dev and Test environment not working the same.

Resources