How to fix: Visual Studio 2012 startup problem: The 'HDIProjectPackage' package did not load correctly.

After installing Community Edition of VS2013 with some additional packages i've noticed that my VS2012 starts failing to start with message like "The 'HDIProjectPackage' package did not load correctly. The problem may have been caused by a configuration change or by the installation of another extension. You can get more information by examining the file 'C:\Users\************\AppData\Roaming\Microsoft\VisualStudio\11.0\ActivityLog.xml'."

Activity log shows

SetSite failed for package [HDIProjectPackage]
End package load [HDIProjectPackage]
{665BA479-E656-4485-9097-7CE2A22A90F2} (just to let Google to find this page)

Google knows nothing about HDIProjectPackage. I dont know how to fix errors with VS2012. I'm stuck!
After 24 hours of thinking and meditation (and several reinstalls of both VS2012 and VS2013) I've found that the problem was with "Microsoft Azure HDInsight Tools for Visual Studio" which I've installed via Web Platform Installer
HDIProjectPackage->HDInsight, looks similar, right?
So I uninstalled "Microsoft Azure HDInsight Tools for Visual Studio" and my VS2012 now alive!!!!!
And I can continue to develop next release of SqlCodeGuard with such nice features like, say, preprocessor commands to disable specific issues in particular scripts (or even - part of script)

SQLSaturday 311, Sofia, October 11

Making last changes before sqlsat 311.
By the way, did you ever visited SQL Saturday? No? Why?
SQL Saturday is fun, SQL Saturday is free, SQL Saturday is just what you need!
Good place to meet famous speakers from all around the world, ask them question that cannot be answered by usual people, make new friends :)

To be brief. What to do when PATH variable is too long to be real PATH.

Sometimes after installing pretty useful addins when starting SSMS you can see error message like "Exception has been thrown by the target of an invocation"

That can mean that your PATH variable is tooooo long to fit some system functions - and SSMS crashes.
Question "Why one function allows to write value that other function cannot read?" falls far beyond this topic.
So - what can you do?

First that came into my mind is manually change PATH variable - remove entries that are no longer needed and replace full folder names with the same names but in 8.3 format.
But stop! My PATH variable is more than 2K chars long! I dont want to make all these changes by my own hands!
Lets write small  program instead, ok?

using System;
using System.Runtime.InteropServices;
using System.Text;

namespace ShortenPath
{
    class ShortifyPath
    {
        [DllImport("kernel32.dll", SetLastError = true)]
        private static extern int GetShortPathName(String pathName, StringBuilder shortName, int cbShortName);
        static void Main(string[] args)
        {
            string path = Environment.GetEnvironmentVariable("PATH");
            Console.WriteLine("Current path value:{0}",path);
            string[] chunks = path.Split(new char[] {';'},StringSplitOptions.RemoveEmptyEntries);
            StringBuilder sb = new StringBuilder();
            foreach (string chunk in chunks)
            {
                StringBuilder sb1 = new StringBuilder(500);
                int n = GetShortPathName(chunk, sb1, 500);
                if (0 == n)
                {
                    if (2 != Marshal.GetLastWin32Error())
                    {
                        Console.WriteLine("Something went wrong...");
                        Console.WriteLine("{0}", Marshal.GetLastWin32Error().ToString());
                        return;
                    }
                    else
                    {
                        sb1.Append(chunk);
                    }
                }
                sb.AppendFormat("{0};", sb1.ToString());
            }
            Console.WriteLine("Path is shortened! Old len: {0}, new len {1}",path.Length,sb.ToString().Length);
            Console.WriteLine(sb.ToString());
        }
    }
}

Compile it using csc.exe
C:\Windows\Microsoft.NET\Framework\v2.0.50727\csc.exe ShortenPath.cs

And voila!
Run it and the program will print old value of PATH variable and the new value with shortened folder names. If you agree with result then you can use new value as value for PATH variable.
In my case I've saved about 500 chars from 2000.

SQL Code Guard v2.6 released

Today we released new version of SQL Code Guard, 2.6

Main changes:

1. Bug fixing

2. Small GUI and usability improvements

3. Fixed support of SQL2014

4. Changed behavior of some issues.

Full list of changes you can see at http://sqlcodeguard.com/index-database-changelog.html#R2_6

Enjoy!

ExpressProfiler - new release

Just released new build of ExpressProfiler.
No major changes, just cosmetic improvements - shortcut here, confirmation dialog there, new captured events (SQL:StmtStarting/Completed, Blocked Process report), descriptions for events/columns.
Still digitally signed, still 2 options - installation package (including Red Gate Ecosystem registration ) and standalone application.

Also do not forget to try our main t-sql tool - Sql Code Guard. It is really cool :)

Keep it simple.... you know.

Twice a week I am asked "why don't you add new cool feature to ExpressProfiler?"
The answer is as simple as ExpressProfiler itself - you should always keep things as simple as you can.

Should be balance.
On one hand you have nothing that requires nothing and does nothing - perfect minimalism.
On the other hand you have standard SQL Profiler - it does all and requires all.

Express Profiler should be as simple as possible, so you could easily copy and use it; and as useful as possible so you would be able and wanted to use it.

And yes - sometimes I simply too lazy to implement feature request.

[BP017] DELETE statement without WHERE clause

Sometimes people ask me: "What wrong with following code? Why issue BP017 is registered?"

delete d
from    dbo.Data d inner join #ids i on d.id = i.id

As you can see, rows to be deleted are limited by joining with temporary table named #ids which apparently contains a list of needed row identifiers.
So what is the problem? Why BP017 is registered?

I like to move it, move it. Upload and download binary data to or from SQL Server using standard tools

Imagine that you have a huge collection of Hello Kitty images (I do).
One day you make a decision to organize your collection - sort it, label it, tag it, remove duplicates and so on. The first thing you need - sophisticated data storage, of course. And it is obvious that you choose SQL Server - the world's best database platform :)
And since SQL Server is already present in our Universe - how about to load binary data into it?

ExpressProfiler: Yet another "when and why" story.

Core of ExpressProfiler  was born about 8 years ago when I worked for a company whose mission was the migration of Oracle and Sybase to SQL Server. We used a tool for automated database code conversion and migration (a pretty good tool btw - SSMA).
If you already know what is the difference between an "automatic" and an "automated" conversion then you may guess that there were a lot of manual work involved on the rewriting and optimization of converted code.

Script, store and transfer data using XML, XQuery and clipboard

When surfing SQL Server forums you might notice a lot of questions like "How do I script my data and transfer it to another server?". Of course these questions always have answers,  from "Nohow" to "Use SSMS 2012/ SSMSBoost/ SSMS Toolpack". But what can you do if you don't have neither SQL Server 2012 nor any 3rd party tools (quite useful by the way).

Don't mess with success or Everybody lies.

Imagine that you have a table of customers

create table Customer
(
Name varchar(30),
LastName varchar(30),
Title varchar(30)
);
go
insert into Customer values('Alex','Smith','Mr.');
insert into Customer values('John','Doe','Dr.');
go

Now you selecting list of customers

select    c.Title + ' '+ c.Name +' '+c.LastName as Name from dbo.Customer c
order by Name

Just as planned!
Here is a list of customers, perfectly sorted.

To be sure that your query has no issues you test it with SQL Code Guard.
What a pity!

How to easily automate SQL Code Guard.

It may happen that you have a need to check the scripts that you have on your disk.  For example, it may be part of your build process.

So how you can run SQL Code Guard as a command-line tool?

SQL Code Guard. New release 2.5.5257

Today we released new version of SQL Code Guard, 2.5.5257
Main changes:

1. SQL Code Guard is digitally signed now to satisfy corporate security policies.
2. Added editor for exclusion lists.
3. Some information messages added in order to make user experience more convenient

Full list of changes you can see at http://sqlcodeguard.com/index-database-changelog.html

ExpressProfiler now is digitally signed.

Today I've replaced download package for ExpressProfiler with digitally signed version of standalone application and Ecosystem installation.
Unfortunately I did something wrong and reset download statistics to zero :( Hope that Codeplex's guys can help.
However this is not a big deal - ExpressProfiler looks popular and can reach big download numbers in a just few days :)

Complexity. How it is calculated.

What are the magic pink numbers you can see in Code Outline window?

The answer is very simple - these are Code Complexity numbers. Each number represents complexity of outlined node, something like Cost in query execution plan. The same numbers you can see after Complexity check - but for whole procedure, not for each statement.

So how Complexity is calculated?

Varchar "miracle" and best practice

I've just spent fifteen minutes helping colleague to find "miracle in t-sql code".
- There should be rows, definitely! I can run simple query and voila! 4 thousands of rows selected! So why there are no rows when I'm doing a simple left join with small filter expression?
There is no miracle of course.

ExpressProfiler: The New Kid on the Block

I'm pleased to inform you that yet another of my pet-projects, ExpressProfiler, joined the rapidly growing family of Ecosystem-based tools for SQL Server.
You may ask me, "why should I use ExpressProfiler if there is standard profiler supplied with SQL Server?"

There are a number of reasons:

First - not every edition of SQL Server has a profiler. The Express edition didn’t benefit from a profiler until 2012SP1.
Of course you can buy the inexpensive Developer Edition, but what will you do on environments where only SQLExpress is installed? Will you install The Developer Edition? Of course not. The solution is to download ExpressProfiler, which doesn't need to be installed. Simply copy a single executable and run it! (it also comes with an optional installer.)
Okay. You may say, "I already have the Standard (or even Enterprise) edition of SQL Server. Why do I need ExpresProfiler?"
1. It is small and simple.
2. It is always ready for action (especially when launched from SSMS, which supplies it with your server connection settings)
3. It is fast. I'm not joking. When you are working in a remote session with servers in your datacenter, ExpressProfiler is much faster than the standard profiler due to its far simpler UI.

Psst! Wanna some API?

If you have already visited SQL Code Guard's website you might notice a reference to an "API for custom tool development". Pretty cool sounding, isn’t it? SQL Code Guard can not only find issues as a standalone tool, but also exposes its capabilities to be used in your own solutions!

A few words about simplicity: Using Red-Gate Ecosystem framework

In my first blog post I briefly covered when and why I created SQL Code Guard (SCG).

To be honest, the first version of SCG was a standalone application, but supporting a GUI took too much time so I decided to convert it to an SSMS plugin – in this case I was able to use SSMS’s GUI, its editors, syntax highlighting and so on.

After  some considerable research I discovered that the knowledge on how to write an SSMS addin is a much hidden treasure.

When issue is not exactly "issue"

In last release of SQL Code Guard (2.4.5222) I've added few issues which can cause confusion.

Eg - ST012 "Consider using temporary table instead of table variable"

You can ask me - "What is wrong with table variable? Everyone suggests to use it! Even Microsoft!"

New release 2.4.5222

Today we released new version of SQL Code Guard - 2.4.5222

There are many fixes and improvements, some new rules were added.

Full list of changes you can view at http://sqlcodeguard.com/index-database-changelog.html

But there is a few more important things.

First, we introduced new type of issues - "Script issues". This category will contain issues that can be vital during build, deploy and continuous integration process. Used with msbuild it can be very useful in modern development processes.

Second, we introduced "exclusions". What that means? Sometime you want to relax checking some rules, eg to not check nullability of columns for tables which name fits specific pattern. So now you can add exception for some issues.

Third - we added support for TFS Checkin Policy so you can establish new level of code quality control for t-sql scripts.

And last but not least - SQL Code Guard team now offers support and custom development. You can read more at http://sqlcodeguard.com/index-database-custom.html

Why I Developed SQL Code Guard: A Fairy Tale from a DBA

A long time ago, in a galaxy far, far away….

About ten years ago, I realized I was spending more than 75% of my business day writing T-SQL code. It was cool – yet awful at the same time! Cool ‘cause I became a ReAl TsQl GuY :) and awful as I had no good tools to do my job better. When I was working with C++, Delphi – even Borland Pascal – I had a wide range of code-proofing tools. But T-SQL doesn’t have ANY! Yes, we had MSBPA (SQL Server 2000 Best Practices Analyzer) - but it was impossible to use it in everyday development or continuous integration processes.

Therefore, as a TRUE programmer, I made the only sensible decision – to create my own tool! With bells and whistles, of course. And furthermore, I’ve made the tool available to the SQL community for free!
It took quite a lot of time to make the dream come true, but one fine day it was finally done, and now SQL Code Guard (SCG) is born!

So, what does it do?