I want to know How can I use Excel from SQL Server 2019 with PolyBase?which ODBC driver string do I need to use for connecting Excel file with External Data Source?and anyone can do it?Reference: Configure PolyBase to access external data in SQL Server

Okey. I am use CDATA EXCEL ODBC Driver x64..Fixed it.https://www.cdata.com/drivers/excel/download/odbc/ Uninstall Microsoft Access Database Engine 2016 Redistributable..Install CDATA EXCEL ODBC Driver Version: 18.0.6817. Thanks!

How can I use Excel from SQL Server 2019 with PolyBase?

佳易楊
Copper Contributor
Nov 03, 2018
Hey..
I want to know too..

I have a T-SQL code..

/**************************/
USE [PolyBaseMSSQL_MySQL_Excel]
GO

CREATE DATABASE SCOPED CREDENTIAL [ExcelCredential]
WITH IDENTITY = 'MyExcel', Secret = ''
GO

select *
from sys.database_scoped_credentials

--Using [Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) Ver16.00 x64]
CREATE EXTERNAL DATA SOURCE [ExcelInstance]
WITH (
LOCATION = 'odbc://<hostname>',
CONNECTION_OPTIONS ='DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DSN=MyExcel',
CREDENTIAL = [ExcelCredential])
GO

select *
from sys.external_data_sources

--Run this then Error!!!!
CREATE EXTERNAL TABLE [extExcel_Person](
[RowID] [int] NOT NULL,
[PersonID] [varchar](10) NOT NULL,
[Name] [varchar](100) NOT NULL,
[CorpCode] [varchar](2) NOT NULL,
[PostCode] [varchar](6) NOT NULL)
WITH
(LOCATION = 'Sheet1',DATA_SOURCE = [ExcelInstance])
GO
/**************************/

I got this error.
メッセージ 105082、レベル 16、状態 1、行 170
105082; 汎用 ODBC エラー: [Microsoft][ODBC Excel Driver]Optional feature not implemented 。

So, I try change「CONNECTION_OPTIONS」value.
/**************************/
USE [PolyBaseMSSQL_MySQL_Excel]
GO

DROP EXTERNAL DATA SOURCE [ExcelInstance]
GO

CREATE EXTERNAL DATA SOURCE [ExcelInstance]
WITH (
LOCATION = 'odbc://<hostname>',
CONNECTION_OPTIONS ='DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};Excel File=C:\work\Person.xlsx',
CREDENTIAL = [ExcelCredential])
GO
/**************************/

Then, retry create external table.
/**************************/
CREATE EXTERNAL TABLE [extExcel_Person](
[RowID] [int] NOT NULL,
[PersonID] [varchar](10) NOT NULL,
[Name] [varchar](100) NOT NULL,
[CorpCode] [varchar](2) NOT NULL,
[PostCode] [varchar](6) NOT NULL)
WITH
(LOCATION = 'Sheet1',DATA_SOURCE = [ExcelInstance])
GO
/**************************/

I got a different error content.

メッセージ 105082、レベル 16、状態 1、行 171
105082; 汎用 ODBC エラー: [Microsoft][ODBC Excel Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x814 Thread 0x3e14 DBC 0xda7b6ad8 Excel'. Additional error <2>: ErrorMsg: [Microsoft][ODBC Excel Driver]Invalid connection string attribute SERVER, SqlState: 01S00, NativeError: 8 Additional error <3>: ErrorMsg: [Microsoft][ODBC Excel Driver]Invalid connection string attribute excel file, SqlState: 01S00, NativeError: 8 Additional error <4>: ErrorMsg: [Microsoft][ODBC Excel Driver]Invalid connection string attribute SERVER, SqlState: 01S00, NativeError: 8 Additional error <5>: ErrorMsg: [Microsoft][ODBC Excel Driver]Invalid connection string attribute excel file, SqlState: 01S00, NativeError: 8 。

Well. I check the error log using T-SQL
/**************************/
SELECT *
FROM [DWDiagnostics].[dbo].[pdw_errors]
ORDER BY [DateTimePublished] DESC
/**************************/

I got this error.
EngineInstrumentation:EngineExecuteQueryErrorEvent

Microsoft.SqlServer.DataWarehouse.Common.ErrorHandling.MppSqlException: 105082; 汎用 ODBC エラー: [Microsoft][ODBC Excel Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x814 Thread 0x3e14 DBC 0xda7b6ad8 Excel'. Additional error <2>: ErrorMsg: [Microsoft][ODBC Excel Driver]Invalid connection string attribute SERVER, SqlState: 01S00, NativeError: 8 Additional error <3>: ErrorMsg: [Microsoft][ODBC Excel Driver]Invalid connection string attribute excel file, SqlState: 01S00, NativeError: 8 Additional error <4>: ErrorMsg: [Microsoft][ODBC Excel Driver]Invalid connection string attribute SERVER, SqlState: 01S00, NativeError: 8 Additional error <5>: ErrorMsg: [Microsoft][ODBC Excel Driver]Invalid connection string attribute excel file, SqlState: 01S00, NativeError: 8 。
場所 Microsoft.SqlServer.DataWarehouse.Engine.Server.ServerInterface.Execute(IQueryContext queryCtx, Boolean isSubBatch, Boolean isBatchInProgress)
場所 Microsoft.SqlServer.DataWarehouse.Engine.Server.ServerInterface.OnExecuteRequest(IClientInterface client, String query, Boolean isInBatch, StatementExecutionContext executionContext, IDictionary`2 replacementInfos, String database, Statement pregeneratedStatement, SqlFrontEndRequest request)

The error message is [Invalid connection string attribute excel file]..
So, I re-change the [CONNECTION_OPTIONS] value.

CONNECTION_OPTIONS ='DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DSN=MyExcel',

After all it was impossible.

Microsoft.SqlServer.DataWarehouse.Common.ErrorHandling.MppSqlException: 105082; 汎用 ODBC エラー: [Microsoft][ODBC Excel Driver]Optional feature not implemented 。
場所 Microsoft.SqlServer.DataWarehouse.DataMovement.Common.ExternalAccess.OdbcUtil.ThrowMppSqlException(String message, NativeOdbcConnection connection)
場所 Microsoft.SqlServer.DataWarehouse.DataMovement.Common.ExternalAccess.OdbcUtil.GetTableType(String connectionString, Int32 connectionLoginTimeout, String catalogNameArgument, String schemaNameArgument, String tableNameArgument, List`1& fieldNameList, Int16[]& dataTypeArray, Int32[]& columnSizeArray, Int32[]& bufferLengthArray, Int16[]& decimalDigitsArray, Int16[]& nullableArray, Int32[]& ordinalPositionArray, List`1& typeNameList)
場所 Microsoft.SqlServer.DataWarehouse.DataMovement.Common.ExternalAccess.OdbcMetadataProcessor.GetTableType(String tableName)
場所 Microsoft.SqlServer.DataWarehouse.DataMovement.Common.ExternalAccess.OdbcMetadataProcessor.get_TableType()
場所 Microsoft.SqlServer.DataWarehouse.Sql.Statements.ExternalGenericTable.ValidateExternalSchema()
場所 Microsoft.SqlServer.DataWarehouse.Sql.Statements.ExternalGenericTableFactory.GetExternalGenericTable(String sourceLocation, Object[] constructorArgs)
場所 Microsoft.SqlServer.DataWarehouse.Sql.Statements.ExternalGenericTable.ObtainTableStatsForExternalGenericTable(ExternalSourceIdentification externalDataSourceInfo, ExternalFileFormatIdentification externalFileFormatInfo, Int32 rowSizeEstimate, String databaseName, String schemaName, String name, RejectOptions rejectOptions, String externalLocation, IList`1 columns, Boolean createIfNotFound)
場所 Microsoft.SqlServer.DataWarehouse.Sql.Statements.CreateExternalTableStatement.ValidateForSQLServer(ExternalSourceIdentification externalDataSourceInfo, ExternalFileFormatIdentification externalFileFormatInfo)
場所 Microsoft.SqlServer.DataWarehouse.Sql.Statements.SqlFrontEnd.ValidateExternalDdlStatement.ValidateForSQLServer()
場所 Microsoft.SqlServer.DataWarehouse.Engine.Processors.SqlFrontEndWorkProcessor.ValidateExternalDdlHandler(ISessionContext sessionContext, SqlFrontEndWorkStatement statement, ICancelableExecutionUnit cancelUnit)
場所 Microsoft.SqlServer.DataWarehouse.Engine.Processors.SqlFrontEndWorkProcessor.OnExecuteRequest()
場所 Microsoft.SqlServer.DataWarehouse.Engine.Utils.EventUtils.PublishApplicationEventAndExecute(ApplicationEventTrigger beginTrigger, ApplicationEventTrigger endTrigger, ApplicationEventTrigger errorTrigger, ApplicationEventTrigger cancelTrigger, PublishedEventPayloadDelegate payload, Action callback)
場所 Microsoft.SqlServer.DataWarehouse.Engine.Processors.AbstractProcessor.OnProcess()
場所 Microsoft.SqlServer.DataWarehouse.Engine.Processors.AbstractProcessor.OnExecute()
- 佳易楊
  Copper Contributor
  Nov 03, 2018
  Microsoft.SqlServer.DataWarehouse.DataMovement.Common.ExternalAccess.OdbcUtil.GetTableType()
  
  ok. I look this dll code.
  
  public static void GetTableType(string connectionString, int connectionLoginTimeout, string catalogNameArgument, string schemaNameArgument, string tableNameArgument, out List<string> fieldNameList, out short[] dataTypeArray, out int[] columnSizeArray, out int[] bufferLengthArray, out short[] decimalDigitsArray, out short[] nullableArray, out int[] ordinalPositionArray, out List<string> typeNameList)
  {
      //IL_0151: Expected O, but got Unknown
      if (connectionString == null)
      {
          throw new ArgumentNullException("connectionString");
      }
      NativeOdbcConnection nativeOdbcConnection = new NativeOdbcConnection(connectionString, CreateOptionFlags.ReadExternal, string.Empty);
      nativeOdbcConnection.ConnectionLoginTimeout = connectionLoginTimeout;
      nativeOdbcConnection.EnableConnectionPooling = true;
      using (NativeOdbcConnection nativeOdbcConnection2 = nativeOdbcConnection)
      {
          IntPtr fieldNames = IntPtr.Zero;
          IntPtr dataType = IntPtr.Zero;
          IntPtr columnSize = IntPtr.Zero;
          IntPtr bufferLength = IntPtr.Zero;
          IntPtr decimalDigits = IntPtr.Zero;
          IntPtr nullable = IntPtr.Zero;
          IntPtr ordinalPosition = IntPtr.Zero;
          IntPtr typeNames = IntPtr.Zero;
          int numFields = 0;
          try
          {
              nativeOdbcConnection2.Open();
              if (!DmsNative.OdbcGetTableType(nativeOdbcConnection2.OdbcConnHandle, catalogNameArgument, schemaNameArgument, tableNameArgument, out fieldNames, out dataType, out columnSize, out bufferLength, out decimalDigits, out nullable, out ordinalPosition, out typeNames, out numFields))
              {
                  ThrowMppSqlException("OdbcUtil.GetTableType, error in OdbcGetTableType", nativeOdbcConnection2);
              }
              fieldNameList = MarshalIntPtrToStringList(fieldNames, numFields);
              dataTypeArray = new short[numFields];
              Marshal.Copy(dataType, dataTypeArray, 0, numFields);
              columnSizeArray = new int[numFields];
              Marshal.Copy(columnSize, columnSizeArray, 0, numFields);
              bufferLengthArray = new int[numFields];
              Marshal.Copy(bufferLength, bufferLengthArray, 0, numFields);
              decimalDigitsArray = new short[numFields];
              Marshal.Copy(decimalDigits, decimalDigitsArray, 0, numFields);
              nullableArray = new short[numFields];
              Marshal.Copy(nullable, nullableArray, 0, numFields);
              ordinalPositionArray = new int[numFields];
              Marshal.Copy(ordinalPosition, ordinalPositionArray, 0, numFields);
              typeNameList = MarshalIntPtrToStringList(typeNames, numFields);
              if (numFields == 0)
              {
                  ErrorUtil.GenerateUserVisibleException(SR.TableDoesNotExist(tableNameArgument));
              }
          }
          catch (DmsSqlNativeException val)
          {
              DmsSqlNativeException ex = val;
              throw GenerateUserVisibleException(ex);
          }
          finally
          {
              FreeIntPtrArray(fieldNames, numFields);
              Marshal.FreeCoTaskMem(dataType);
              Marshal.FreeCoTaskMem(columnSize);
              Marshal.FreeCoTaskMem(bufferLength);
              Marshal.FreeCoTaskMem(decimalDigits);
              Marshal.FreeCoTaskMem(nullable);
              Marshal.FreeCoTaskMem(ordinalPosition);
              FreeIntPtrArray(typeNames, numFields);
              nativeOdbcConnection2.Close();
          }
      }
  }
  - 佳易楊
    Copper Contributor
    Nov 03, 2018
    if (!DmsNative.OdbcGetTableType(nativeOdbcConnection2.OdbcConnHandle, catalogNameArgument, schemaNameArgument, tableNameArgument, out fieldNames, out dataType, out columnSize, out bufferLength, out decimalDigits, out nullable, out ordinalPosition, out typeNames, out numFields))
                {
                    ThrowMppSqlException("OdbcUtil.GetTableType, error in OdbcGetTableType", nativeOdbcConnection2);
                }
    
    The place where the error occurred is here.↑
    ThrowMppSqlException()
    
    SQL Server 2019 CTP2.0 is not support Excel ODBC x64?
佳易楊
Copper Contributor
Nov 04, 2018
Okey.
I am use CDATA EXCEL ODBC Driver x64..Fixed it.
https://www.cdata.com/drivers/excel/download/odbc/

Uninstall Microsoft Access Database Engine 2016 Redistributable..
Install CDATA EXCEL ODBC Driver Version: 18.0.6817.

Thanks!
- 善博河端
  Steel Contributor
  Nov 04, 2018
  Nice CDATA Excel ODBC driver for connect from SQL Server 2019 to Excel file as External table.
  and I hope the Microsoft ODBC driver for it, for share with friends/customers/partners.
  because SQL Server 2019 build by Microsoft, Excel build by Microsoft, External table to ODBC is new feature of SQL Server 2019.

Forum Discussion

How can I use Excel from SQL Server 2019 with PolyBase?

Share

Resources