Getting out of the ground with TSQL Smells.

 

At SQLBits I had a number of conversations with a number of people over TSQL Smells, my open source project for static code analysis of SQL Code. The general opinion was that although the concept was sound, the process of getting it running ( and developing it further ) was not documented. Im sure that if you have sufficient .Net skills you could muddle through on your own, but since it will be mainly TSQL devs who have the interest here, I thought I would take the time to draw up some ‘Arse and Elbow’ documentation to get you started.

So lets go :

  1. Grab Visual Studio 2013. Community edition is fine operationally ( Im not a lawyer, check the licensing terms).
  2. After that has installed, update SSDT. Tools -> Extensions and Updates -> Updates -> Product Updates
  3. Install both the 32Bit and 64Bit versions of the DACFX framework, at time of writing latest is downloadable from http://www.microsoft.com/en-us/download/confirmation.aspx?id=45886
  4. Ensure that the SSDT extensions directory ( C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\Extensions ) is read writable ( I use Everyone / Full control ).

That should be the tooling side of the equation sorted.

Now clone ( I wont go into what this means , a number of tutorials are available on github) the TSQL Smells repo on github http://github.com/davebally/TSQL-Smells

In terms of software this should now all be done.

Run up visual studio, load in the TSQLSmells solutions and “Rebuild Solution”.

If that all goes according to plan, you will now be able to run the unit tests

And if you are even more lucky they will all pass.

This now means that the rules are installed correctly and we can proceed onwards.

The unit test cases reference sql files that are held in the TSQLSmellsTest project, you can further test by loading this project and selecting the “Run Code Analysis” option.

You are now in a position to load your own projects and run the code analysis against them.

Finally, included in the solution is RunSCAAnalysis, this generates a small exe that can run the static code analysis rules against a database or dacpac.

Command line options for that are –S Server –o outputfile and one of d
database or –f filename, it would be my intention with this that it that it would form part of your automated build/deploy framework (if you are not using SSDT) and break the build on a “Smell”.

I hope that this blog entry goes someway to starting you on your way to better TSQL Code quality and for my part, I will be giving it some love in the near future.


Update 16-apr-2016

Ive had some comments that the project is not working so ive now update the GitHub project to work with VS2015 / SQL 2014.  To do this i built a fresh azure VM with VS2015 installed and i did run into some problems.

If you are having build problems :

Mostly these may be due to missing references and due to the multiple packages involved it hard to give absolute instructions.

Missing Dac references : You need to find and install the latest ( or appropriate) DACFX packages (32Bit is essential , 64 Bit is optional for a runtime env).  The SSDT team blog is the best source of reference https://blogs.msdn.microsoft.com/ssdt/ After install you may need to delete the reference in the project , add reference and browse to the DACFX folder ( C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130\ ) and add the dlls in.

If you are not using VS2015 & SQL 2014 then you will probably have to look in different version number folders.

Missing TransactSql.ScriptDom reference : This is installed as part of sql server and not vs,  if you do not have SQL Installed, you will need to find “Transact-SQL Language Service” from the SQL Server Feature pack https://www.microsoft.com/en-gb/download/details.aspx?id=42295.  The package is tsqllanguageservice.msi.

Again delete the references and re-add by browsing to the folder, C:\Program Files (x86)\Microsoft SQL Server\130\SDK\Assemblies.  Again the 130 version number will change over time / older versions.

It builds but the tests fail :

This will probably be due to the output being placed in the incorrect folder.  SSDT will look to the folders (C:\Program Files\Microsoft SQL Server\X\Dac\Bin\Extensions\ ) where X is the SQL Version being targeted..  Make sure that the folder exists for your specific setup.

I wish that there were a way to get over all the versioning and streamline this but as of yet there isnt.

 

 

2 thoughts on “Getting out of the ground with TSQL Smells.

  1. Hi Dave Bally

    the issue with the reference in my case was that for Project:
    TSQLSmellSCA, TSQLSmellsSSDTTest
    for the assembly Microsoft.SqlServer.TransactSql.ScriptDom
    the project reference points to C:\Program Files (x86)\Microsoft SQL Server\130\SDK\Assemblies\
    while for assembly Microsoft.SqlServer.Dac.Extensions and
    Microsoft.SqlServer.Dac it points to C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130\
    in my case i have different build so I got the following error “Compiler Error CS1705” updating the references to point to the same folder VS 14.0 fix the issue. I will try to contribute to GitHub to fix this.

    • Hi Pedro…

      Yes, this is a constant battle with TSQLSmells… There is effectively a cartesian join between VS and SQL versions, any one of which may or may not be valid on a particular desktop. Keeping upto date ( although maybe people are not working on the latest and greatest ) is something i havent been to good at.

Leave a Reply

Your email address will not be published. Required fields are marked *