Can't import Azure SQL View into Excel.

Question

What I'm trying to do (which I have been able to do many times before until i somehow "broke" Excel). Obviously, I've tried the common reinstall of Office, as well as remove any connections or and data source connections. Issue still persists. Power BI Pro is installed.

What I'm trying to do is add a view from Azure SQL into Excel. From a new fresh install I go into Get Data, Select SQL, type in the server and database name (optional). I don't enter anything else and in the next step am presented with the view to select what tables or views I want. I can preview the tables just fine. I select a view or a table (using database credentials for the dbo owner, just to make sure). And it crashes with an unexpected error: 

Feedback Type:

Frown (Error)

Error Message:

Object reference not set to an instance of an object.

Stack Trace:

   at Microsoft.Mashup.Client.UI.Windows.JsHostedScript.<>c__DisplayClass4.<InvokeScript>b__3()

   at Microsoft.Mashup.Client.UI.Windows.JsErrorHandler.WrapInvokeScript(Func`1 invokeScript)

   at Microsoft.Mashup.Client.UI.Windows.JsHostedScript.TryInvokeT

   at Microsoft.Mashup.Client.UI.Windows.JsHostedScript.TryInvoke(String script)

   at Microsoft.Mashup.Client.UI.Shared.Ux.Navigator.NavigatorHost.DeleteSubTree(TreeNode parent, TreeNode[] subTree)

   at Microsoft.Mashup.Host.Models.Tree.NavigatorModel.InternalDeletingSubTree(TreeNode parent, TreeNode[] subTree)

   at Microsoft.Mashup.Host.Models.Tree.NavigatorModel.OnTreeNodeSubTreeChanged(Object sender, CollectionChangeEventArgs e)

   at Microsoft.Mashup.Host.Models.Tree.TreeNode.OnSubTreeRemoved(TreeNode[] value)

   at Microsoft.Mashup.Host.Models.Tree.TreeNode.set_SubTree(TreeNode[] value)

   at Microsoft.Mashup.Host.Models.Tree.NavigatorModel.Dispose()

   at Microsoft.Mashup.Client.UI.Shared.Ux.FloatingDialog.NavigatorFloatingDialog.Dispose(Boolean disposing)

   at Microsoft.Mashup.Client.UI.Shared.WebDialogs.WebDialogBase`1.Dispose()

   at Microsoft.Mashup.Client.UI.Shared.Ux.FloatingDialog.NavigatorFloatingDialog.ShowEditLoadTo(IWindowHandle owner, IUIHost uiHost, Query query, ITelemetryService telemetryService)

   at Microsoft.Mashup.Client.Excel.ExcelDataImporter.<>c__DisplayClass1c.<ShowNavigator>b__1b(IWorkbook workbook)

   at Microsoft.Mashup.Client.Excel.Shim.NativeCoAuthServices.NotifyGetDataPresence(IWorkbook workbook, Action action)

   at Microsoft.Mashup.Client.Excel.ExcelDataImporter.<>c__DisplayClassf.<InvokeOnWorkbook>b__e(IWorkbook workbook, IWindowContext windowContext)

   at Microsoft.Mashup.Client.Excel.ExcelDataImporter.InvokeOnWorkbookT

   at Microsoft.Mashup.Client.Excel.ExcelDataImporter.ShowNavigator(Query query)

   at Microsoft.Mashup.Client.UI.Shared.DataImporter.ShowNavigatorOnSourceQuery(Query query, IEnumerable`1& importedQueries)

   at Microsoft.Mashup.Client.UI.Shared.DataImporter.ImportNavigationSource(Query query)

   at Microsoft.Mashup.Client.UI.Shared.DataImporter.OnGetPreviewResult(PreviewResult preview, Query query, String sourceID, String formulaTitle, Nullable`1 explicitImportDestination, Boolean isNewQuery, Boolean isFromEditor)

   at Microsoft.Mashup.Client.UI.Shared.DataImporter.GetPreviewResult(Query query, String sourceID, String formulaTitle, Boolean isNewQuery, Boolean isFromEditor, Nullable`1 explicitImportDestination)

   at Microsoft.Mashup.Client.Excel.ExcelDataImporter.<>c__DisplayClass20.<GetPreviewResult>b__1f()

   at Microsoft.Mashup.Client.Excel.Shim.NativeWorkbookStorageServices.Microsoft.Mashup.Client.Excel.Shim.IDeferredStorageInvoker.InvokeDeferredStorageAction(IWorkbook workbook, Action action)

   at Microsoft.Mashup.Client.Excel.Shim.NativeCoAuthServices.InvokeCoauthAction(IWorkbook workbook, UndoableActionType actionType, Action action)

   at Microsoft.Mashup.Client.Excel.Shim.NativeCoAuthServices.NotifyGetDataPresence(IWorkbook workbook, Action action)

   at Microsoft.Mashup.Client.Excel.ExcelDataImporter.<>c__DisplayClassf.<InvokeOnWorkbook>b__e(IWorkbook workbook, IWindowContext windowContext)

   at Microsoft.Mashup.Client.Excel.ExcelDataImporter.InvokeOnWorkbookT

   at Microsoft.Mashup.Client.Excel.ExcelDataImporter.GetPreviewResult(Query query, String sourceID, String formulaTitle, Boolean isNewQuery, Boolean isFromEditor, Nullable`1 explicitImportDestination)

   at Microsoft.Mashup.Client.UI.Shared.DataImporter.<>c__DisplayClass20.<OnQuerySettingsResolved>b__1d()

   at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)

Stack Trace Message:

Object reference not set to an instance of an object.

Invocation Stack Trace:

   at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()

   at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo..ctor(String message, Exception exception)

   at Microsoft.Mashup.Client.Excel.Native.NativeUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)

   at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass1.<HandleException>b__0()

   at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)

   at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)

   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)

   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)

   at System.Delegate.DynamicInvokeImpl(Object[] args)

   at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)

   at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)

   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)

   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)

   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)

   at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)

   at System.Windows.Forms.Control.InvokeMarshaledCallbacks()

   at System.Windows.Forms.Control.WndProc(Message& m)

   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Supports Premium Content:

False

Formulas:

section Section1;

shared #"NavigatorBase_c0a1eff2-8e8b-439e-a449-a6b60f3304be" = let

    Source = Sql.Database("*AZURE DATABASE LOCATION*", "*DATABASE NAME*")

in

    Source;

I'm also getting this sometimes:

Details: "Microsoft SQL: The data source appears to have been modified since it was last accessed. Refreshing may resolve this error."

Please send this to your engineers!!

Answer 1

It seems to resolve itself by Going into Data, Get Data , Data Source Settings and removing all the settings there.

Then adding the connection again. Then trying to import from SQL, but instead of hitting load or edit when the table/view is in preview, hit cancel instead and try loading it again.

There is obviously some properties from connections that "Live on" when trying to delete the global connections.

Answer 2

Hi Agneum,

We appreciate you sharing the solutions for the issue. This will help other community members who may encounter the same issue in the future.

Thanks,

Rena

Answer 3

I've also noticed that upon multiple error messages the following appends to the end:

shared #"NavigatorBase_*Random String*" = let

    Source = Sql.Database("*AZURE DATABASE LOCATION*", "*DATABASE NAME*")

in

    Source;

With a new NavigatorBase value.