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

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s