Create external table with target database's name contains a dot

Question

I am working on creating external tables using SQL Server 2019 PolyBase feature. Everything works fine excepts an issue with a target database name contains a dot.

The target database is in SQL server. The problem is the database name contains a dot, xxxx.xxx. Here is statement for creating external table:

CREATE EXTERNAL TABLE [schema_name].[table_name]
(
[Column1] [int] NOT NULL,
[Column2] [int] NOT NULL,
[Column3] [decimal](18, 5) NOT NULL,
[Column4] BINARY(8) NOT NULL
)
WITH (LOCATION = N'[xxxx.xxx].[dbo].[table_name]', DATA_SOURCE = [data_source_name]);
When try to run it, I am getting the following error

105082;Generic ODBC error: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Could not find server 'xxxx' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. . 105082;Generic ODBC error: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Could not find server 'xxxx' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. . OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message "Unspecified error". OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message "Unspecified error".

I have put brackets around the database name [xxxx.xxx] when specify location, but still getting same errors.

Is there any solution (except change database name by removing dot) for fixing this issue?

Answer 1

This issue has been fixed in SQL Server 2019 CU11, see https://support.microsoft.com/en-us/topic/kb5003870-fix-support-using-the-polybase-sql-server-connector-to-create-external-table-with-remote-database-name-containing-a-period-f920ec30-2e9d-449e-a2af-6586d7c61712.

Answer 2

We are building a data warehouse database on SQL server 2019 that read data from different data source. PolyBase provides the features that fit our needs over linked server. We will try to open a support case on https://support.serviceshub.microsoft.com/supportforbusiness/onboarding?origin=%2Fsupportforbusiness%2Fcreate

Answer 3

That certainly looks like it could be the place, but I will need to add the disclaimer that I have never opened a support case myself.

Is there any particular reason you want to use Polybase over a traditional linked server?

Answer 4

Thanks a lot for your quick response! Since the target database (with dot in the name) is on production and it's used by other applications, we can't rename the database. So its' a blocking issue for us. Where should we open a support case? Is this web site https://support.serviceshub.microsoft.com/supportforbusiness/onboarding?origin=%2Fsupportforbusiness%2Fcreate

Answer 5

I was able to repro this with CU10, the most recent version of SQL 2019.

This looks like a bug to me. If this is a blocking issue for you and need a fix, you need to open a support case. Anything you is charged initially, should be refunded, since this clearly is a bug.

If you can live with the workaround of renaming the database, you can submit a bug on https://feedback.azure.com/forums/908035-sql-server to let Microsoft know about the issue.

The error message about sys.servers comes from the remote server. Using Profiler, I could see that Polybase submits this call:

exec [sys].sp_columns_ex_100 N'dotty',N' ',N'dbo',NULL,N'%',@fUsePattern=1

The name of my remote database was dotty.name.