SQL formatting and Power Query

Today I got caught out by using SSMSBoost to SQL format a query being used as the source for a Microsoft Power Query.

On updating the stored procedure the query stopped working in Power Query, with the error that the column “Temp” was missing.

Looking at the query output in SSMS, I could see that the column had changed to TEMP in upper case from Temp in the original query, easily fixed. Then I realised what might have happened to cause it and proved it. Formatting the SQL had caused the temp column to be upper cased, my suspicion is that Temp was wrongly identified as a keyword by the formatting template and thus with the setting in SSMSBoost being to uppercase keywords, it got uppercased. Power Query is case sensitive so got upset.

SSMSBoot Settings Menu showing the UPPERCASE keywords = True

Just a warning for those of you that use SQL formatting tooling, one to keep an eye out for, luckily having a column named Temp is not too common.

ClickOnce with report viewer control

Installing SSRS Report Viewer component for Visual Studio 2017 (VS2017) using NUGET

For Visual Studio 2017, the SQL server reporting services, report viewer component, for web forms or windows forms has been put into a NuGet package. This means it can be installed into projects where it is required, with the added benefit of no longer needing a client report viewer runtime msi installer for the client, that previously was used to install the runtime report viewer components into the GAC.

To upgrade a solution from using the old report viewer component, open all the references nodes in the project tree, remove all the old .dll references to the original report viewer .dll files. Then install the NUGET package for the projects in the solutions within the solution that require it.

To install the NuGet package, Right click the project, select Manage NuGet Packages,

Select the Browse option and then search for “ReportViewerControl” as shown.

2018-01-05_12-35-12

Take care to install the Windows Forms or Web Forms version as required and that it is the newer version, it is easy to click the wrong one as there are a few similar products in there.

This will pull down the required .dlls in to the project.

If you need the control in the visual control toolbox, then right click the toolbox area, select “choose items”, then in the .NET Framework Components Tab use the Browse button to browse to the control’s dll. This will be located in the “\packages\Microsoft.ReportingServices.ReportViewerControl.Winforms.140.1000.523\lib\net40” folder for the example where we installed the version shown above (note the version number). The packages folder is usually found with the solution or project files somewhere.  Select the file “Microsoft.ReportViewer.WinForms.dll” within that folder.

This will add the control to the toolbox.

2018-01-05_12-41-26

 

Problems:

I experienced some real problems after this when trying to deploy the application with the ReportViewer control via ClickOnce deployment.

On install of a non-developer machine, various errors occurred.

These were due to the “Microsoft.ReportViewer.Design.dll” library being referenced by the project. This meant the project then had visual studio dependencies introduced. Removing this file caused the application deployment to work again as expected.

"assembly Microsoft.VisualStudio.Text.Logic version 14.0.0.0 be installed in the GAC"

The above was caused by the report viewer design dll being referenced by the project, causing a dependency that was picked up by the ClickOnce manifest generator.

Microsoft.VisualStudio.Text.Logic installed in teh Global Assembly Cache

Assembly Microsoft.VisualStudio.Diagnostics.Assert needs to be installed in the Global Assembly Cache

image

Unable to install or run the application. The application requires that assembly Microsoft.VisualStudio.Diagnostics.Assert Version 14.0.0.0 be installed in the Global Assembly Cache (GAC) first.