Visual Studio Community 2017 – Upgrading

Background

The lead developer on one of our projects experienced problems deploying a report yesterday evening.

It is time to see if we can duplicate and thus research the error.

 

SQL Server Data Tools

SQL Server Data Tools – v2015

Downloaded SQL Server Data Tools (SSDT) v2015 from here.

Was able to successfully install and deploy the report.

 

SQL Server Data Tools – v2017

Download

From same link, downloaded SSDT v2017.

Install

Prerequisite

Visual Studio 2017

Blocking Issue
Textual

The current installation requires Visual Studio 2017 version number 15.3.0 or higher.  Please upgrade your Visual Studio instance before continuing the setup.

Image

 

Validate v2017

Launched our installed v2017 and validated the Version # is 15.2 (26430.15 )

Image

Textual

Microsoft Visual Studio Community 2017
Version 15.2 (26430.15) Release
VisualStudio.15.Release/15.2.0+26430.15
Microsoft .NET Framework
Version 4.7.02053

Review Visual Studio 2017 Packages

Let us quickly review Visual Studio ( VS) 2017 Release History

Image

Explanation
  1. v15.2
    • We have v15.2, released on May 10th, 2017
  2. v15.3
    • We need at least 15.3, released on August 14th, 2017
  3. v15.4.3
    • The latest as of today, 2017-Nov-9th, is v15.4.3
Download Visual Studio 2017 – Bootstrapper

We will to “Install Visual Studio 2017 on low bandwidth or unreliable network environments” ( here  )and reach for the Bootstrapper community edition.

The direct link to Community edition is here.

 

Create an offline installer

Let us create an offline installer


set "_target=C:\VS2017offline"
set "_desktop=Microsoft.VisualStudio.Workload.ManagedDesktop"
set "_netWeb=Microsoft.VisualStudio.Workload.NetWeb"
set "_github=Component.GitHub.VisualStudio"

vs_community.exe --layout %_target% --add %_desktop% --add %_netWeb%  --add %_github% --includeOptional --lang en-US

 

Build offline installer

Run the batch file created earlier to build the offline installer.

Image – Invoke batch file

Image – download…

Image – Successful
Patch Visual Studio 2017

Let us patch our installed v2017

Run the setup.exe availed through the offline installer.

Image – 1

Update available

Visual Studio 2017 is already installed.

Click update to update to version 15.4.3

 

Image – 2

Image – 3

 

Image – 4

 

Image – 5

 

Validate Visual Studio 2017 Version

Launch Visual and validated that v2017 is installed.

 

Install

Steps

Let us go back and try to install SQL Server Data Tools – Release 15.4.0 Preview

Install Tools to this Visual Studio 2017 instance

 

References

  1. Microsoft
    • Docs / SQL / SSDT ( SQL Server Data Tools )
      • Download SQL Server Data Tools ( SSDT )
        Link
    • Docs / Visual Studio / Documentation / Installation
      • Install Visual Studio 2017 on low bandwidth or unreliable network environments
        Link
      • Create an offline installation of Visual Studio 2017
        Link
    • VisualStudio.com
      • Visual Studio 2017 version 15.4 Release Notes
        Link

Transact SQL / Static Code Analysis – Using SSDT

Background

Life is kinda funny.

One leaves it aside, and sit in front of the computer days and nights.

And, occasionally you ponder did you fall asleep and miss some free optimization tricks.

 

Warning – Type Conversion In Expression

On Friday, I found myself looking at some code and was immediately distracted and taken in by the “Yellow Caution Sign” shown below.

SelectWithYellowCaution

 

Looking under the hood, found the problem.

Image

ConversionWarnings

Textual

Type Conversion in Expression  … Convert Implicit

 

Lint / Static Code Analysis

Scope

Yes, I can try looking at the specific Database Object and see about fixing it.

And, if I were a DBA and have access to Dynamic Management Views (DMVs), I can put together a script to check for such warnings in the Plan Cache.

But, I felt I had a bigger burden.  How prevalent are warnings such as this in my entire code base; at least that part of the code base that is in my database; so called programmable objects.  Our list will thus include Stored Procedures, Views, and Functions.

For now, we will turn a blind eye to SQL that is embedded in the source code or auto-generated via ORM (Entity Framework).

 

Tools

Took to Google in search of Transact SQL Lint Tools.

The ones I found and links to the corresponding rules they check for are listed below:

 

 

SQL Server Data Tools

SQL Server Data Tools – v2010

Choice

Like my friend Morris says, “I do mind spending money, I just don’t have any to spend”.

Also same lines, I was not really blindly looking for SQL Lint tools, I was looking for free ones.

 

Launch

Launched SQL Server Data Tools via Start Menu; specifically Microsoft SQL Server 2012 / SQL Server Data Tools:

StartMenu-uncluttered

 

New Project

Let us create a new Project.

As always, we use the menu items File \ New Project:

 

newProject_Lint

 

 

Prompt

Once we click OK, we get the same message, I have received for the last 6 or so months:

The operation you are attempting requires Microsoft SQL Server Data Tools to be installed.

 

Microsoft SQL Server Data Tools (Web Install)

 

When I click on the install link, my default browser arises and I am taken to Data Developer Center – Microsoft SQL Server Data Tools … )( https://msdn.microsoft.com/en-us/data/gg427686 ).

 

Help

And, so like Lionel Richie, I have been stuck, not “On You”, but on a dead end error message.

Since today is Sunday, and I am already playing hookie with the Sabbath, may be, I might as well spend the whole day in front of the computer.

Thanks Goodness, Thomas Larock, SQL Rockstar, had the same problem 2 years in the past:

In his post, he stated that though a our Start Menu, “SQL Server Data Tools” is listed under “SQL Server 2012“, we might be launching the Visual Studio 2010 edition.

 

Confirm

To  confirm:

In the Application, accessed Help/About and got the Window pasted below.

VisualStudio2010Shell

 

Tell Tale Signs:

  • Microsoft Visual Studio 2010 Shell
  • Installed Products
    • Microsoft Visual Basic 2010
    • Microsoft Visual C# 2010
    • ..
    • Microsoft Visual 2010 Shell (Integrated) – ENU Service Pack 1

 

Fix

Which Visual Studio Versions are installed?

Using Windows Explorer, determine the versions of Visual Studio installed.

Here is what is underneath C:\Program Files (x86):

VisualStudioVersions

 

Copied and pasted link

Made a copy of short-cut for “Microsoft SQL Server 2012” \ “SQL Server Data Tools“:

 

StartMenu-Item-Pasted

 

 

Change Link’s target
Changed link from “C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\devenv.exe” to “C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\devenv.exe

 

Visual Studio 2012

About

Let us ensure that we are using VS 2012.

Once the change is made, we launched app (through new short-cut)  and checked version using the menu items (About).

 

About

 

New Project

Here is the “New Project”.

NewProject

 

Thanks goodness, no prompting to download new updates.

 

Import

Let us  import our programmable objects.  Unfortunately not directly from a live database, but from SQL Script files.

ImportSQLScriptFile-SelectSQLScriptFileToImport

 

 

Code Analysis

Let us do a quick Code Analysis.  Using the menu items, “SQL \ Static Code Analysis \ Run”, we attempted a quick analysis:

Here is our result:

 


Running Code Analysis...
		C:\Windows\Microsoft.NET\Framework\v4.0.30319\Vbc.exe /noconfig /optionstrict:custom /nostdlib /errorreport:prompt /rootnamespace:CSPLint /sdkpath:"C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.5" /highentropyva+ /define:"CONFIG=\"Debug\",DEBUG=-1,TRACE=-1,PLATFORM=\"AnyCPU\"" /reference:"C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.5\System.dll" /debug+ /debug:full /optimize- /out:obj\Debug\CSPLint.dll /subsystemversion:6.00 /target:library /warnaserror- "C:\Users\eygp\AppData\Local\Temp\.NETFramework,Version=v4.5.SqlClrAttributes.vb"
		Loading project references...
		Loading project files...
		Building the project model and resolving object interdependencies...
		Validating the project model...
C:\CSPLint\warningImplicitConversion\Stored Procedures\usp_ListInternalCustomersWithUnalignedGrade.sql(4,18): Error:  SQL71501: Procedure: [warningImplicitConversion].[usp_ListInternalCustomersWithUnalignedGrade] has an unresolved reference to Schema [warningImplicitConversion].
C:\CSPLint\warningImplicitConversion\Stored Procedures\usp_ListInternalCustomers.sql(1,18): Error:  SQL71501: Procedure: [warningImplicitConversion].[usp_ListInternalCustomers] has an unresolved reference to Schema [warningImplicitConversion].
C:\CSPLint\warningImplicitConversion\Tables\user.sql(7,14): Error:  SQL71501: Table: [warningImplicitConversion].[user] has an unresolved reference to Schema [warningImplicitConversion].
C:\CSPLint\warningImplicitConversion\Tables\customer.sql(5,14): Error:  SQL71501: Table: [warningImplicitConversion].[customer] has an unresolved reference to Schema [warningImplicitConversion].
Done building project "CSPLint.sqlproj" -- FAILED.

Build FAILED.
Code analysis failed -- 0 error(s), 0 warning(s)

 

Image:

NoSchema

 

The problem is that our schema creation file was not properly interpreted by the tool:

Original File


if schema_id('warningImplicitConversion') is null
begin

   exec ('create schema [warningImplicitConversion] authorization [dbo];')

end
go

Stripped down file:


create schema [warningImplicitConversion] authorization [dbo];
go

 

Import

We went back and re-imported that lone file.

 

Static Code Analysis

 


Running Code Analysis...
		C:\Windows\Microsoft.NET\Framework\v4.0.30319\Vbc.exe /noconfig /optionstrict:custom /nostdlib /errorreport:prompt /rootnamespace:CSPLint /sdkpath:"C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.5" /highentropyva+ /define:"CONFIG=\"Debug\",DEBUG=-1,TRACE=-1,PLATFORM=\"AnyCPU\"" /reference:"C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.5\System.dll" /debug+ /debug:full /optimize- /out:obj\Debug\CSPLint.dll /subsystemversion:6.00 /target:library /warnaserror- "C:\Users\eygp\AppData\Local\Temp\.NETFramework,Version=v4.5.SqlClrAttributes.vb"
		Loading project references...
		Loading project files...
		Building the project model and resolving object interdependencies...
		Validating the project model...
		Writing model to C:\CSPLint\CSPLint\obj\Debug\Model.xml...
		CSPLint -> C:\CSPLint\CSPLint\bin\Debug\CSPLint.dll
				Loading project references...
				Loading project files...
				Building the project model and resolving object interdependencies...
				Validating the project model...
				One problem has been detected.
				The results are saved in C:\CSPLint\CSPLint\bin\Debug\CSPLint.StaticCodeAnalysis.Results.xml.
C:\CSPLint\CSPLint\warningImplicitConversion\Tables\customer.sql(14,22): Warning:  : SR0009 : Microsoft.Rules.Data : Avoid NVARCHAR of only one element.
		CSPLint -> C:\CSPLint\CSPLint\bin\Debug\CSPLint.dacpac
Code analysis complete -- 0 error(s), 1 warning(s)

 

The lone warning states “Warning: : SR0009 : Microsoft.Rules.Data : Avoid NVARCHAR of only one element”

 

What did we expect?

Well, sorry due to the initial prompt that we should download updates, our original intent got lost.

Started down this track to see how much SSDT will help us find Implicit conversions in programmable objects.

Data Model

Here is our data model.

 

DataModel

DDL

Here is what our Stored Procedure looks like:

 

create procedure [warningImplicitConversion].[usp_ListInternalCustomersWithUnalignedGrade]
as

    select
			  tblCust.CUSTOMER_ID
			, tblCust.CUSTOMER_ID
			, tbLcust.firstnameContact
			, tbLcust.lastnameContact

    from   [warningImplicitConversion].[customer] tblCust

                inner join [warningImplicitConversion].[user] tblUser

                    on  tblCust.[firstnameContact] = tblUser.[firstname]
                    and tblCust.[lastnameContact] = tblUser.[lastname]

    where isNull(tblCust.[contactGrade], '0') != isNull(tblUser.[contactGrade], 0)
go

Explanation:

  • We were hoping that by comparing varchar’s defined columns in the user table with nvarchar defined columns in the customer table, we will trigger an implicit conversion error.
  • Along same lines, contactGrade is defined as tinyint in the customer table, but nvarchar(1) in the customer table

 

 

Warning – Shape of Result Set will change:

The Code Analysis tool flags simpler constructs such as “select *” :

Image:

TheShapeofTheResultSetWillChange

 Changed Code:
NoProblemsWereDetected


Static Code Analysis

Via the menu items – SQL / Static Code Analysis / Configure, here are the areas currently covered.

CodeAnalysisConfigure

 

 

Visual Studio 2013

About

We tried using Visual Studio 2013, as well.

VisualStudio2013Shell

 

Error List

But, we are unable to successfully build our project.

ErrorList

 

Quote

Kenny Chesney


    Overwhelming power of dreams 

    For the dreamer, but also people dreaming along

    You don’t give up  

    You keep dreaming … with work, faith, and being willing

     .. and lives people live, right inside your songs

     Remarkable things can happen to pretty average people

     Thank all of you for loving music, and giving all of us a great life.

Summary

Judging from the date listed in my short-cut, I installed SQL Server Data Tools on 6/12/2014.  And, as today is 3/15/2015, the problem has bested me for 9 whole months.

Finally through the public labor of Thomas Larock, SQL Rockstar, was able to have the veil lifted.

 

Microsoft Connect Items

Please consider up-voting these connect items:

References

Rules

Sargable Functions

Music

SQL Server – Engine – Data Export and Import- Error – “The Execute Package utility requires Integration Services to be installed”

Introduction

There is one little hurdle that one might have to cross when one uses “Microsoft SQL Server Management Studio – SQL Server Import and Export Wizard” to import and export data.

Error

The hurdle occurs when one tries to later re-use the package created. If “SQL Server Integration Services” is not installed on the machine, one gets the error pasted below:

Error: The Execute Package utility requires Integration Services to be installed by one of these editions of SQL Server Standard 2008: Standard, Enterprise, Developer, or Evaluation.  To install Integration Services, run SQL Server Setup and select Integration Services.

v2008

RequiresIntegrationServices

 

 

 

 

 

 

 

 

 

 

 

 

 

 

v2014

sqlserversetupandselectbusinessintelligencestudio

The error is straightforward.

Solution:

There are a couple of solutions, inclusive:

  1. Install the latest evaluation version SQL Server and be sure to include “Integration Services“.
  2. Convert the package

Install Evaluation Copy of SQL Server

Download MS SQL Server 2012 – SP1 Trial Edition

Download MS SQL Server 2012 – SP1 Trial Edition

Download Microsoft SQL Server 2012 – SP1 from here.

Install MS SQL Server 2012

Install the downloaded version and make sure that you include Integration Services as part of installation.

Backup package

Make a copy of the package.  Using MS Windows Explored, please make a copy of the package.

Use Project Conversion Wizard

Use “SQL Server 2012 \ Integration Services \ Project Conversion Wizard”

(

     This step did not work for our purpose as it does not produce

          the traditional dtxs/XML file, but a binary file (.ispac); which needs to

          be deployed to SQL Server/msdb or SSIS ENgine

)

Hopefully, you installed the tooling components that come with SQL Server 2012.  The nice tool known as Integration Services \ Project Conversion Wizard comes with it.

Launch Tool

Launch that tool and choose to convert the package

Locate Packages

The Locate Package window allows to choose the package’s location.  Remember a package can be saved on the File System \ SSIS Package Store \ SQL Server (msdb).

locatePackages

Select Packages

Select the packages that you want converted.

SelectPackages

Select Destination

Select the packages destination

Please create and select a new folder to save the new files.

Here you will have to deal with the more contentious aspects of SSIS /DTS packages; what to do with the step child that are packaged as “sensitive data“.

As always, I will skip the hard part by choosing “DontSaveSensitive”.

Select Destination

SelectDestination

Configuration and Parameters

Based on your packages, you might have to deal with configurations and parameters.

Review

Review your choices:

Review

Review

Review your choices.

Review

Result

Please review the result of the conversion process.

results

Close

And, click on the close button.

Execute Package

Launch SQL Server Integration Services Execute Package

Launch “Microsoft SQL Server 2012” / “Integration Services” / “Execute Package Utility”

LaunchExecutePackage

General – General – Load File

General File System

ExecutePackageUtility-General-PackageSource

File System

Empty File System as dtsx file not found!

ChooseFileSystemFile - dtsx

Using IDE ( Business Intelligence Studio [BIDS] / SQL Server Data Tools [SSDT]) Convert Package to new Version

Using SQL Server Business Intelligence BIDS  or SQL Server Data Tools convert package to new version.

We will use SQL Server Data Tools BI.

Download

Download SSDT (Microsoft SQL Server Data Tools – Business Intelligence for Visual Studio 2012) from http://www.microsoft.com/en-us/download/details.aspx?id=36843.

Install SSDT

Install SSDT

Launch SSDT

Launch “SQL Server Data Tools for Visual Studio 2012” (SSDT)

launchSSDT

Open File

Using menu items (File \ Open file) …

The package is automatically updated:

The package is upgraded.
See the list of warning and information messages for details.

ThePackageIsUpdated

Save the “File As”

Use the menu “File / Save As” to save the file under a new name:

SavePackageToFile

Again, chose not to “Save sensitive”:

SaveCopyofPackage

Use “SQL Server Execute Package” to execute the package

Initiate SQL Server Execute Package

Using the Start button, initiate “Microsoft SQL Server 2012” \ “Integration Services” \ “Execute Package Utility”

LaunchExecutePackage_v2

Initiate SQL Server Execute Package

Using the Start button, initiate “Microsoft SQL Server 2012” \ “Integration Services” \ “Execute Package Utility”

Execute Package Utility – General

The first window that occurs is the “General” window:

ExecutePackageUtility-General-PackageSource

Choose :

  • Package source :- File System
  • Package :- choose the file

ExecutePackageUtility-General-PackageSource_AfterFileChosen

Execute Package Utility – “Connection Managers”

Make sure that you edit the DB Connection Settings:

ConnectionManagers

Once you have made necessary changes, please click the Execute button.

Summary

In summary, you need SQL Server Integration Services to execute saved SSIS packages.

SQL Server Integration Services Conversion Wizard is promising, but a bit restrictive when one wants to quickly make and re-execute changes.