SQL Server 2022, CU 10 on Windows 2022. I am migrating a SQL Server 2019 database to a new SQL Server 2022 and when attempting to SELECT rows from an external Polybase driven table, I get the following error: The service master key on this server is a restored copy of the service master key from the original server. I verified that information by comparing the GUID of the ##MS_ServiceMasterKey## in the master database on both instances. The External Table is accessible on the original server. If the service master key in the master database is the same as the key in the original server, what other steps do I need to take? The password that was used to create the **database** master key is no longer available. Polybase is enabled for the instance and the Engine and Data Movement services are running. Thank you, Craig

I'm exploring sql polybase using Azure blob storage and other data sources. For blob storage sql server brings the entire blob onto the server and then performs where/join. What is the behaviour when using another database like mysql instead of Azure blob storage? Does polybase bring entire mysql table data into the sql server for each query?

I would like to install SQL Server 2019 on Linux either a full Linux server (Ubuntu 18.04) or on a Docker container with Polybase. Installing MSSQL 2019 on a Linux Server or a container is relatively easy. It's the Polybase bit that I cannot figure out. Using the instructions at this Microsoft Docs site: https://learn.microsoft.com/en-us/sql/relational-databases/polybase/polybase-linux-setup?view=sql-server-ver15 There is a reference to install Polybase on Ubuntu, which should be applicable in both cases (server or container). When I attempted to install the Polybase packages on an Ubuntu server I got this result: sudo apt-get install mssql-server-polybase Installed without any issue sudo apt-get install mssql-server-polybase-hadoop `The following packages have unmet dependencies: mssql-server-polybase-hadoop : Depends: mssql-zulu-jre-11 but it is not installable E: Unable to correct problems, you have held broken packages.` I could not find a solution to installing the package mssql-zulu-jre-11 on a Linux Server. In a container, I logged in as a Root user and attempted the commands above: apt-get install mssql-server-polybase E: Unable to locate package mssql-server-polybase I did verify that within the container the Linux instance has the correct sources for apt to find this package. I am completely confused as to how to get Polybase running with SQL Server 2019 on Linux. Have any of your had success? Any help would be greatly appreciated!

we have successfully created all pre-requisite of creating external tables using SQL Server 2017 Standard Edition CU22 Polybase. The issue we have is the non-sysadmin login can't query those external tables .It shows below error. >Msg 7416, Level 16, State 2, Line 29 Access to the remote server is denied because no login-mapping exists. The sysadmin login can query external tables without any issue. Is there anybody saw this issue and can help to resolve it.

Executing an OPENROWSET query with wildcards in the bulk path and the arguments in the WHERE clause seems not to work when using PolyBase to connect to S3-compatible storage. I have an on-premises SQL Server 2022 (16.0.4120.1) with PolyBase enabled. The external source is an S3-compatible object storage (Minio) running on a Linux Red Hat server. My version of the query: The S3 storage bucket is partitioned in source/year/month/date/file.txt. For the query below, the path is: testbucket/files_txt/year=2024/month=05/date=06/*.txt.

SELECT 
    r.filepath() AS filepath,
    r.filepath(1) AS [year],
    r.filepath(2) AS [month],
    COUNT_BIG(*) AS [rows]
FROM OPENROWSET( 
        BULK '/testbucket/files_txt/year=*/month=*/date=*/*.txt',
        DATA_SOURCE = 's3_ds', 
        FORMAT = 'CSV' ,
        FIRSTROW = 2,
        FIELDTERMINATOR = ';',
        ROWTERMINATOR = '\n'
    ) 
WITH ( 
    col1 varchar(800) 
) AS [r] 
WHERE 
    r.filepath(1) COLLATE Latin1_General_BIN = '2024'
    AND r.filepath(2) COLLATE Latin1_General_BIN = '05'
    AND r.filepath(3) COLLATE Latin1_General_BIN = '06'
GROUP BY 
    r.filepath(),
    r.filepath(1),
    r.filepath(2)
ORDER BY filepath;

When I execute the query as above, it takes several minutes to get back a result. However, when I run the query and replace the wildcards with the year, month, and date figures I have in the WHERE clause to the bulk string, it brings back the result in seconds. SQL Server gave the message that adding collation would enable pushdown, but I’m not sure if that is possible in this case. However, adding the collation did not seem to have any effect. Is this due to the fact that the polybase REST API does not mange to add the wildcard values to the query before it scans the storage partion scheme? I know that I can create a dynamic SQL to fix this. However, I thought this would work better since there are several examples in the documentation. Or have I missed something?

I'm trying to see if it's possible to create a PolyBase connection to a .CSV file. I'm using the Microsoft Access Text Driver and have set up a system DSN to the folder where the csv file is located. I'm able to query the file using OPENROWSET and the DSN, but when I try creating the external table, I'm getting the following generic error. Msg 105082, Level 16, State 1, Line 108 105082;Generic ODBC error: [Microsoft][ODBC Text Driver]Optional feature not implemented The file is just a simple one I created with three columns and two rows. Would it be possible to create a PolyBase connection to a .csv file? CREATE DATABASE SCOPED CREDENTIAL CSVFile WITH IDENTITY ='ident',SECRET='pass' CREATE EXTERNAL DATA SOURCE CSV WITH ( LOCATION = 'odbc://localhost' ,CONNECTION_OPTIONS = 'Driver={Microsoft Access Text Driver (*.txt; *.csv)};DSN=CSVs' ,CREDENTIAL = CSVFile ); CREATE EXTERNAL TABLE TestCSV ( Col1 VARCHAR(255) NULL ,Col2 VARCHAR(255) NULL ,Col3 VARCHAR(255) NULL ) WITH ( LOCATION = N'Test.csv', DATA_SOURCE = CSV ) GO

I have been scratching my head for a while now on how to fix this issue, SQL Server Polybase Data Movement Service starts successfully however after a few seconds it just stops. The OS on our server went corrupt last week and force us to reload the OS(Windows Server 2016 Data Center), which meant I had to reinstall and reconfigure Microsoft SQL Server 2017 Enterprise. Since this I cannot get the data movement engine to start and stay running as I have a few process that relies on this I am really in need of some advice. I have found an Microsoft article below which if I look at error:3409 (Which I cannot seem to find in the logs) is referring to registry access. I have always installed and configured Microsoft Sql Server to only use the service accounts for the Engine and the Agent and leave the other services to the defaults. https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-events-and-errors?view=sql-server-ver15 See logs **MSSQLSERVER_DET1_Dms_movement** *2020/04/03 3:14:28 PM [Thread:114900] [CommandManager:ErrorEvent] (Error, High): Shared memory segment has not been created or attached. Microsoft.SqlServer.DataWarehouse.DataMovement.SharedMemory.NativeSharedMemoryException: Shared memory segment has not been created or attached. at Microsoft.SqlServer.DataWarehouse.DataMovement.SharedMemory.NativeSharedMemory.ThrowIfInvalid() at Microsoft.SqlServer.DataWarehouse.DataMovement.SharedMemory.NativeSharedMemory.SetDistributionCount(Int32 distrCount) at Microsoft.SqlServer.DataWarehouse.DataMovement.Nodes.DMSChannelMap.UpdateSharedMemoryDistributionCount() at Microsoft.SqlServer.DataWarehouse.DataMovement.Nodes.DMSChannelMap.AddDataChannelReceiver(String nodeName, DataChannelReceiver dataChannelReceiver) at Microsoft.SqlServer.DataWarehouse.DataMovement.CommandManager.StartDataChannel(Int32 senderQueueSize, Int32 tcpBufferSize, HashSet`1 nodesToConnect) at Microsoft.SqlServer.DataWarehouse.DataMovement.CommandManager.ProcessStartDataChannel(IMessage message) at Microsoft.SqlServer.DataWarehouse.DataMovement.CommandManager.ProcessBaseMessage(IBaseMessage message) 2020/04/03 3:14:28 PM [Thread:114900] [CommandManager:InformationEvent] (Info, Normal): Shutting down. 2020/04/03 3:14:28 PM [Thread:12864] [DataChannelReceiver:InformationEvent] (Info, Normal): DataChannelReceiver DataChannelReceiver.Run--DET1:1433: Localhost DET1:1433 connected to RemoteHost DET1:1433: Initiating Run loop. 2020/04/03 3:14:28 PM [Thread:114900] [GeneralInstrumentation:SynchronousLogEvent] (Info, Normal): DMS Exit() called: Forceful shutdown initiated... 2020/04/03 3:14:28 PM [Thread:93816] [DMSDataChannelSenderClient:InformationEvent] (Info, Normal): DMSDataChannelClient created 2020/04/03 3:14:28 PM [Thread:93816] [DataChannelSender:InformationEvent] (Info, Normal): Starting CreditFeedbackReceiver thread: CreditFeedbackReceiver.Run--DET1 2020/04/03 3:14:28 PM [Thread:93816] [CommandManager:InformationEvent] (Info, Normal): Started data channel, node: DET1, type: Sender 2020/04/03 3:14:28 PM [Thread:84568] [CommandManager:InformationEvent] (Info, Normal): Started data channel, node: DET1, type: CreditFeedback* **UPDATE** When stopping the SQL Sever Polybase Engine and then Trying to start the Polybase Data Movement Service, then the service remain started until I start the Polybase Engine Service then back to square 1 again.

I've recently performed an in-place upgrade of the default instance of a SQL Server 2016 to SQL Server 2019 (CU11). I'm trying to add the Polybase feature but am unable to resolve this error that occurs during installation: > Error installing SQL PolyBase The proposed new database owner is already a user or aliased in the database. Changed database context to 'DWQueue'. Error code: 0x84BB0001 The following error dialog appears during the installation process *Retry* just results in the same message, after clicking *Cancel* the installation continues and then finishes with the install dialog showing the following: If I re-run the setup it thinks Polybase is installed (however it's not, connecting using SSMS still informs me I have to install Polybase). I have to manually run msiexec /x {6FDD1CAA-79C4-43B0-9483-5BD503E656DC} to remove it before I can retry. During setup I provided domain accounts for the service setup, there's nothing else to configure. Can anyone shed any light on this error and what I can do to install successfully? **Edit** When in the setup process and presented with the Service Accounts screen for Polybase Engine and Polybase Data Movement, the text at the top of the screen states "*Microsoft recommends that you use separate account for each SQL Server service.*" However if I specify a different Account Name & password for these services it produces an error and you cannot proceed:

Microsoft states that: > [...You can also use the generic ODBC connector to connect to additional > providers using third-party ODBC drivers.] The reality is much far away form that. I've been trying to follow several guides about how to query DB2 from PolyBase. I know this should be possible. I've also find guides about how to query SalesForce through the ODBC Driver so yes, you should be able to query a lot of things. The only stable code I could create and execute successsfully is this: -- Create a database CREATE DATABASE Test_PolyBase;GO -- Use that database USE Test_PolyBase;GO -- Create a database master key. ALTER MASTER KEY ENCRYPTION BY PASSWORD = 'password';GO -- Set DB2 user and password ALTER DATABASE SCOPED CREDENTIAL Test_PolyBase WITH IDENTITY = 'db2inst1' --put here DB2's username ,SECRET = 'db2_password';--put here DB2's password GO From there on is a no men's land where no one really knows what's the next move. I've successfully created a System DSN using the IBM DB2 ODBC DRIVER - DB2COPY1 Driver: But then: CREATE EXTERNAL DATA SOURCE Test_PolyBase WITH ( LOCATION = 'odbc://192.168.1.44:50000' ,CONNECTION_OPTIONS = 'DSN={testdb}' --,PUSHDOWN = ON ,CREDENTIAL = Test_PolyBase ) Is not working. I tried to use a more detailed setup and the Driver itself instead of the DSN: CREATE EXTERNAL DATA SOURCE Test_PolyBase WITH ( LOCATION = 'odbc://192.168.1.44' ,CONNECTION_OPTIONS = 'Driver={IBM DB2 ODBC DRIVER - DB2COPY1};PolyBaseOdbcSupportsSetDescRec=false' --,PUSHDOWN = ON ,CREDENTIAL = Test_PolyBase ); And I have also used a more detailed one: CREATE EXTERNAL DATA SOURCE Test_PolyBase WITH ( LOCATION = 'odbc://192.168.1.44:50000' ,CONNECTION_OPTIONS = 'Dsn={testdb}; Driver={IBM DB2 ODBC DRIVER - DB2COPY1}; uid=root; server=192.168.1.44; port=50000; database=testdb; PolyBaseOdbcSupportsSetDescRec=false' --,PUSHDOWN = ON ,CREDENTIAL = Test_PolyBase ) I'm using the PolyBaseOdbcSupportsSetDescRec=false option as described in the example but no joy, the error is always the same: OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message "Cannot generate SSPI context". Msg -2146893042, Level 16, State 1, Line 0 SQL Server Network Interfaces: No credentials are available in the security package Why SQL Server is trying to use OLE DB provider "MSOLEDBSQL"? That should be for SQL Server, right? Not DB2?! And why ...for linked server "(null)" (which means I'm not indicating the linked server)? I shouldn't been using linked server at all. **EDIT:** After extensive investigation I'm even more certain hat this might be flagged as a bug. The kb4552255 is in fact specifying that: > You can access configurations that ship with PolyBase when specifying > a DSN in the CONNECTION_OPTIONS of a Generic ODBC External Data Source > definition. The options are picked up by matching the driver name used > in the DSN definition. This currently applies to the following > drivers: > > • IBM DB2 ODBC DRIVER > > • HDBODBC > > • Microsoft Spark ODBC Driver So the examples provided in the reply should work. With DSN but also with Driver.

I'm using polybase to load data from PostgreSQL and have some problem The PostgreSQl database and SQL server (for my case is SQL server 2022) are in same machine. I create an external table in SQL server to loading data from PostgreSQL database by Polybase, It's took at least over 1s even the table is empty and meanwhile i ran it in Pgadmin it run ok. I already Select only **id** column but it was same result.Here is some information: 1. When i run in pgadmin 2. run with Linked Server **As you can see it took only 329 ms** 2. I create table in SQL server

    CREATE EXTERNAL TABLE [dbo].[SaleorApp]
    (
    	[id] [int] NOT NULL,
    	[private_metadata] [nvarchar](max) NULL,
    	[metadata] [nvarchar](max) NULL,
    	[name] [nvarchar](60) NOT NULL,
    	[created_at] [datetime2](6) NOT NULL,
    	[is_active] [nvarchar](5) NOT NULL,
    	[about_app] [nvarchar](max) NULL,
    	[app_url] [nvarchar](200) NULL,
    	[configuration_url] [nvarchar](200) NULL,
    	[data_privacy] [nvarchar](max) NULL,
    	[data_privacy_url] [nvarchar](200) NULL,
    	[homepage_url] [nvarchar](200) NULL,
    	[identifier] [nvarchar](256) NULL,
    	[support_url] [nvarchar](200) NULL,
    	[type] [nvarchar](60) NOT NULL,
    	[version] [nvarchar](60) NULL,
    	[manifest_url] [nvarchar](200) NULL
    )
    WITH (DATA_SOURCE = [PostgreSQL35W],LOCATION = N'"saleor"."public"."app_app"')

After that i ran it in SQL server 2022

SET STATISTICS TIME ON;
SELECT  [id]
      ,[private_metadata]
      ,[metadata]
      ,[name]
      ,[created_at]
      ,[is_active]
      ,[about_app]
      ,[app_url]
      ,[configuration_url]
      ,[data_privacy]
      ,[data_privacy_url]
      ,[homepage_url]
      ,[identifier]
      ,[support_url]
      ,[type]
      ,[version]
      ,[manifest_url]
  FROM [Pastaxi].[dbo].[SaleorApp]
SET STATISTICS TIME OFF;

And it ruturned : **SQL Server Execution Times: CPU time = 0 ms, elapsed time = 940 ms.** I already checked in Polybase log and here is result:

12/3/2022 10:15:54 AM [Thread:2304] [PdwTdsNativeToManagedInterop:InformationEvent] (Info, Normal): Incoming TDS connection, Client TDS version: 7 (TDS74). 
12/3/2022 10:15:54 AM [Thread:2304] [EngineInstrumentation:TdsAddConnectionEvent] (Info, Low): Starting TDS connection: 1929345925184 Client: 192.168.1.22:63040, isFromDataSecurityProxy: False, isVnetAddress: False, clientAddressSafe: 192.168.1.22:63040 
12/3/2022 10:15:54 AM [Thread:2304] [DataClassificationConfig:InformationEvent] (Info, Normal): Returning FeatureSwitch DataClassificationCoreEnabled status: False [Session.SessionId:SID3170][Session.IsTransactional:False]
12/3/2022 10:15:54 AM [Thread:2304] [EngineInstrumentation:ServerStartSessionEvent] (Info, Low): Started new session, in reset connection: False. [Session.SessionId:SID3170][Session.IsTransactional:False]
12/3/2022 10:15:54 AM [Thread:2304] [TdsRequestProcessor:InformationEvent] (Info, Normal): Processing login, authentication scheme: "SqlAuthentication", client application name: "C39A9D00-5206-85DF-3907-338474E48E80" 
12/3/2022 10:15:54 AM [Thread:2304] [PdwTdsNativeToManagedInterop:InformationEvent] (Info, Normal): Data Classification TDS Extension Requested=False, FS Enabled=False, Enabled=False. [Session.SessionId:SID3170][Session.IsTransactional:True][Query.QueryId:QID3294]
12/3/2022 10:15:54 AM [Thread:1940] [EngineInstrumentation:TdsAddConnectionEvent] (Info, Low): Starting TDS connection: 1929427025984 Client: , isFromDataSecurityProxy: False, isVnetAddress: False, clientAddressSafe:  
12/3/2022 10:15:54 AM [Thread:13664] [EngineInstrumentation:EngineStopSessionBeginEvent] (Info, Low): Stopping session, in reset connection: False. [Session.SessionId:SID3170][Session.IsTransactional:False]
12/3/2022 10:15:54 AM [Thread:13664] [EngineInstrumentation:TdsRemoveConnectionEvent] (Info, Low): Stopping TDS connection: 1929427025984 
12/3/2022 10:15:54 AM [Thread:2304] [TdsRequestProcessor:InformationEvent] (Info, Normal): CancelBatch request started [Session.SessionId:SID3170] 
12/3/2022 10:15:54 AM [Thread:2304] [TdsRequestProcessor:InformationEvent] (Info, Normal): CancelBatch request started [Session.SessionId:SID3170] 
12/3/2022 10:15:54 AM [Thread:3860] [EngineInstrumentation:EngineCancelQueryBeginEvent] (Info, Low): Cancel requested. [Session.SessionId:SID3170][Session.IsTransactional:False]
12/3/2022 10:15:54 AM [Thread:2288] [EngineInstrumentation:EngineCancelQueryBeginEvent] (Info, Low): Cancel requested. [Session.SessionId:SID3170][Session.IsTransactional:False]
12/3/2022 10:15:54 AM [Thread:13664] [EngineInstrumentation:TdsRemoveConnectionEvent] (Info, Low): Stopping TDS connection: 1929345925184 
12/3/2022 10:15:54 AM [Thread:13664] [EngineInstrumentation:EngineStopSessionBeginEvent] (Info, Low): Stopping session, in reset connection: False. [Session.SessionId:SID3170][Session.IsTransactional:False]
12/3/2022 10:15:54 AM [Thread:2304] [PdwTdsNativeToManagedInterop:InformationEvent] (Info, Normal): Incoming TDS connection, Client TDS version: 7 (TDS74). 
12/3/2022 10:15:54 AM [Thread:2304] [EngineInstrumentation:TdsAddConnectionEvent] (Info, Low): Starting TDS connection: 1929345925184 Client: 192.168.1.22:63041, isFromDataSecurityProxy: False, isVnetAddress: False, clientAddressSafe: 192.168.1.22:63041 
12/3/2022 10:15:54 AM [Thread:2304] [DataClassificationConfig:InformationEvent] (Info, Normal): Returning FeatureSwitch DataClassificationCoreEnabled status: False [Session.SessionId:SID3171][Session.IsTransactional:False]
12/3/2022 10:15:54 AM [Thread:2304] [EngineInstrumentation:ServerStartSessionEvent] (Info, Low): Started new session, in reset connection: False. [Session.SessionId:SID3171][Session.IsTransactional:False]
12/3/2022 10:15:54 AM [Thread:2304] [TdsRequestProcessor:InformationEvent] (Info, Normal): Processing login, authentication scheme: "SqlAuthentication", client application name: "C39A9D00-5206-85DF-3907-338474E48E80" 
12/3/2022 10:15:54 AM [Thread:2304] [PdwTdsNativeToManagedInterop:InformationEvent] (Info, Normal): Data Classification TDS Extension Requested=False, FS Enabled=False, Enabled=False. [Session.SessionId:SID3171][Session.IsTransactional:True][Query.QueryId:QID3295]
12/3/2022 10:15:54 AM [Thread:1940] [EngineInstrumentation:TdsAddConnectionEvent] (Info, Low): Starting TDS connection: 1929427025984 Client: , isFromDataSecurityProxy: False, isVnetAddress: False, clientAddressSafe:  
12/3/2022 10:15:54 AM [Thread:2304] [ServerInterface:InformationEvent] (Info, Normal): Incoming Query:  SID3171:QID3296 [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:54 AM [Thread:3860] [ServerInterface:InformationEvent] (Info, Normal): Starting processor ExecuteMemoProcessor. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:54 AM [Thread:3860] [OptimizedStatement:InformationEvent] (Info, Normal): Memo compilation time: 2.0026 ms [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:54 AM [Thread:3860] [OptimizedStatement:InformationEvent] (Info, Normal): Sql Server Optimization Clock: 0.001 s, CPU: 0.001 s. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:54 AM [Thread:3860] [OptimizedStatement:InformationEvent] (Info, Normal): SQL Server XML generation Clock : 0.001 s, CPU: 0.001 s [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:54 AM [Thread:3860] [NativeOdbcConnection:InformationEvent] (Info, Normal): NativeOdbcConnection.Open, ConnectionString: Dsn=PostgreSQL35W;uid=saleor;server=localhost;port=5432, CreateOptions: ReadExternal, PacketSize: 0, EnableConnectionPooling: True, TransactionPropagationToken: null, ResourceGroup: null [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:54 AM [Thread:3860] [NativeOdbcConnection:InformationEvent] (Info, Normal): NativeOdbcConnection.Open, ConnectionString: Dsn=PostgreSQL35W;uid=saleor;server=localhost;port=5432, CreateOptions: ReadExternal, PacketSize: 0, EnableConnectionPooling: True, TransactionPropagationToken: null, ResourceGroup: null [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:54 AM [Thread:3860] [NativeOdbcConnection:InformationEvent] (Info, Normal): NativeOdbcConnection.Open, ConnectionString: Dsn=PostgreSQL35W;uid=saleor;server=localhost;port=5432, CreateOptions: ReadExternal, PacketSize: 0, EnableConnectionPooling: True, TransactionPropagationToken: null, ResourceGroup: null [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:54 AM [Thread:3860] [NativeOdbcConnection:InformationEvent] (Info, Normal): NativeOdbcConnection.Open, ConnectionString: Dsn=PostgreSQL35W;uid=saleor;server=localhost;port=5432;database=saleor, CreateOptions: ReadExternal, PacketSize: 0, EnableConnectionPooling: True, TransactionPropagationToken: null, ResourceGroup: null [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:54 AM [Thread:3860] [NativeOdbcConnection:InformationEvent] (Info, Normal): NativeOdbcConnection.Open, ConnectionString: Dsn=PostgreSQL35W;uid=saleor;server=localhost;port=5432;database=saleor, CreateOptions: ReadExternal, PacketSize: 0, EnableConnectionPooling: True, TransactionPropagationToken: null, ResourceGroup: null [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:54 AM [Thread:3860] [NativeOdbcConnection:InformationEvent] (Info, Normal): NativeOdbcConnection.Open, ConnectionString: Dsn=PostgreSQL35W;uid=saleor;server=localhost;port=5432;database=saleor, CreateOptions: ReadExternal, PacketSize: 0, EnableConnectionPooling: True, TransactionPropagationToken: null, ResourceGroup: null [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:54 AM [Thread:3860] [NativeOdbcConnection:InformationEvent] (Info, Normal): NativeOdbcConnection.Open, ConnectionString: Dsn=PostgreSQL35W;uid=saleor;server=localhost;port=5432;database=saleor, CreateOptions: ReadExternal, PacketSize: 0, EnableConnectionPooling: True, TransactionPropagationToken: null, ResourceGroup: null [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:54 AM [Thread:3860] [NativeOdbcConnection:InformationEvent] (Info, Normal): NativeOdbcConnection.Open, ConnectionString: Dsn=PostgreSQL35W;uid=saleor;server=localhost;port=5432;database=saleor, CreateOptions: ReadExternal, PacketSize: 0, EnableConnectionPooling: True, TransactionPropagationToken: null, ResourceGroup: null [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:55 AM [Thread:3860] [NativeOdbcConnection:InformationEvent] (Info, Normal): NativeOdbcConnection.Open, ConnectionString: Dsn=PostgreSQL35W;uid=saleor;server=localhost;port=5432;database=saleor, CreateOptions: ReadExternal, PacketSize: 0, EnableConnectionPooling: True, TransactionPropagationToken: null, ResourceGroup: null [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:55 AM [Thread:3860] [ExecuteMemoStatement:InformationEvent] (Info, Normal): Operation on External Table [SaleorApp] with LOB columns : Columns 17, LOBColumns 4, LOBStringColumns 4. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:55 AM [Thread:3860] [LimitNumberOfScaOpConst:InformationEvent] (Info, Normal): The number of literals in the query: 0. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:55 AM [Thread:3860] [OptimizedStatement:InformationEvent] (Info, Normal): Distributed QO time: 728.4171 ms [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:55 AM [Thread:3860] [DMSManager:InformationEvent] (Info, Normal): ExecuteCommand Query:SID3171 Plan:3e7c8916-13e2-41c2-a8cf-8c32837d290f [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:55 AM [Thread:3860] [DMSManager:InformationEvent] (Info, Normal): DMS Manager starting query: SID3171, plan: 3e7c8916-13e2-41c2-a8cf-8c32837d290f. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:55 AM [Thread:3860] [AbstractDataMovementExecutable`1:InformationEvent] (Info, Normal): DMS Manager finishing query: SID3171, plan: 3e7c8916-13e2-41c2-a8cf-8c32837d290f, queryId: QID3296 [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:55 AM [Thread:3860] [ServerInterface:InformationEvent] (Info, Normal): Query SID3171:QID3296 completed. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296]
12/3/2022 10:15:55 AM [Thread:1940] [EngineInstrumentation:TdsAddConnectionEvent] (Info, Low): Starting TDS connection: 1929427042368 Client: , isFromDataSecurityProxy: False, isVnetAddress: False, clientAddressSafe:  
12/3/2022 10:15:55 AM [Thread:2304] [EngineInstrumentation:EngineStopSessionBeginEvent] (Info, Low): Stopping session, in reset connection: False. [Session.SessionId:SID3171][Session.IsTransactional:False]
12/3/2022 10:15:55 AM [Thread:2304] [EngineInstrumentation:TdsRemoveConnectionEvent] (Info, Low): Stopping TDS connection: 1929427025984 
12/3/2022 10:15:55 AM [Thread:5232] [EngineInstrumentation:TdsRemoveConnectionEvent] (Info, Low): Stopping TDS connection: 1929427042368 
12/3/2022 10:15:55 AM [Thread:2512] [TdsRequestProcessor:InformationEvent] (Info, Normal): CancelBatch request started [Session.SessionId:SID3171] 
12/3/2022 10:15:55 AM [Thread:2288] [EngineInstrumentation:EngineCancelQueryBeginEvent] (Info, Low): Cancel requested. [Session.SessionId:SID3171][Session.IsTransactional:False]
12/3/2022 10:15:55 AM [Thread:2512] [EngineInstrumentation:TdsRemoveConnectionEvent] (Info, Low): Stopping TDS connection: 1929345925184 
12/3/2022 10:15:55 AM [Thread:2512] [EngineInstrumentation:EngineStopSessionBeginEvent] (Info, Low): Stopping session, in reset connection: False. [Session.SessionId:SID3171][Session.IsTransactional:False]

I don't know why that is, any help for me??? Thanks for watching

We've been experimenting with Polybase on a SQL Server 2019 (CU2) box, using SQL Server external data sources and the performance has not been good - in most cases a 1400% increase. In each case all of the tables/views we're querying are from external tables pointing at the same external data source. We've tried both running the query broken out on the local box, and using the same query as a view pulled in as an external table. We've also scripted every statistic from the remote server onto the external tables with no change. You can see the performance difference below, using a sample query. The servers are setup identical resource wise: 32GBs of RAM, 8 vCPU, SSD disks, and no other running queries. I've tried against two different remote servers, one running SQL Server 2016 with the latest SP/CU, and a separate 2019 box running CU2. The servers are VMs running on the same host, and we've ruled out any type of host contention. Sample Query:

SELECT
     StockItem_StockNumber, BlanktypeId, NameHTML, BackgroundStrainName, IsExact, IsConditional
     ,ROW_NUMBER() Over(Partition By StockItem_StockNumber, BlanktypeId Order By pt.Name, p.Name, gptr.Text) as row_num
     ,pt.Name as Level1, p.Name as Level2, gptr.Text as Level3, MGIReference_JNumber
     ,gptr.Type as Level3Type  
FROM
     1 sig
     INNER JOIN 2 g on g.BlanktypeId = sig.Blanktype_BlanktypeId
     INNER JOIN 3 gpt on gpt.Blanktype_BlanktypeId = g.BlanktypeId
     INNER JOIN 4 p on p.StocktypeTermId = gpt.StocktypeTerm_StocktypeTermId
     INNER JOIN 5 gptr on gptr.BlanktypeStockTerm_BlanktypeStockTermId = gpt.BlanktypeStockTermId
     INNER JOIN 6 ptc on ptc.ChildStockTerm_StocktypeTermId = p.StocktypeTermId
     INNER JOIN 7 pt on pt.StocktypeTermId = ptc.ParentStockTerm_StocktypeTermId
WHERE
     ptc.ParentHeaderKey = 3

Running on the remote 2016/2019 box directly:

SQL Server Execution Times:
   CPU time = 3486 ms,  elapsed time = 5035 ms.

Running on the 2019 box with Polybase and with PUSHDOWN OFF:

SQL Server Execution Times:
   CPU time = 15016 ms,  elapsed time = 92113 ms.

Running on the 2019 box with Polybase and with PUSHDOWN ON:

SQL Server Execution Times:
   CPU time = 3875 ms,  elapsed time = 74149 ms.

Polybase Execution Plan (they look the same regardless of PUSHDOWN option): sp_whoisactive (wait info) shortly after starting Polybase query: sp_whoisactive (wait info) further into the Polybase query: Using a Linked Server instead of Polybase:

SQL Server Execution Times:
   CPU time = 3032 ms,  elapsed time = 9316 ms.

That's roughly a 1400% increase in the time it takes the query to run. What's confusing is that Microsoft is pushing Polybase as an ETL replacement, but there is no way that's possible with this kind of performance. Are other people seeing similar performance using Polybase with connections from SQL Server to SQL Server? And, does anyone know what Polybase internal operations could be causing this kind of slowness? Thank you. ******************** ***Update on 2/23/2020:*** Not that it has any implications to the query performance, but I discovered today that Polybase queries are not honoring MAXDOP settings (either instance wide, or query hints) and the set statistics CPU time is not reporting accurately. I was able to look at the execution plan through XML, and this is the only section that is informative. The only information I could find on PREEMPTIVE_SHAREDMEM_GETDATA is its a wait type when a thread is waiting for a call to the GetData method of a COM object to complete. ******************** ***Second update on 2/23/2020:*** I created a single large table and I ran a "select *" both locally and over Polybase. Those runs have almost identical run times. I opened a support case with Microsoft and I'll report back if we make any headway. ******************** ***Update on 2/24/2020:*** After Kevin's response (below) I made a few tweaks to the original post for clarity, and running the queries/DMVs he referenced comes back with these results:

How to determine what is causing Microsoft SQL Server to create dump files over and over taking up 170 GB of disk space? How do I troubleshoot what is causing the dumps? The path of the dump files is C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSQL\Log\Polybase\dump Can I delete these? I have looked at https://dba.stackexchange.com/questions/87112/the-sql-server-log-folder-is-expanding-because-of-the-sql-dump-files-what-to-do but it doesn't really answer my question - it mainly states to contact Microsoft.

I would like to ETL a table from one instance of SQL Server to another instance of SQL Server. I do not want to create an SSIS package. I have tried 1) to extract the data using an External Table on the destination (i.e. I use the External Table to create a local copy e.g. with

INTO #Hello FROM MyExternalTable

). 2) Linked Server with using

* INTO #HELLO FROM MyLinkedServer.MyDB.dbo.MyTable

3) I have also used

. They all run single threaded, which I can see from the execution plans. I have not used

but it seems that I first need to save the table to a file and then read it from the file and load it. So I do not think it is the correct tool for this task. What is the fastest approch to copy a table from one SQL Server instance to another SQL Server instance?

I have customer information in a SQL server table, and the billing information is exposed via REST web api (with customer_id as parameter). I am exploring using polybase to create the polybase table to represent the REST api, so that I can perform a JOIN between the customer table and REST web api. Does polybase support this?

The documentation for Polybase says that creating statistics for External Tables will help getting better execution plans. This question is about when the External Data Source is another SQL Server. I guess the better execution plan is on the External Data Source, i.e. for pushdown computations (for where clauses, joins and aggregates). To get a good execution plan the optimizer also has to know what indexes are on the table. Can the optimizer use the statistics on the remote server (the External Data Source)? If yes, what are the benefits of creating the statistics locally on the External Table? If the optimizer has too look at the indexes on the remote server, then it might as well look at the statistics... Please provide references in the answer.

I'm installing PolyBase on the latest SQL Server 2019. During the installation I'm incurring in this error: https://www.youtube.com/watch?v=Oxd8yeiBfig (sorry for the video quality) The proposed new database owner is already a user or aliased in the database. Changed database context to 'DWQueue'. Yes, this error has already been addressed here: https://dba.stackexchange.com/questions/296176/sql-server-2019-polybase-installation-error-the-proposed-new-database-owner-is This means to me that Administrators cannot install PolyBase. Is there any way out without dropping my Administrator?

We have two SQL Servers (both running Enterprise version on 15.0.4083.2) where we use queries to External tables via Polybase from one server to the other. We have encountered a strange error when we use the getdate() function to filter against a datetime column. The query fails with the following error message: >Msg 7320, Level 16, State 110, Line 4 >Cannot execute the query "Remote Query" against OLE DB provider "MSOLEDBSQL" for linked server "(null)". 105082;Generic ODBC error: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. . The query used from the server where the external table is created is as follows:

SELECT TidID
FROM Felles.DimTid
WHERE TidID <= GETDATE();

The query converted by Polybase (I think) and executed at the destination server (where it fails) is as follows (from SQL Profiler):

SELECT [T1_1].[TidID] AS [TidID]
FROM
(
    SELECT [T2_1].[TidID] AS [TidID]
    FROM [DBName].[Felles].[DimTid] AS T2_1
    WHERE ([T2_1].[TidID] <= CAST('2021-10-08 13.32.41.373' AS datetime))
) AS T1_1;

The problem is how it has converted the GetDate() function: it uses periods between hour.minute.second, instead of using colons (hour:minute:second). Is there a way for me to change this behavior in our system settings? (We do not want to have to manipulate the queries themselves).

SQL Server 2016, SQL Server Management Studio (SSMS). Creating external tables with Transact-SQL and Polybase. How can we read decimals with a decimal mark, comma instead of dot? When using float and decimal, it fails and throws an error. When using money data type it succeeds, but misreads decimals and integers are returned instead. Revised the file format standard clause, no option to configure such a thing. Collation already set for my region. Sample data:

-none
2017;4;2017;601PPP;183,63;0
2017;4;2017;601PPP;183,63;0
2017;4;2017;601PPP;183,63;1.000,55
2017;4;2017;601PPP;183,63;2,5
2017;4;2017;601PPP;183,63;7,5
2017;4;2017;601PPP;405,28;17,5

Example of a create table that would fail to read the sample data: CREATE EXTERNAL TABLE [dbo].[STG_Table] ( field1 smallint NULL ,field2 tinyint NULL ,field3 smallint NULL ,fieldn_2 varchar(10) NULL ,fieldn_1 money NULL ,fieldn float NULL ) WITH (LOCATION='/STG_Table/', DATA_SOURCE = AzureDataSource, FILE_FORMAT = FileFormat ); Query used to read data: SET @dynamicSQL = N'SELECT * INTO ##' + @someTable + ' FROM STG_' + @someTable EXEC sp_executesql @dynamicSQL Specifying fields implies more cases to be added (working inside a procedure) and it can be avoided by configuring such a thing. I found more simple configuring this, than adding more code. No configuration found in database configuration.

I'm new to polybase. I have linked my SQL Serer 2019 to a third parties Azure cosmos and i am able to query data out of my collection. I am getting an error out when i try to query date fields though. In the documents the dates are defined as: "created" : { "$date" : 1579540834768 }, In my external table i have the column defined as [created] DATE, I have tried to create the column as int and nvarchar(128) but the schema detection rejects it each time. (I have tried to create a field created_date but the schema detection also disagrees that this is correct. When I try a query that returns any of the date fields I get this error: Msg 105082, Level 16, State 1, Line 8 105082;Generic ODBC error: [Microsoft][Support] (40460) Fractional data truncated while performing conversion. . OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message "Unspecified error". Msg 7421, Level 16, State 2, Line 8 Cannot fetch the rowset from OLE DB provider "MSOLEDBSQL" for linked server "(null)". . This happens if i try and exclude null values in my query - even when filtering to specific records where the date is populated (validated using the Azure portal interface) Is there something i should be doing to handle the integer date from the json records; or another type i can use to get my external table to work?

I installed SQL Server 2017 Enterprise (14.0.3162.1) on my box. With the polybase (Scale-out Group). But when I try to enable through: exec sp_configure @configname = 'polybase enabled', @configvalue = 1; RECONFIGURE WITH OVERRIDE; It shows: > Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62 [Batch Start Line 0] The configuration option 'polybase enabled' does not exist, or it may be an advanced option. So I used: sp_configure 'show advanced option', 1; GO RECONFIGURE Go and check by: select * from sys.configurations where name like '%poly%' only: polybase network encryption allow polybase export shows up. How can I enable this feature in this case? Please let me know if I missed any.