SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-281912%22%20slang%3D%22en-US%22%3EHow%20can%20I%20use%20Excel%20from%20SQL%20Server%202019%20with%20PolyBase%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281912%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3EI%20want%20to%20know%20How%20can%20I%20use%20Excel%20from%20SQL%20Server%202019%20with%20PolyBase%3F%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3Ewhich%20ODBC%20driver%20string%20do%20I%20need%20to%20use%20for%20connecting%20Excel%20file%20with%20External%20Data%20Source%3F%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3Eand%20anyone%20can%20do%20it%3F%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CBR%20%2F%3EReference%3A%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fpolybase%2Fpolybase-configure-odbc-generic%3Fview%3Dsqlallproducts-allversions%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3EConfigure%20PolyBase%20to%20access%20external%20data%20in%20SQL%20Server%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F58973iEFD41E26A22E9EF9%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%221.png%22%20title%3D%221.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-281912%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eexternal%20data%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPolyBase%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-282074%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20use%20Excel%20from%20SQL%20Server%202019%20with%20PolyBase%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-282074%22%20slang%3D%22en-US%22%3ENice%20CDATA%20Excel%20ODBC%20driver%20for%20connect%20from%20SQL%20Server%202019%20to%20Excel%20file%20as%20External%20table.%3CBR%20%2F%3Eand%20I%20hope%20the%20Microsoft%20ODBC%20driver%20for%20it%2C%20for%20share%20with%20friends%2Fcustomers%2Fpartners.%3CBR%20%2F%3Ebecause%20SQL%20Server%202019%20build%20by%20Microsoft%2C%20Excel%20build%20by%20Microsoft%2C%20External%20table%20to%20ODBC%20is%20new%20feature%20of%20SQL%20Server%202019.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281990%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20use%20Excel%20from%20SQL%20Server%202019%20with%20PolyBase%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281990%22%20slang%3D%22en-US%22%3E%3CP%3EOkey.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20use%20CDATA%20EXCEL%20ODBC%20Driver%20x64..Fixed%20it.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.cdata.com%2Fdrivers%2Fexcel%2Fdownload%2Fodbc%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.cdata.com%2Fdrivers%2Fexcel%2Fdownload%2Fodbc%2F%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUninstall%26nbsp%3BMicrosoft%20Access%20Database%20Engine%202016%20Redistributable..%3C%2FP%3E%3CP%3EInstall%26nbsp%3B%3CSPAN%3ECDATA%20EXCEL%20ODBC%20Driver%26nbsp%3B%3C%2FSPAN%3EVersion%3A%2018.0.6817.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281923%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20use%20Excel%20from%20SQL%20Server%202019%20with%20PolyBase%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281923%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3Eif%20(!DmsNative.OdbcGetTableType(nativeOdbcConnection2.OdbcConnHandle%2C%20catalogNameArgument%2C%20schemaNameArgument%2C%20tableNameArgument%2C%20out%20fieldNames%2C%20out%20dataType%2C%20out%20columnSize%2C%20out%20bufferLength%2C%20out%20decimalDigits%2C%20out%20nullable%2C%20out%20ordinalPosition%2C%20out%20typeNames%2C%20out%20numFields))%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%7B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BThrowMppSqlException(%22OdbcUtil.GetTableType%2C%20error%20in%20OdbcGetTableType%22%2C%20nativeOdbcConnection2)%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%7D%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20place%20where%20the%20error%20occurred%20is%20here.%E2%86%91%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThrowMppSqlException()%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ESQL%20Server%202019%20CTP2.0%20is%20not%20support%20Excel%20ODBC%20x64%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281922%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20use%20Excel%20from%20SQL%20Server%202019%20with%20PolyBase%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281922%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EMicrosoft.SqlServer.DataWarehouse.DataMovement.Common.ExternalAccess.OdbcUtil.GetTableType()%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Eok.%20I%20look%20this%26nbsp%3B%20dll%20code.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Epublic%20static%20void%20GetTableType(string%20connectionString%2C%20int%20connectionLoginTimeout%2C%20string%20catalogNameArgument%2C%20string%20schemaNameArgument%2C%20string%20tableNameArgument%2C%20out%20List%3CSTRING%3E%20fieldNameList%2C%20out%20short%5B%5D%20dataTypeArray%2C%20out%20int%5B%5D%20columnSizeArray%2C%20out%20int%5B%5D%20bufferLengthArray%2C%20out%20short%5B%5D%20decimalDigitsArray%2C%20out%20short%5B%5D%20nullableArray%2C%20out%20int%5B%5D%20ordinalPositionArray%2C%20out%20List%3CSTRING%3E%20typeNameList)%3CBR%20%2F%3E%7B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2F%2FIL_0151%3A%20Expected%20O%2C%20but%20got%20Unknown%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3Bif%20(connectionString%20%3D%3D%20null)%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%7B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3Bthrow%20new%20ArgumentNullException(%22connectionString%22)%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BNativeOdbcConnection%20nativeOdbcConnection%20%3D%20new%20NativeOdbcConnection(connectionString%2C%20CreateOptionFlags.ReadExternal%2C%20string.Empty)%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BnativeOdbcConnection.ConnectionLoginTimeout%20%3D%20connectionLoginTimeout%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BnativeOdbcConnection.EnableConnectionPooling%20%3D%20true%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3Busing%20(NativeOdbcConnection%20nativeOdbcConnection2%20%3D%20nativeOdbcConnection)%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%7B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BIntPtr%20fieldNames%20%3D%20IntPtr.Zero%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BIntPtr%20dataType%20%3D%20IntPtr.Zero%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BIntPtr%20columnSize%20%3D%20IntPtr.Zero%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BIntPtr%20bufferLength%20%3D%20IntPtr.Zero%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BIntPtr%20decimalDigits%20%3D%20IntPtr.Zero%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BIntPtr%20nullable%20%3D%20IntPtr.Zero%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BIntPtr%20ordinalPosition%20%3D%20IntPtr.Zero%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BIntPtr%20typeNames%20%3D%20IntPtr.Zero%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3Bint%20numFields%20%3D%200%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3Btry%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%7B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BnativeOdbcConnection2.Open()%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3Bif%20(!DmsNative.OdbcGetTableType(nativeOdbcConnection2.OdbcConnHandle%2C%20catalogNameArgument%2C%20schemaNameArgument%2C%20tableNameArgument%2C%20out%20fieldNames%2C%20out%20dataType%2C%20out%20columnSize%2C%20out%20bufferLength%2C%20out%20decimalDigits%2C%20out%20nullable%2C%20out%20ordinalPosition%2C%20out%20typeNames%2C%20out%20numFields))%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%7B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BThrowMppSqlException(%22OdbcUtil.GetTableType%2C%20error%20in%20OdbcGetTableType%22%2C%20nativeOdbcConnection2)%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BfieldNameList%20%3D%20MarshalIntPtrToStringList(fieldNames%2C%20numFields)%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BdataTypeArray%20%3D%20new%20short%5BnumFields%5D%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BMarshal.Copy(dataType%2C%20dataTypeArray%2C%200%2C%20numFields)%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BcolumnSizeArray%20%3D%20new%20int%5BnumFields%5D%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BMarshal.Copy(columnSize%2C%20columnSizeArray%2C%200%2C%20numFields)%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BbufferLengthArray%20%3D%20new%20int%5BnumFields%5D%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BMarshal.Copy(bufferLength%2C%20bufferLengthArray%2C%200%2C%20numFields)%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BdecimalDigitsArray%20%3D%20new%20short%5BnumFields%5D%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BMarshal.Copy(decimalDigits%2C%20decimalDigitsArray%2C%200%2C%20numFields)%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BnullableArray%20%3D%20new%20short%5BnumFields%5D%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BMarshal.Copy(nullable%2C%20nullableArray%2C%200%2C%20numFields)%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BordinalPositionArray%20%3D%20new%20int%5BnumFields%5D%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BMarshal.Copy(ordinalPosition%2C%20ordinalPositionArray%2C%200%2C%20numFields)%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BtypeNameList%20%3D%20MarshalIntPtrToStringList(typeNames%2C%20numFields)%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3Bif%20(numFields%20%3D%3D%200)%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%7B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BErrorUtil.GenerateUserVisibleException(SR.TableDoesNotExist(tableNameArgument))%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3Bcatch%20(DmsSqlNativeException%20val)%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%7B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BDmsSqlNativeException%20ex%20%3D%20val%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3Bthrow%20GenerateUserVisibleException(ex)%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3Bfinally%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%7B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BFreeIntPtrArray(fieldNames%2C%20numFields)%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BMarshal.FreeCoTaskMem(dataType)%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BMarshal.FreeCoTaskMem(columnSize)%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BMarshal.FreeCoTaskMem(bufferLength)%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BMarshal.FreeCoTaskMem(decimalDigits)%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BMarshal.FreeCoTaskMem(nullable)%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BMarshal.FreeCoTaskMem(ordinalPosition)%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BFreeIntPtrArray(typeNames%2C%20numFields)%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BnativeOdbcConnection2.Close()%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%7D%3CBR%20%2F%3E%7D%3C%2FSTRING%3E%3C%2FSTRING%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281915%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20use%20Excel%20from%20SQL%20Server%202019%20with%20PolyBase%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281915%22%20slang%3D%22en-US%22%3E%3CP%3EHey..%3C%2FP%3E%3CP%3EI%20want%20to%20know%20too..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%26nbsp%3B%20a%20T-SQL%20code..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%2F**************************%2F%3C%2FP%3E%3CP%3EUSE%20%5BPolyBaseMSSQL_MySQL_Excel%5D%3CBR%20%2F%3EGO%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECREATE%20DATABASE%20SCOPED%20CREDENTIAL%20%5BExcelCredential%5D%3CBR%20%2F%3EWITH%20IDENTITY%20%3D%20'MyExcel'%2C%20Secret%20%3D%20''%3CBR%20%2F%3EGO%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eselect%20*%3CBR%20%2F%3Efrom%20sys.database_scoped_credentials%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E--Using%20%5BMicrosoft%20Excel%20Driver%20(*.xls%2C%20*.xlsx%2C%20*.xlsm%2C%20*.xlsb)%20Ver16.00%20x64%5D%3CBR%20%2F%3ECREATE%20EXTERNAL%20DATA%20SOURCE%20%5BExcelInstance%5D%3CBR%20%2F%3EWITH%20(%3CBR%20%2F%3ELOCATION%20%3D%20'odbc%3A%2F%2F%3CHOSTNAME%3E'%2C%3CBR%20%2F%3ECONNECTION_OPTIONS%20%3D'DRIVER%3D%7BMicrosoft%20Excel%20Driver%20(*.xls%2C%20*.xlsx%2C%20*.xlsm%2C%20*.xlsb)%7D%3BDSN%3DMyExcel'%2C%3CBR%20%2F%3ECREDENTIAL%20%3D%20%5BExcelCredential%5D)%3CBR%20%2F%3EGO%3C%2FHOSTNAME%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eselect%20*%3CBR%20%2F%3Efrom%20sys.external_data_sources%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E--Run%20this%20then%20Error!!!!%3CBR%20%2F%3ECREATE%20EXTERNAL%20TABLE%20%5BextExcel_Person%5D(%3CBR%20%2F%3E%5BRowID%5D%20%5Bint%5D%20NOT%20NULL%2C%3CBR%20%2F%3E%5BPersonID%5D%20%5Bvarchar%5D(10)%20NOT%20NULL%2C%3CBR%20%2F%3E%5BName%5D%20%5Bvarchar%5D(100)%20NOT%20NULL%2C%3CBR%20%2F%3E%5BCorpCode%5D%20%5Bvarchar%5D(2)%20NOT%20NULL%2C%3CBR%20%2F%3E%5BPostCode%5D%20%5Bvarchar%5D(6)%20NOT%20NULL)%3CBR%20%2F%3EWITH%3CBR%20%2F%3E(LOCATION%20%3D%20'Sheet1'%2CDATA_SOURCE%20%3D%20%5BExcelInstance%5D)%3CBR%20%2F%3EGO%3C%2FP%3E%3CP%3E%3CSPAN%3E%2F**************************%2F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20got%20this%20error.%3C%2FP%3E%3CP%3E%E3%83%A1%E3%83%83%E3%82%BB%E3%83%BC%E3%82%B8%20105082%E3%80%81%E3%83%AC%E3%83%99%E3%83%AB%2016%E3%80%81%E7%8A%B6%E6%85%8B%201%E3%80%81%E8%A1%8C%20170%3CBR%20%2F%3E105082%3B%20%E6%B1%8E%E7%94%A8%20ODBC%20%E3%82%A8%E3%83%A9%E3%83%BC%3A%20%5BMicrosoft%5D%5BODBC%20Excel%20Driver%5DOptional%20feature%20not%20implemented%20%E3%80%82%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20I%20try%20change%E3%80%8CCONNECTION_OPTIONS%E3%80%8Dvalue.%3C%2FP%3E%3CP%3E%3CSPAN%3E%2F**************************%2F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EUSE%20%5BPolyBaseMSSQL_MySQL_Excel%5D%3CBR%20%2F%3EGO%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDROP%20EXTERNAL%20DATA%20SOURCE%20%5BExcelInstance%5D%3CBR%20%2F%3EGO%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECREATE%20EXTERNAL%20DATA%20SOURCE%20%5BExcelInstance%5D%3CBR%20%2F%3EWITH%20(%3CBR%20%2F%3ELOCATION%20%3D%20'odbc%3A%2F%2F%3CHOSTNAME%3E'%2C%3CBR%20%2F%3ECONNECTION_OPTIONS%20%3D'DRIVER%3D%7BMicrosoft%20Excel%20Driver%20(*.xls%2C%20*.xlsx%2C%20*.xlsm%2C%20*.xlsb)%7D%3BExcel%20File%3DC%3A%5Cwork%5CPerson.xlsx'%2C%3CBR%20%2F%3ECREDENTIAL%20%3D%20%5BExcelCredential%5D)%3CBR%20%2F%3EGO%3C%2FHOSTNAME%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%2F**************************%2F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThen%2C%20retry%20create%20external%20table.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%2F**************************%2F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ECREATE%20EXTERNAL%20TABLE%20%5BextExcel_Person%5D(%3CBR%20%2F%3E%5BRowID%5D%20%5Bint%5D%20NOT%20NULL%2C%3CBR%20%2F%3E%5BPersonID%5D%20%5Bvarchar%5D(10)%20NOT%20NULL%2C%3CBR%20%2F%3E%5BName%5D%20%5Bvarchar%5D(100)%20NOT%20NULL%2C%3CBR%20%2F%3E%5BCorpCode%5D%20%5Bvarchar%5D(2)%20NOT%20NULL%2C%3CBR%20%2F%3E%5BPostCode%5D%20%5Bvarchar%5D(6)%20NOT%20NULL)%3CBR%20%2F%3EWITH%3CBR%20%2F%3E(LOCATION%20%3D%20'Sheet1'%2CDATA_SOURCE%20%3D%20%5BExcelInstance%5D)%3CBR%20%2F%3EGO%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%2F**************************%2F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3BI%20got%20a%20different%20error%20content.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%E3%83%A1%E3%83%83%E3%82%BB%E3%83%BC%E3%82%B8%20105082%E3%80%81%E3%83%AC%E3%83%99%E3%83%AB%2016%E3%80%81%E7%8A%B6%E6%85%8B%201%E3%80%81%E8%A1%8C%20171%3CBR%20%2F%3E105082%3B%20%E6%B1%8E%E7%94%A8%20ODBC%20%E3%82%A8%E3%83%A9%E3%83%BC%3A%20%5BMicrosoft%5D%5BODBC%20Excel%20Driver%5DGeneral%20error%20Unable%20to%20open%20registry%20key%20Temporary%20(volatile)%20Ace%20DSN%20for%20process%200x814%20Thread%200x3e14%20DBC%200xda7b6ad8%20Excel'.%20Additional%20error%20%26lt%3B2%26gt%3B%3A%20ErrorMsg%3A%20%5BMicrosoft%5D%5BODBC%20Excel%20Driver%5DInvalid%20connection%20string%20attribute%20SERVER%2C%20SqlState%3A%2001S00%2C%20NativeError%3A%208%20Additional%20error%20%26lt%3B3%26gt%3B%3A%20ErrorMsg%3A%20%5BMicrosoft%5D%5BODBC%20Excel%20Driver%5DInvalid%20connection%20string%20attribute%20excel%20file%2C%20SqlState%3A%2001S00%2C%20NativeError%3A%208%20Additional%20error%20%26lt%3B4%26gt%3B%3A%20ErrorMsg%3A%20%5BMicrosoft%5D%5BODBC%20Excel%20Driver%5DInvalid%20connection%20string%20attribute%20SERVER%2C%20SqlState%3A%2001S00%2C%20NativeError%3A%208%20Additional%20error%20%26lt%3B5%26gt%3B%3A%20ErrorMsg%3A%20%5BMicrosoft%5D%5BODBC%20Excel%20Driver%5DInvalid%20connection%20string%20attribute%20excel%20file%2C%20SqlState%3A%2001S00%2C%20NativeError%3A%208%20%E3%80%82%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EWell.%20I%20check%20the%20error%20log%20using%20T-SQL%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%2F**************************%2F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ESELECT%20*%3CBR%20%2F%3EFROM%20%5BDWDiagnostics%5D.%5Bdbo%5D.%5Bpdw_errors%5D%3CBR%20%2F%3EORDER%20BY%20%5BDateTimePublished%5D%20DESC%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%2F**************************%2F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20got%20this%20error.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EEngineInstrumentation%3AEngineExecuteQueryErrorEvent%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EMicrosoft.SqlServer.DataWarehouse.Common.ErrorHandling.MppSqlException%3A%20105082%3B%20%E6%B1%8E%E7%94%A8%20ODBC%20%E3%82%A8%E3%83%A9%E3%83%BC%3A%20%5BMicrosoft%5D%5BODBC%20Excel%20Driver%5DGeneral%20error%20Unable%20to%20open%20registry%20key%20Temporary%20(volatile)%20Ace%20DSN%20for%20process%200x814%20Thread%200x3e14%20DBC%200xda7b6ad8%20Excel'.%20Additional%20error%20%26lt%3B2%26gt%3B%3A%20ErrorMsg%3A%20%5BMicrosoft%5D%5BODBC%20Excel%20Driver%5DInvalid%20connection%20string%20attribute%20SERVER%2C%20SqlState%3A%2001S00%2C%20NativeError%3A%208%20Additional%20error%20%26lt%3B3%26gt%3B%3A%20ErrorMsg%3A%20%5BMicrosoft%5D%5BODBC%20Excel%20Driver%5DInvalid%20connection%20string%20attribute%20excel%20file%2C%20SqlState%3A%2001S00%2C%20NativeError%3A%208%20Additional%20error%20%26lt%3B4%26gt%3B%3A%20ErrorMsg%3A%20%5BMicrosoft%5D%5BODBC%20Excel%20Driver%5DInvalid%20connection%20string%20attribute%20SERVER%2C%20SqlState%3A%2001S00%2C%20NativeError%3A%208%20Additional%20error%20%26lt%3B5%26gt%3B%3A%20ErrorMsg%3A%20%5BMicrosoft%5D%5BODBC%20Excel%20Driver%5DInvalid%20connection%20string%20attribute%20excel%20file%2C%20SqlState%3A%2001S00%2C%20NativeError%3A%208%20%E3%80%82%3CBR%20%2F%3E%E5%A0%B4%E6%89%80%20Microsoft.SqlServer.DataWarehouse.Engine.Server.ServerInterface.Execute(IQueryContext%20queryCtx%2C%20Boolean%20isSubBatch%2C%20Boolean%20isBatchInProgress)%3CBR%20%2F%3E%E5%A0%B4%E6%89%80%20Microsoft.SqlServer.DataWarehouse.Engine.Server.ServerInterface.OnExecuteRequest(IClientInterface%20client%2C%20String%20query%2C%20Boolean%20isInBatch%2C%20StatementExecutionContext%20executionContext%2C%20IDictionary%602%20replacementInfos%2C%20String%20database%2C%20Statement%20pregeneratedStatement%2C%20SqlFrontEndRequest%20request)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20error%20message%20is%20%5BInvalid%20connection%20string%20attribute%20excel%20file%5D..%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ESo%2C%20I%26nbsp%3B%3C%2FSPAN%3Ere-change%20the%20%5B%3CSPAN%3ECONNECTION_OPTIONS%3C%2FSPAN%3E%5D%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECONNECTION_OPTIONS%20%3D'DRIVER%3D%7BMicrosoft%20Excel%20Driver%20(*.xls%2C%20*.xlsx%2C%20*.xlsm%2C%20*.xlsb)%7D%3BDSN%3DMyExcel'%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20all%20it%20was%20impossible.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMicrosoft.SqlServer.DataWarehouse.Common.ErrorHandling.MppSqlException%3A%20105082%3B%20%E6%B1%8E%E7%94%A8%20ODBC%20%E3%82%A8%E3%83%A9%E3%83%BC%3A%20%5BMicrosoft%5D%5BODBC%20Excel%20Driver%5DOptional%20feature%20not%20implemented%20%E3%80%82%3CBR%20%2F%3E%E5%A0%B4%E6%89%80%20Microsoft.SqlServer.DataWarehouse.DataMovement.Common.ExternalAccess.OdbcUtil.ThrowMppSqlException(String%20message%2C%20NativeOdbcConnection%20connection)%3CBR%20%2F%3E%E5%A0%B4%E6%89%80%20Microsoft.SqlServer.DataWarehouse.DataMovement.Common.ExternalAccess.OdbcUtil.GetTableType(String%20connectionString%2C%20Int32%20connectionLoginTimeout%2C%20String%20catalogNameArgument%2C%20String%20schemaNameArgument%2C%20String%20tableNameArgument%2C%20List%601%26amp%3B%20fieldNameList%2C%20Int16%5B%5D%26amp%3B%20dataTypeArray%2C%20Int32%5B%5D%26amp%3B%20columnSizeArray%2C%20Int32%5B%5D%26amp%3B%20bufferLengthArray%2C%20Int16%5B%5D%26amp%3B%20decimalDigitsArray%2C%20Int16%5B%5D%26amp%3B%20nullableArray%2C%20Int32%5B%5D%26amp%3B%20ordinalPositionArray%2C%20List%601%26amp%3B%20typeNameList)%3CBR%20%2F%3E%E5%A0%B4%E6%89%80%20Microsoft.SqlServer.DataWarehouse.DataMovement.Common.ExternalAccess.OdbcMetadataProcessor.GetTableType(String%20tableName)%3CBR%20%2F%3E%E5%A0%B4%E6%89%80%20Microsoft.SqlServer.DataWarehouse.DataMovement.Common.ExternalAccess.OdbcMetadataProcessor.get_TableType()%3CBR%20%2F%3E%E5%A0%B4%E6%89%80%20Microsoft.SqlServer.DataWarehouse.Sql.Statements.ExternalGenericTable.ValidateExternalSchema()%3CBR%20%2F%3E%E5%A0%B4%E6%89%80%20Microsoft.SqlServer.DataWarehouse.Sql.Statements.ExternalGenericTableFactory.GetExternalGenericTable(String%20sourceLocation%2C%20Object%5B%5D%20constructorArgs)%3CBR%20%2F%3E%E5%A0%B4%E6%89%80%20Microsoft.SqlServer.DataWarehouse.Sql.Statements.ExternalGenericTable.ObtainTableStatsForExternalGenericTable(ExternalSourceIdentification%20externalDataSourceInfo%2C%20ExternalFileFormatIdentification%20externalFileFormatInfo%2C%20Int32%20rowSizeEstimate%2C%20String%20databaseName%2C%20String%20schemaName%2C%20String%20name%2C%20RejectOptions%20rejectOptions%2C%20String%20externalLocation%2C%20IList%601%20columns%2C%20Boolean%20createIfNotFound)%3CBR%20%2F%3E%E5%A0%B4%E6%89%80%20Microsoft.SqlServer.DataWarehouse.Sql.Statements.CreateExternalTableStatement.ValidateForSQLServer(ExternalSourceIdentification%20externalDataSourceInfo%2C%20ExternalFileFormatIdentification%20externalFileFormatInfo)%3CBR%20%2F%3E%E5%A0%B4%E6%89%80%20Microsoft.SqlServer.DataWarehouse.Sql.Statements.SqlFrontEnd.ValidateExternalDdlStatement.ValidateForSQLServer()%3CBR%20%2F%3E%E5%A0%B4%E6%89%80%20Microsoft.SqlServer.DataWarehouse.Engine.Processors.SqlFrontEndWorkProcessor.ValidateExternalDdlHandler(ISessionContext%20sessionContext%2C%20SqlFrontEndWorkStatement%20statement%2C%20ICancelableExecutionUnit%20cancelUnit)%3CBR%20%2F%3E%E5%A0%B4%E6%89%80%20Microsoft.SqlServer.DataWarehouse.Engine.Processors.SqlFrontEndWorkProcessor.OnExecuteRequest()%3CBR%20%2F%3E%E5%A0%B4%E6%89%80%20Microsoft.SqlServer.DataWarehouse.Engine.Utils.EventUtils.PublishApplicationEventAndExecute(ApplicationEventTrigger%20beginTrigger%2C%20ApplicationEventTrigger%20endTrigger%2C%20ApplicationEventTrigger%20errorTrigger%2C%20ApplicationEventTrigger%20cancelTrigger%2C%20PublishedEventPayloadDelegate%20payload%2C%20Action%20callback)%3CBR%20%2F%3E%E5%A0%B4%E6%89%80%20Microsoft.SqlServer.DataWarehouse.Engine.Processors.AbstractProcessor.OnProcess()%3CBR%20%2F%3E%E5%A0%B4%E6%89%80%20Microsoft.SqlServer.DataWarehouse.Engine.Processors.AbstractProcessor.OnExecute()%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

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

1.png

5 Replies
Highlighted

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()

 

 

 

Highlighted

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();
        }
    }
}

Highlighted

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?

Highlighted
Solution

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!

Highlighted
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.