SSMS Database Diagram Crash

I was attempting to create a database diagram with the SSMS 2012 diagramming tool today and ran into the following issue:

“Attempted to read or write protected memory. This is often an indication that other memory is corrupt. (Microsoft.VisualStudio.OLE.Interop)”

with the details:

===================================

Attempted to read or write protected memory. This is often an indication that other memory is corrupt. (Microsoft.VisualStudio.OLE.Interop)

——————————

Program Location:

at Microsoft.VisualStudio.OLE.Interop.IOleCommandTarget.QueryStatus(Guid& pguidCmdGroup, UInt32 cCmds, OLECMD[] prgCmds, IntPtr pCmdText)

at Microsoft.VisualStudio.Platform.WindowManagement.DocumentObjectSite.QueryStatus(Guid& pguidCmdGroup, UInt32 cCmds, OLECMD[] prgCmds, IntPtr pCmdText)

at Microsoft.VisualStudio.Platform.WindowManagement.WindowFrame.QueryStatus(Guid& pguidCmdGroup, UInt32 cCmds, OLECMD[] prgCmds, IntPtr pCmdText)

at Microsoft.Internal.VisualStudio.Shell.Interop.IVsTrackSelectionExPrivate.Register()

at Microsoft.VisualStudio.Platform.WindowManagement.WindowFrame.ConnectSelectionContext()

at Microsoft.VisualStudio.Platform.WindowManagement.WindowFrame.Activate()

at Microsoft.VisualStudio.Platform.WindowManagement.WindowManagerService.viewManager_ActiveViewChanged(Object sender, ActiveViewChangedEventArgs e)

at System.EventHandler`1.Invoke(Object sender, TEventArgs e)

at Microsoft.VisualStudio.PlatformUI.ExtensionMethods.RaiseEvent[TEventArgs](EventHandler`1 eventHandler, Object source, TEventArgs args)

at Microsoft.VisualStudio.PlatformUI.Shell.ViewManager.SetActiveView(View view, ActivationType type)

at Microsoft.VisualStudio.Platform.WindowManagement.WindowFrame.ShowInternal(ShowFlags showFlags)

at Microsoft.VisualStudio.Platform.WindowManagement.WindowFrame.<Show>b__26()

at Microsoft.VisualStudio.ErrorHandler.CallWithCOMConvention(Func`1 method)

at Microsoft.VisualStudio.Platform.WindowManagement.WindowFrame.Show()

at Microsoft.VisualStudio.Platform.WindowManagement.WindowFrame.MarshalingWindowFrame.<Microsoft.VisualStudio.Shell.Interop.IVsWindowFrame.Show>b__7a()

at Microsoft.VisualStudio.Shell.ThreadHelper.Invoke[TResult](Func`1 method)

at Microsoft.VisualStudio.Platform.WindowManagement.WindowFrame.MarshalingWindowFrame.Microsoft.VisualStudio.Shell.Interop.IVsWindowFrame.Show()

at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con, String fileName)

at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProject.Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con, String fileName)

at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con, String fileName)

at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.CreateDesignerWindow(IManagedConnection mc, DocumentOptions options)

at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.InvokeDesigner(IManagedConnection connection)

at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.Invoke()

at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ToolsMenuItemBase.MenuInvokedHandler(Object sender, EventArgs args)

So, after some digging, I found a post that suggested I upgrade my tools to SSMS 2014, which yielded the same results, and did not work.

The next post suggestion I found recommended replacing the DLLs in the following location:

C:\Program Files (x86)\Common Files\microsoft shared\Visual Database Tools\

and

C:\Program Files (x86)\Common Files\microsoft shared\MSDesigners8\

Which also did not work.

If anyone knows of a better work around, I’d like some input!

Power View Drill Drown/Up Bug or, by Design?

While rehearsing my demo for my upcoming session at SQL Saturday Boston this weekend, #sqlsat500 where I will be lecturing and demonstrating Scratching the Surface of Power View, I noticed a quirky issue where I wasn’t able to drill through my column data, but was fine with row data.  Further, after some playing, I was able to drill down if I changed the order of my column values, or tried using other fields.  Everyone hates NULL data, and I think this is just another reason why.  As it turns out, there are some NULLs that happen to show up as the first column when you drill down, so the ability drill “up” is lost, and the only way to get back to your top-level data is to close and re-open the report.

The Scenario:

Using the AdventureWorksDW2014 Data, and building a basic example of creating a matrix, then adding the Drill Down properties, my selections look like this:

As you can see.  A fairly simple example, just trying to capture the essence of Drilling Down and back out again.  When I click to drill down in to the “Black” column, I get the following:

Note, the first column heading has no title, and there isn’t an available “Drill Up” arrow either.  At this point, I’m stuck.  I can’t go back up to my original report, and now need to close and re-open to start over.  If I change the order of the columns so that “Style” is listed first, above “Color”, the Drill Down and Drill Up work fine.  In order to workaround the issue, I replaced all the NULLs in the table with “NA” and Viola! Works perfectly.  I’m not sure if this is by design from Microsoft in order to force the data to have values, but since it’s their dataset to begin with, I’m assuming it’s a bug.

Hope this helps anyone else running into this issue!

Wonky Column Headers in SSRS

Have you run in to issues where your SSRS column headers just won’t scroll with the data?  How about losing the column headers when you go to your next page?  Well, there seems to be some ambiguity about this topic, and anyone who has searched for an answer to their problem has probably run into a bunch of rudimentary answers on how to fix said issues.  I’m living proof that said “fixes” don’t seem to always work consistently, and I have gone ahead and created my own set of steps to address the issue at hand.  I can’t promise it will fix your issues, but in every instance I had the problem, it worked.

For starters, I would read the following page from MSDN to get an understanding of how row and column headings work:

https://msdn.microsoft.com/en-us/library/ee240753%28v=sql.110%29.aspx

As you can see, this should be pretty simple, but in reality, I can’t get it to work consistently, and it seems many others have tried and failed.

For starters… these settings in the Tablix Properties just seem to cause more issues than help:

If you modify the “Row Headers” and “Column Headers” properties in here (select them), then you will get the following error when you attempt to run your report later… so don’t use them with this method:

This error will ruin your day when you’re using the advanced properties… trust me… I know!

As referenced in the MSDN page, the way to gain better control over the column headers is to use the advanced properties for Row and Column Groups:

After changing to “Advanced Mode”, you’ll see essentially 2 different types of top row.  Either way, the top row with contain the word “static” the major differentiation will whether or not the word is in parenthesis.

What makes the parenthesis critical is that if they’re missing, it means the header row has been deleted.  If the header row has been deleted, my advice is to re-create the report.  You will spend much less time in recreating it than you will chasing your tail trying to fix it!

On the flip side, if that top row has no parenthesis, you should be able to tackle this issue.

Select your top row and go to your properties window:

The key fields you want to look at here are “FixedData”, “KeepTogether”, “KeepWithGroup”, and “RepeatOnNewPage”.  By default, I typically set FixedData = True; KeepTogether = True; KeepWithGroup = After; and RepeatOnNewPage = True.

From there, if it’s not quite right, you can play with those 4 settings to resolve your issue.  Also, I would pay close attention to the bottom section of the MSDN article with regard to “Renderer Support” as there are only a few particular types of renderings for which the repeating rows and headers will work.

I hope this helps!