none
SQLServer 2019 Polybase - External access operation failed because the location URI is invalid. Revise the URI and try again. RRS feed

  • Question

  • All,

    I tried to create an External Data Source against another SQLServer (2016) from a SQLServer 2019 server but I get the below error:

    <<External access operation failed because the location URI is invalid. Revise the URI and try again.>>

    NYCSQL10.nydomain.net is a SQLServer 2016 server

    Configure PolyBase to access external data in SQL Server

    What am I missing?

    Thanks,

    rgn

       CREATE EXTERNAL DATA SOURCE PolyBaseSQL
        WITH ( 
        LOCATION = 'sqlserver://NYCSQL10.nydomain.net\ACT:1488',
        -- PUSHDOWN = ON | OFF,
          CREDENTIAL = SQLServerCredentials
        );
        GO


    • Edited by grajee Thursday, December 5, 2019 6:14 PM
    Thursday, December 5, 2019 6:14 PM

All replies

  • Hi grajee,

    According to MS document, identifies the external data source using below format LOCATION = '<vendor>://<server>[:<port>]'. According to CREATE EXTERNAL DATA SOURCE, you can also use another format as below screenshot. 

    From your description, is NYCSQL10.nydomain.net is your host name or instance name? Can you connect to SQL server 2016 from SSMS on the server that SQL server 2019 is located.

    Best regards,
    Cathy 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, December 6, 2019 8:04 AM
  • Cathy,

    NYCSQL10.nydomain.net\ACT  is hostname\Instance. Yes. I can connect to the instance.

    rgn

    Friday, December 6, 2019 8:50 AM
  • Hey Cathy

    Don't have the answer for you, but have a workaround.. I was facing the very same issue!

    I found a workaround by doing away with the instance/port - there are many ways to do this, but ODBC is the simplest...

    - create a new connection (probably 64 bit)  for the Sql Server driver
    - enter any dummy name for the DSN and for the server, suggest you make it (aliased to) servername_instancename
    - click CLIENT CONFIGURATION and change the server name to servername\instancename, deselect dynamic and enter your static IP port (do NOT change the "server alias" - check it is still servername_instancename)
    - click OK, test, save etc

    Then in "CREATE EXTERNAL DATA SOURCE" you no longer need the FQ server\instance:port, you can just use the "alias name" you created above (servername_instancename)

    Hopefully someone might post the correct solution/fix in the future, but this got my moving forward...

    Cheers,,,

    Friday, December 6, 2019 10:01 AM
  • I tried creating External Table against SQLServer 2019 database and it works but it fails against SQLServer 2016 server (SQLServer 2017 ?).

    Does this mean that Polybase in SQLServer 2019 works only with SQLServer 2019 servers/instances and not against earlier versions of SQLServer (2012, 2014, 2016 and 2017).

    I tried the ODBC option that Matt mentioned as well but it does not work.

    Wednesday, December 11, 2019 8:05 PM
  • I tried creating External Table against SQLServer 2019 database and it works but it fails against SQLServer 2016 server (SQLServer 2017 ?).

    Does this mean that Polybase in SQLServer 2019 works only with SQLServer 2019 servers/instances and not against earlier versions of SQLServer (2012, 2014, 2016 and 2017).

    I tried the ODBC option that Matt mentioned as well but it does not work.

     It seems yes. 
    Tuesday, December 31, 2019 7:44 AM
  • Hi,

    Creating external table from SQL 2019, to SQL 2016/17 works fine. The issue you experience is that the LOCATION argument is just that; the physical server your SQL instance is on. If you want to connect to an instance other than default, you would need to use the CONNECTION_OPTIONS argument.

    So something like this:

    CREATE EXTERNAL DATA SOURCE PolyBaseSQL
    WITH
    (
      LOCATION = 'sqlserver://NYCSQL10.nydomain.net',
      --PUSHDOWN = ON | OFF,
      CONNECTION_OPTIONS = 'Server=NYCSQL10.nydomain.net\ACT:1488'
      CREDENTIAL = SQLServerCredentials
    );
    GO
    Hope this helps.



    Friday, January 3, 2020 5:19 AM
  • I tried creating External Table against SQLServer 2019 database and it works but it fails against SQLServer 2016 server (SQLServer 2017 ?).

    Does this mean that Polybase in SQLServer 2019 works only with SQLServer 2019 servers/instances and not against earlier versions of SQLServer (2012, 2014, 2016 and 2017).

    I tried the ODBC option that Matt mentioned as well but it does not work.

     It seems yes. 
    That is absolutely not true. To create external tables from SQL 2019 against SQL 2016/17 is absolutely fine. See my reply to the OP above.
    Friday, January 3, 2020 5:21 AM
  • The DATA SOURCE creation part goes through but the EXTERNAL TABLE creation part fails with the below error. It looks like it is unable to see the server on the network at all. I even tried without the FQDN, with the IP Address and without the Port. But none of the three helped.

    I even reviewed the link in order to troubleshoot:

    https://github.com/microsoft/bobsql/tree/master/demos/sqlserver/polybase/sqldatahub/sql2008r2

    Msg 105082, Level 16, State 1, Line 21
    105082;Generic ODBC error: [Microsoft][ODBC Driver 17 for SQL Server]SQL Server Network Interfaces: Connection string is not valid [87].  Additional error <2>: ErrorMsg: [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired, SqlState: HYT00, NativeError: 0 Additional error <3>: ErrorMsg: [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute, SqlState: 01S00, NativeError: 0 Additional error <4>: ErrorMsg: [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to, SqlState: 08001, NativeError: 87 .


    CREATE EXTERNAL DATA SOURCE SQLServer2016 
    WITH ( 
      LOCATION = 'sqlserver://NYCSQL10.nydomain.net',
      CONNECTION_OPTIONS = 'Server=NYCSQL10.nydomain.net\SQL2016:1444',
      CREDENTIAL = SQL2016Cred
    );
    
    CREATE EXTERNAL TABLE [dbo].[ClaimClassType](
    	[ClaimClassTypeCode] [varchar](3) NOT NULL,
    	[ClaimClassTypeDesc] [varchar](255) NOT NULL,
    	[CreatedBy] [varchar](32) NOT NULL,
    	[CreatedDate] [datetime] NOT NULL,
    	[VersionStamp] [varchar](100) NOT NULL)
    WITH (
     LOCATION='[ClaimDB].[dbo].[ClaimClassType]',
     DATA_SOURCE=SQLServer2016
    )
    GO

    Wednesday, January 8, 2020 5:38 PM