Transact SQL and the C Level Programmer ( String Casing and Indexes )

Background

Again, back reviewing code and saw yet another interesting one.

The code looks like this:


select username
from   [dbo].[contactFNTest]
where  LOWER(lastname) = LOWER(@lastname)

 

Thinking

Left me thinking why all the string conversion/proper casing when your Database is case-insensitive. And, I was wondering will an index help.

 

Lab

Let us create a small little lab environment.

 

Create Table

set noexec off
go

use tempdb
go

if object_id('dbo.contactFNTest') is not null
begin
   set noexec on
end
go

create table [dbo].[contactFNTest]
(

    [username]    varchar(30) not null
  , [firstname]   varchar(60) not null
  , [lastname]    varchar(60) not null

  , [phoneNumber] varchar(30) null

  , constraint PK_CONTACTFNTEST PRIMARY KEY
    (
      [username]
    )

)

go

set noexec off
go

if not exists
(
   select 1
   from   sys.indexes tblSI
   where  tblSI.object_id = object_id('dbo.contactFNTest')
   and    tblSI.name = 'idx_lastname'
)
begin

    create index idx_lastname
    on [dbo].[contactFNTest]
    (
      [lastname]
    )

end
go

 

Populate Table


set nocount on
go

use tempdb
go

truncate table [dbo].[contactFNTest]
go

insert into [dbo].[contactFNTest]
([username], [firstname], [lastname], [phoneNumber] )
values
  ( 'dadeniji', 'Daniel', 'Adeniji', '803-111-2222')
, ( 'troberts', 'Tanya', 'Roberts', '415-121-2222')
, ( 'Trinidad', 'Felix', 'Trinidad', '310-121-2222')
, ( 'SHAGER', 'Sammy', 'Hager', '707-121-2222')
, ( 'smosely', 'Shane', 'Mosely', '213-121-2222')

 

Query Table


set nocount on
go

declare @lastname varchar(60)

print 'Product Version    :  ' + cast(serverproperty('productversion') as varchar)
print 'Server Collation   :  ' + cast(serverproperty('collation') as varchar)
print 'Database           :  ' + db_name()
print 'Database Collation :  ' + cast(databasepropertyex(db_name(), 'collation') as varchar)

select username
from   [dbo].[contactFNTest]
where  LOWER(lastname) = LOWER(@lastname)

select username
from   [dbo].[contactFNTest]
where  lastname = @lastname

 

DB Info

Here is Server and DB Info:

DBInfo

Explanation:

  • Product Version :- 12.0.2000.8 – 12 means SQL Server 2014
  • Database Collation :- Latin1_General_CI_AS – CI means Case Insensitive

 

Review Query Plan

QueryPlan

A quick explanation:

  • The query that uses the Lower on the column name does an index scan
  • And, the one that does not use the lower does an index seek

They both were able to put the index on the lastname to work.

 

C Level Programmer

I titled this post C-Level programmer for a couple of reasons.  In C language we had strcmp, strcmpi.

And, so when you bring a C Developer to the SQL World he assumes that the DB Engine is by default configured for case-sensitive searches and comparison; which is in-fact quicker and faster.

But, if truth be told, I personally have seen more databases setup with case-insensitive collation \ sort.

So you are thinking Daniel, you are making an assumption that just because someone cares about string case, that does not make them C-Developers.

But, then the dead giveaway was this code line


where  LOWER(substring(countryCode, 0, 3)) = LOWER(substring(@CountryCode, 0, 3))

I am thinking to myself that in SQL we do not start counting at 0, but at 1.

 

Substring – Start

Let us compare substring start argument at 0 with start argument 1


    use [tempdb]
    go

    select
         username
       , phonenumber
       , substring(phoneNumber,0,3) [returnsFirst2Chars]
       , substring(phoneNumber,1,3) [returnsFirst3Chars]
    from [dbo].[contactFNTest]

 

Output:

phoneNumber

 

 

Microsoft Take

Here is Microsoft’s documentation on SubString ( https://msdn.microsoft.com/en-us/library/ms187748.aspx )

SUBSTRING ( expression ,start , length )

Is an integer or bigint expression that specifies where the returned characters start. If start is less than 1, the returned expression will begin at the first character that is specified in expression. In this case, the number of characters that are returned is the largest value of either the sum of start + length- 1 or 0. If start is greater than the number of characters in the value expression, a zero-length expression is returned.

I will let you think more why you will want to pass a value less than 1 as the start argument.

 

 

SQL Server Data Tools (SSDT) / SSRS – Report Data Pane

Background

I am feeling a bit out of place with SQL Server Data Tools ( SSDT ).  Windows are missing.

 

Window Missing

reportDataPaneMissing

 

What is missing?

You never know what you have till it is gone.

Here is what our screen currently looks like.

 

reportDataPaneAppear

 

Later, find out that the window missing is called “Report Data”.

 

Report Data

Enable

To enable the “Report Data” pane.

  • Access the menu Items – View \ ReportData
  • Please keep in mind that once the Report Data panel is visible, the aforementioned menu item is gone

 

reportDataPaneMenu

 

 

What is in the “Report Data” pane:

  • List of Report Parameters
  • List of Images
  • List of Data Sources
  • List of Datasets

Basically, it contains all the elements that are currently in-use in the report.

 

IIS – Web Site Configuration Inspection using msdeploy

Background

I am trying to get this 3rd party Web Application to work properly, but it so happens that for one for virtual directory (vdir) the global.asa is read, but for another vdir the global config file is not being read.

 

Trouble-shooting

Eye Balling

Tried eye-balling, but I can’t figure out what is different.

WebDeploy

Download & Install

Download WebDeploy from http://www.iis.net/downloads/microsoft/web-deploy. The current version is 3.5 and so the rest of this post will assume that is the version we are using.

And, install it.

 

Where is it installed?


dir msdeploy.exe /s

Image:

WhereIsMsdeployPlaced

 

Usage


"C:\Program Files (x86)\IIS\Microsoft Web Deploy V3\msdeploy.exe" -verb:dump -so
urce:appHostConfig="Default Web Site" -xml > c:\tmp\websiteconfigDefault.xml

 

Review

Using a text editor review the XML Configuration file.

DirPath

Let us review the directory path for both folders.

Please keep in mind that — “the IntegratedLogin works, but the Login does not work“.

 

IntegratedLogin

dirPath path="IntegratedLogin" securityDescriptor="D:" parentSecurityDescriptors="" attributes="Directory"

 

dirPath-Login

 

Login

dirPath path="IntegratedLogin" securityDescriptor="D:" parentSecurityDescriptors="" attributes="Directory"

 

dirPath-IntegratedLogin

 

Tabulated:

Verb Folder :- IntegratedLogin Folder :- Login
 path IntegratedLogin Login
 securityDescriptor  D:  D:
 parentSecurityDescriptors  [empty] [empty]
 attributes  Directory Directory

 

 

Things are same.

Application & Virtual Directory

Let us review the Application and Virtual Directory settings for both folders.

Please keep in mind that — “the IntegratedLogin works, but the Login does not work“.

 

XMLBrowser

 

 

Very Quick Explanation:

  • The Virtual directories are listed under application path=”/”
    • Neither /Login nor IntegratedLogin is listed
  • In the list of Application paths
    • /Login is listed
    • But, /IntegratedLogin is not

 

Let us go remove Login as an Application.

Here is IIS before the change:

iismanager-before

 

And, as we make the change we are prompted:

removeApplication

 

Here it is:

iismanager-after

Technical Summary

Things work for /IntegratedLog, but not for /Login as Login was defined as an Application.

Applications do not inherit \ access the global.asa file.

The global.asa file is where we initialized and populated our global variables.

Unfortunately, the original application contained the awful “on error resume next“.

Once we removed it, and added a code to show our Application variables, we were more comfortable with our solution.


Dim iNumberofApplicationContentObjects
Dim x

on error goto 0

iNumberofApplicationContentObjects = Application.Contents.Count
Response.Write "iNumberofApplicationContentObjects " & cstr(iNumberofApplicationContentObjects) & "<BR>"

for each x in Application.Contents
  
  Response.Write(x & "=" & Application.Contents(x) & "<br>")

next

 

Listening

Daryl Singletary: I Let Her Lie (video)
https://www.youtube.com/watch?v=zX0pQ0QdUg8

 

References

 

SQL Server Reporting Services [SSRS] – Error – Null is not declared. ‘Null’ constants is no longer supported; use ‘System.DBNull’ instead

Background

It is a little bit of work to use null-able arguments in Sql Server Reporting Services (SSRS).

 

In DataSet Properties, Tried using “=(Null)”

For the dataset, when we set the parameter value to =Null

DatasetProperties-Parameters-(Null)

 

 Textual:

Error - Null is not declared.  'Null' constants is no longer supported; use 'System.DBNull' instead
Image:

NullConstantIsNoLongerSupported

 

In Dataset Properties, tried using “System.DBNull”

In Dataset properties, When we set the parameter value to =System.DBNull

DatasetProperties-Parameters-System.DBNull

Text
Error: [BC30691] 'DBNull' is a type in 'System' and cannot be used as an expression.

Image:
DBNullIsATypeInSystemAndCannotBeUsedInAnExpression

In Dataset Properties, tried using “=Nothing”

Tried setting the parameter value to:

  • =Nothing
  • =System.Convert.DBNull

 

DatasetProperties-Parameters-Nothing

 

We get an error.

Error

In Designer, when trying to preview the report.

Textual:

One or more parameters required to run the report have not been specified

 

Image:

OneOrMoreParametersRequiredToRunTheReportHaveNotBeenSpecified


 

Report Parameter – Default Value – Setting Parameter Value

  • Access the parameter
  • Access the “Default Values” panel
  • In the “Default Values” panel
    • In “Select from one of the following options” radio group, choose the “Specify values” option
    • In “Value” set “(Null)

ReportParameter-DefaultValues-SpecifyValues-Value-(Null)

 

Does not help

Dataset Properties – Report Parameter

Let us remove the parameter

Original:

Here are the dual parameters.

DatasetProperties-Parameter-Null-Included

Revised:

We have removed the second parameter.

DatasetProperties-Parameter-Null-Excluded

Error Message:

OneOrMoreParametersRequiredToRunTheReportHaveNotBeenSpecified (2nd Parameter removed)

Report Parameter – Set Value to Empty

Let us go set the parameter’s value to “” or System.String.Empty.

DatasetProperties-Parameter-SetToEmptyString

Things are good.

SQL Server Profiler

Here is what we see in SQL Server Profiler:

SQLServerProfiler-ParameterValueToEmpty

Transact SQL

In Transact SQL, make sure of the following:

    • The arguments is nullable — (variable [datatype] = null )
    • That you have same code line for when the variable is null and empty

 Sample Code:


if object_id('[dbo].[usp_city]') is null
begin

    exec('create procedure [dbo].[usp_city] as select 1/0 as [shell] ')

end
go

alter procedure [dbo].[usp_city]
(
      -- make sure @countryCode is nullable
      @countryCode varchar(255) = null

     --make sure that @country is nullable
    , @country     varchar(255) = null
)
as

    select
              vc.country
            , vc.countryCode
            , vc.city

    from   [dbo].[vw_city] vc

    where  (

                (
                    vc.countryCode
                                = case
                                        when (@countryCode is null) then vc.countryCode
                                        when (@countryCode = '') then vc.countryCode
                                        else @countryCode
                                  end
                )

                and
                (
                    vc.country
                                = case
                                        when (@country is null) then vc.country
                                        when (@country = '') then vc.country
                                        else @country
                                  end
                )

           ) 

    order by
                  vc.country asc
                , vc.city

go

 

Conclusion

It appears that SSRS does not really support the setting of parameters to Null, but it supports empty string.

 

References

SSRS

SQL Server Reporting Services–Error Message–“Invalid value ‘Outside’ for the custom attribute ‘Outside’”

Background

Here I am working on some reports and I ended up with the error message:

      Invalid value ‘Outside’ for the custom attribute ‘Outside’

 

How did I get that far off line

While waiting for my next train, I just ran into my play sister.  As we spoke the question of whether her parents are strict came up.

And, she replied “Ain’t all Caribbean parents like that”.

So here is my take, my graph used to look like this.

ActivitiesByRoom

 

It is obviously a Column Chart; on the other hand, it might not be so obvious that in some cases the labels will be placed inside the bars; while in

other cases they will be placed outside.

In our captured graph, our data points are shown inside for the first two numbers; 246 and 244.

 

SSRS Version

To keep this honest, we are developing using SQL Server Data Tools – v2012 ( SP1-CU4).

MicrosoftSQLServerReportingServicesDesigners

 

Setting Chart Label’s Position Outside

For the sake of consistency, let us go set the label’s position to always be on the outside.

ChartData.Legend.Label.Position

 

Error Message

The error message below is shown where the graph should have appeared

InvalidValueOutsideForTheCustomAttributeOutside

 

Remediate

To remediate, please run back and revert to Automatic.

The proper trail to take is to set via “Custom Attributes”:

CustomAttributes

 

Available Styles

Within the label style drop-down, here are the available choices:

CustomAttributeStyles

We can see that the disallowed choice of “Outside” is not included.

 

Pie Line Style

One of the cases where the Outside Position is valid, is when the graph’s type is Pie.

Custom Attributes:

For Pie Charts, the Label’s position is called “PieLabelStyle”.

Properties-PieChart-Series-Attributes

 

Choices:

PieLineStyle

 

Source Code

Here is the Source Code:

SourceCode-XML-ChartDataLabel-Position-Outside

 

Quick Explanation:

  • Label Position is set through the
    • Chart Series \ Label \ Position property, it is stored in ChartDataLabel \Style\ Position element
    • Chart Series \ CustomAttributes \ LabelStyle property, it is stored in ChartDataLabel \Style\ Position element

 

References

 

Listening

All the years, I have been listening to this song….  And, it is only now that I heard this line (at the very end of the song)!

Lyrics

Somethings that happen, you never really know,

Just like it says in the bible, it happens for a reason

You got to have faith

Video

Where will you be Today
http://www.vevo.com/watch/kenny-chesney/Who-Youd-Be-Today/USBVA0500022

 

Summary

I think back to my sister’s wise words.

“Yes, I agree with my Dad.  After seeing all the problems other kids have, I like the fact that my parents are more structured”

Enough about her.

I really would not have had my problems if I had gone about setting the Label Position \ Style via the more concise trail of “Custom Attributes”.

Notwithstanding what all the kids gang up to say, “more is not always better“.

SQL Server Reporting Services–TextBox–Expressions greyed out

Background

Last Friday, I and the Project Manager of the Application I am working on locked ourselves in a conference room and tried to nail down some reports.

Honestly, it went slow.

I am not sure whether I was pensive as he is very busy and his time is premium or whether it was other things.

 

What I could not do

What simple things did I have problems with?

Well edit some of the text boxes.

 

Walk through

Here is a walk through.

  1. Create a new Report
  2. Add an Header line
  3. Extend the Header line into two sentences
  4. Now try to access the Express Editor for the Header Text-box
  5. You will notice that the  Expression menu item is disabled

 

Expressions

ExpressionsDisabledExpressionsDisabled

 

Review Source Code

Let us review the Report Source Code and see if there is something special about our Text-box.

Let us assume that we captured the source code before extending the line.

textboxReportHeader-SourceCode-Before

 

Here is what happens once we have extended the text unto two lines:

textboxReportHeader-SourceCode-AfterAddingSecondLine

 

Quick Notes:

  • We have two paragraph elements – one for each line

In Source Code, Comment Out Second Line

Comment out second line

textboxReportHeader-SourceCode-AfterCommeningSecondLine

 

Confirm

Let us confirm that the Expressions menu item re-appears

ExpressionsEnabled

 

 

Proper use of Expression Editor

Here it how to separate our text into separate lines:

 

ExpressionsEditorInuse

 

 

 

Notes:

  • We use the constant vbCRLF to separate out our lines

 

Dedicated

Thanks goodness for XML; as it makes Vendor generated code much easier to delve into.

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