SQL Server 2019 PolyBase connection to SQL Server 2016 - Error

Question

I was trying to setup a linked server between SQL Server 2019 and SQL Server 2016 but I have been consistently getting the error as below:

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

Error:

Msg 105001, Level 16, State 1, Line 25
External access operation failed because the location URI is invalid. Revise the URI and try again

Has anyone worked on something similar?

grajee

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/df4841e5-57f3-451a-94c9-486cab2a431b/sqlserver-2019-polybase-external-access-operation-failed-because-the-location-uri-is-invalid?forum=sqldatabaseengine

Answer 1

I just answered your question on the MSDN forum, but I thought I should answer it here as well.

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 location of 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\ACT:1488'
   CREDENTIAL = SQLServerCredentials
);
GO

Hope this helps.