Saturday, November 24, 2012

IIS Express Quickie Command-line

This is a reminder to my future self for how to quickly start IIS Express from the command-line on 64-bit Windows 7 or 8 assuming you already have IIS Express installed using the defaults.

  1. Open admin command line.
  2. cd\Program Files (x86)\IIS Express
  3. iisexpress /path:c:\YourApplicationRootFolder /port:WhateverPortNumber

Note: You can also specify /clr:v2.0 if you hate fun.  The default is CLR 4 which works with .NET 4 and 4.5.

This was adapted from the full article on www.iis.net: http://www.iis.net/learn/extensions/using-iis-express/running-iis-express-from-the-command-line

Monday, November 19, 2012

Surface: Standard Onscreen Keyboard

If you feel that the default on-screen keyboard on the Surface RT isn't sufficient, you can enable what I would say is the "pro" keyboard via the settings.

  • Drag in from the right hand side to show the charms and click Settings.
  • On the bottom of the settings window, click "Change PC Settings" to show the Windows 8-style control panel.
  • Under General... Touch Keyboard... turn on the option called "Make the standard keyboard layout available".

Now when you're displaying the onscreen keyboard, there will be a fifth option available which is a more "standard" Windows keyboard including up arrow, down arrow, and the option for F1-F12 if you hit the Fn key first.  I still haven't found a way to show Insert/Delete/Home/Page Up/Page Down other than with the physical keyboard.

Monday, October 1, 2012

SQL in the City - New York 2012

I was able to attend the SQL in the City event in New York this past Friday.  I highly recommend that anyone who can spare the time and travel should attend the next SQL in the City day in your area.  I found it to be a very well put-together event with useful content and excellent networking opportunities.

At the event I was able to meet both Steve Jones (who compassionately shook my hand as I walked in dripping wet from the rain during my walk over to 3rd Avenue) and Grant Fritchey (Scary DBA).  It was great to have even a brief conversation with each of them and introduce myself.  I also got to meet Product Manager David Atkinson and Developer David Simner and talk briefly about the awesome SQL Source Control and SQL Compare products.  The presentations I attended, "Database Maintenance Essentials", "Red Gate Tools - the Complete Lifecycle", and "The Whys and Hows of Database Continuous Integration" were all very informative and useful.

The highlight of the day was finding out about Red-Gate's Virtual Restore product.  This tool essentially allows you to mount a .BAK file as a database.  It supports multiple databases mounted from the same .BAK file, and it is not destructive to the .BAK file.  From the description, it works very similarly to VMs that are mounted from a disk file and then store changes in a disk "diff" file.  This tool allows you to save both space and time and sounds very awesome for developers.

I must say that I think Red-Gate has made a critical error with the marketing of this tool.  Developers hear "restore" and think "DBA tool"; in fact I think that's what the Red-Gate marketing team thought too as this application is included in their DBA bundle only.  I would absolutely use this tool as a developer and I might even use it as often as I use things like SQL Compare and the rest of the more developer-oriented tools.  It's a game-changer, and I hope Red-Gate figures out that they're doing a very bad job of marketing this product and could probably be making a lot more money from it by just getting the word out that it's not only a DBA tool.  For most of the other Red-Gate developer tools, a savvy developer could imagine cooking-up some sort of "good enough" workaround to not have to buy it (though certainly not in a shorter time than the reasonable expense for the license), but I would never even attempt creating something like Virtual Restore.  I will absolutely be trying out this software later this week.  Red-Gate - you've got to get the word out, even think about changing the name or something!!!

Thanks very much to everyone at Red-Gate for putting this event together.  I hope to see you again at the next SQL in the City.

Tuesday, August 28, 2012

PowerShell: Redirecting Console Output and Error Output to a Variable and E-mailing it

I recently had the need to script the execution of a command-line utility and thought it would be a good learning experience to try doing it with PowerShell.  The utility would sometimes output to the STDOUT, but would also send its output to STDERR if there was a problem.  This seemed to involve many different problems all at once including:

  • Running an EXE with a space in the path and multiple command-line parameters
  • Capture both STDERR and STDOUT to a variable
  • Escaping special characters/using special characters
  • Doing string concatenation
  • Doing explicit type conversion
  • Sending an email
  • Joining a string array to a single string variable
  • Doing a "FOR" loop (optional but fun).

Anyway, here's the script I came up with that runs a command-line app and takes all the output (including the error stream) and joins it into a string and then sends it in an email to me.  It's a bit ugly because I am a PowerShell novice, but it does work.  The only trouble I've noticed is that sometimes the error lines will be a bit out of sequence from the corresponding stdout lines.  If someone knows how to fix that, please let me know in the comments!!!

 

#Setup the command string - notice the `" to escape the double-quotes inside the string (deals with spaces in path)

# and the old batch file trick of doing 2>&1 to ensure STDERR is piped to STDOUT.

$command = "& `"C:\Users\myprofile\Documents\visual studio 2010\Projects\TestOutput\TestOutput\bin\Debug\testoutput.exe`" someparameter -xyz someotherparameter -abc someotherthing -rfz -a somethinghere 2>&1"
#Execute the command and put the output in an array.

$console_output_array = invoke-expression $command

#loop through the array and print out the results to the command line (optional)

for ($i=0; $i -lt $console_output_array.length; $i++)
{
    [string]$i + "=<" + $console_output_array[$i] + ">"
}

#create a single string by joining together the array

$console_output_string = [string]::join("`r`n",$console_output_array)

#send an email with the results

$emailFrom = "fromemail@example.com"
$emailTo = "toemail@example.com" 
$subject = "Email subject goes here"
$smtp = new-object Net.Mail.SmtpClient("yoursmtpserver.example.com", 25)
$smtp.Send($emailFrom, $emailTo, $subject, $console_output_string)

Tuesday, August 14, 2012

Fix: Crystal Reports Shows Red X Across Entire Page When Report Run Inside Visual Studio

I was googling (with Bing AND Google) like crazy for a solution to this, but didn't find one from any of the open resource sites, so I figured I'd blog about my fix for this to help the next poor dev to come along.

I was getting user feedback that one of the Crystal Reports in an application that I support was intermittently crashing.  When I ran the report in Visual Studio, on certain pages of the report, I observed a red X across the whole screen with a white field.  Obviously something was very wrong here!!!

Red X on Crystal Report 

It boiled down to that there was a null value being used in one of my formula fields, and Crystal seems to raise an exception for nulls when they are used as part of certain types of formulas and not converted manually to an actual value first.  I didn't know which field was causing the issue so I just started a divide and conquer approach.  I suppressed all fields and confirmed that the report worked (select a bunch of fields, Right Click... Format Multiple Objects... Suppress).  Then I started un-suppressing a few objects at a time until the report started failing again.  Now I knew where the problem was (or at least one of the problems).

I then right-clicked one of the formula fields and chose "Find In Formulas" to open the formula workshop.  I drilled down to the formula field (it could be in any of the sections) and found the offending one.  Note that it could be a "normal" formula field or even one of the special formatting formulas (like "if this field's value is true, format as green").

image

I believe the "right way" to fix it is to handle NULLs properly using ISNULL() (which returns a boolean unlike the T-SQL ISNULL() ) or another appropriate method in Crystal for each and every one of your formulas.  This MSDN article describes that.  However, there exists another workaround as well which should be fine for most simple cases.  On the top of each formula when you click into the white field where the code actually lives, you can change this box:

Exceptions For Nulls

To say this:

Default Values for Nulls

If you choose "Default Values For Nulls", Crystal will substitute "" for null strings and an appropriate flavor of 0 for numeric fields.  Here's some documentation that oddly seems to skip booleans.

This dropdown selection is per-formula, so it might take a lot of work to go through and change every formula in the tree view (don't forget to look at the formatting formulas too!!!), but if your report is crashing with the dreaded red X and you don't know why, this may get it working again.

Wednesday, July 18, 2012

Enabling "Open Project Folder" for project types that don't support it

One annoying thing about SSIS (2008) and some of the other project types in Visual Studio is the lack of an "Open Project Directory..." option on the project right-click menu.  Visual Studio's "Tools" menu can provide a work-around this.
 
Go to Tools... External Tools... and add a new entry with the following properties:
Title = &Open Project Folder
Command = explorer.exe
Arguments = "$(ProjectDir)"
Initial Directory = "$(ProjectDir)"

image


Now, when I select my SSIS project, I can choose "Tools... Open Project Folder" (or ALT+T,O,<ENTER>), and the correct folder will open in Windows Explorer.
 
Using macros such as $(ProjectDir), $(SolutionDir), $(Configuration), and others can come in very handy when you find yourself doing the same types of things over and over in Visual Studio.  Here is the documentation for all other built-in VS macros:  http://msdn.microsoft.com/en-us/library/c02as0cs.aspx

Sunday, July 8, 2012

A Pattern for PIVOT

Use of the PIVOT operator in T-SQL has always been tantalizing yet confounding to me.  It offers the promise of more concise and expressive code compared to a subselect pattern, yet I've always found it difficult to get the code just right so that I could apply it in a consistent way without my queries feeling like they are "working by miracle" or that there is way too much hardcoding going on resulting in my code becoming rigid or brittle.

I believe that I've finally figured out a good pattern for using PIVOT that is fast, concise, and reusable.   The pattern hinges on these points.

  1. Only apply this pattern to datasets when you know how many categories you will end up with; for example, months/quarters in a year.  If you don't know this at design time, it might be best to just return the raw data and let your presentation layer do the Pivot (assuming some reporting solution or app).  Barring this, you're off into the land of dynamic SQL (bring a towel).
  2. Start with a CTE called "categoriesAndRawData".  This represents your categories and the raw data to pivot.  Included should be three aliased fields, [PivotOn], [PivotAggregate], and [PivotAnchor].
    • The [PivotOn] field is the value that will turn into the column names or categories.  You should try to keep this as simple as possible - best is a number, but a short code will also work.
    • The [PivotAggregate] field represents a number that will be summed in the pivot result.  If you are looking to do a count, you can hardcode a 1 for this field.
    • The [PivotAnchor] field provides a point of reference for the Pivot On field.  If you were PIVOTing out months into columns, this would be the year.  This field is optional if you don't need an anchor such as if you were PIVOTing data into one of N categories and the categories don't need context.
  3. Create a minimalist subselect from the categoriesAndRawData CTE that includes only the Anchor, Aggregate, and PivotOn fields, plus any "keys to carry through" (the "keys to carry through" is the magic sauce of this pattern).  Alias this as pIn - "pivot input".
  4. Add a PIVOT clause with a SUM of the pIn.PivotAggregate using the pIn.PivotOn field, and then list out your categories in the IN clause.  Alias the pivot result as "pr".

Examples

I have setup the following example queries from the AdventureWorks2008R2 database.

I have the following query that provides the raw data needed to find all-time sales by month overall, by sales person, and by territory.  (Note this only includes data where the sales person is known - there are many rows in AdventureWorks2008R2 where the sales person is NULL).

SELECT OrderDate, SalesPersonID, TerritoryID FROM Sales.SalesOrderHeader

  WHERE SalesPersonID IS NOT NULL AND TerritoryID IS NOT NULL;

image

Let's follow the pattern.

Step 1: I know that I want to break out this data into months per year so I know the number of categories (12 months) and my anchor element that describes the categories (the year).  So I have confirmed that my dataset fits this pattern.  Step 1 = Check.

Step 2: Let's get my query ready that will be used as the categoriesAndRawData CTE...

 

SELECT YEAR(OrderDate) AS [PivotAnchor],

      MONTH(OrderDate) AS [PivotOn],

      1 AS [PivotAggregate],

      OrderDate, SalesPersonID, TerritoryID FROM Sales.SalesOrderHeader

      WHERE SalesPersonID IS NOT NULL AND TerritoryID IS NOT NULL;

 

image

This is the same query as the raw data except that it has the three fields needed to do the PIVOT.  The PivotOn field which will become my columns in the pivot result, the PivotAggregate which will be SUMmed to become the data in the pivot result, and the PivotAnchor which provides context for the PivotOn field.  Because I am going for a "COUNT" of sales in my results, I can hardcode a 1 as the PivotAggregate.  If I were interested in dollars of the sales, I could put either the SubTotal or TotalDue field here.

I can change this into a Common Table Expression (CTE) by stating it as this:

WITH categoriesAndRawData AS (

    SELECT YEAR(OrderDate) AS [PivotAnchor],

      MONTH(OrderDate) AS [PivotOn],

      1 AS [PivotAggregate],

      OrderDate, SalesPersonID, TerritoryID FROM Sales.SalesOrderHeader

      WHERE SalesPersonID IS NOT NULL AND TerritoryID IS NOT NULL

    )

  SELECT * FROM categoriesAndRawData;

This is the same query as before, except encapsulated as a CTE called categoriesAndRawData.  If you are not familiar with Common Table Expressions, for the purpose of this discussion you can think of it like a named "temporary view".  I now have my CTE and the [PivotOn], [PivotAggregate], and [PivotAnchor] fields.  Step 2= Check.

Step 3: now I have to write a select statement that "includes only the Anchor, Aggregate, and PivotOn fields, plus any "keys to carry through"".  For my first pivot I only want the count of sales by sales person, so here is that query:

WITH categoriesAndRawData AS (

    SELECT YEAR(OrderDate) AS [PivotAnchor],

      MONTH(OrderDate) AS [PivotOn],

      1 AS [PivotAggregate],

      OrderDate, SalesPersonID, TerritoryID FROM Sales.SalesOrderHeader

      WHERE SalesPersonID IS NOT NULL AND TerritoryID IS NOT NULL

    )

  SELECT PivotAnchor, PivotAggregate, PivotOn, SalesPersonID FROM categoriesAndRawData;

(We will apply the pIn alias to this when we convert it to a subselect in Step 4).

Since for this first query I don't care about the territory, I can exclude it from the query and I can just include the Anchor, Aggregate, "On", and the keys to carry through.  Step 3 = Check.

Step 4: Now I have to wrap my bottom query from Step 3 in parentheses, and alias it  AS pIn.  Then I need to add a PIVOT operator after this, put an open parenthesis, and then put the text SUM(pIn.PivotAggregate) FOR pIn.PivotOn   .  Then I put a space and add an IN clause with my categories, and close out the pivot with a close parenthesis and the pr alias.  For this query, since I am breaking out the data into month categories, that second part looks like this: IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) pr

The whole "Pivot" section including the original CTE, the minimalist query wrapped in parentheses and aliased as pIn, plus my PIVOT clause looks like this (though this query won't yet run):

  WITH categoriesAndRawData AS (

    SELECT YEAR(OrderDate) AS [PivotAnchor],

      MONTH(OrderDate) AS [PivotOn],

      1 AS [PivotAggregate],

      OrderDate, SalesPersonID, TerritoryID FROM Sales.SalesOrderHeader

      WHERE SalesPersonID IS NOT NULL AND TerritoryID IS NOT NULL

    )

  --ToDo: Fill in this part...

  (SELECT PivotAnchor, PivotAggregate, PivotOn, SalesPersonID FROM categoriesAndRawData) AS pIn

    PIVOT (SUM(pIn.PivotAggregate) FOR pIn.PivotOn

      IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) pr;

Now all I have to do is fill in the part above the pIn subquery.  I can insert the following text above the subquery (replacing the ToDo comment above):

  SELECT pr.PivotAnchor AS [SaleYear],

      pr.SalesPersonID,

      [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]

Which gives me this as the final query:

WITH categoriesAndRawData AS (

    SELECT YEAR(OrderDate) AS [PivotAnchor],

      MONTH(OrderDate) AS [PivotOn],

      1 AS [PivotAggregate],

      OrderDate, SalesPersonID, TerritoryID FROM Sales.SalesOrderHeader

      WHERE SalesPersonID IS NOT NULL AND TerritoryID IS NOT NULL

    )

  SELECT pr.PivotAnchor AS [SaleYear],

      pr.SalesPersonID,

      [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]

    FROM (SELECT PivotAnchor, PivotAggregate, PivotOn, SalesPersonID FROM categoriesAndRawData) AS pIn

    PIVOT (SUM(pIn.PivotAggregate) FOR pIn.PivotOn

      IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) pr;

image

Hooray - pivoted data.  I can add an ORDER BY at the bottom if I want to for example consolidate my years together and then show each sales person for that year.

WITH categoriesAndRawData AS (

    SELECT YEAR(OrderDate) AS [PivotAnchor],

      MONTH(OrderDate) AS [PivotOn],

      1 AS [PivotAggregate],

      OrderDate, SalesPersonID, TerritoryID FROM Sales.SalesOrderHeader

      WHERE SalesPersonID IS NOT NULL AND TerritoryID IS NOT NULL

    )

  SELECT pr.PivotAnchor AS [SaleYear],

      pr.SalesPersonID,

      [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]

    FROM (SELECT PivotAnchor, PivotAggregate, PivotOn, SalesPersonID FROM categoriesAndRawData) AS pIn

    PIVOT (SUM(pIn.PivotAggregate) FOR pIn.PivotOn

      IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) pr

    ORDER BY pr.PivotAnchor, pr.SalesPersonID ASC;

image

From this screenshot, we can see that the sales data in AdventureWorks2008R2 starts in July 2005.

So what's so good about PIVOT and the use of this pattern?  I could have achieved this by doing normal groups and aggregate functions - true, but this query is now quite flexible.  For example, if I now want to change the query to group everything by territory, I can just change it to the following (different code is underlined and in red):

WITH categoriesAndRawData AS (

    SELECT YEAR(OrderDate) AS [PivotAnchor],

      MONTH(OrderDate) AS [PivotOn],

      1 AS [PivotAggregate],

      OrderDate, SalesPersonID, TerritoryID FROM Sales.SalesOrderHeader

      WHERE SalesPersonID IS NOT NULL AND TerritoryID IS NOT NULL

    )

  SELECT pr.PivotAnchor AS [SaleYear],

      pr.TerritoryID,

      [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]

    FROM (SELECT PivotAnchor, PivotAggregate, PivotOn, TerritoryID FROM categoriesAndRawData) AS pIn

    PIVOT (SUM(pIn.PivotAggregate) FOR pIn.PivotOn

      IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) pr

    ORDER BY pr.PivotAnchor, pr.TerritoryID ASC;

image

If I wanted to group by territory and sales person ID, I could do that too:

WITH categoriesAndRawData AS (

    SELECT YEAR(OrderDate) AS [PivotAnchor],

      MONTH(OrderDate) AS [PivotOn],

      1 AS [PivotAggregate],

      OrderDate, SalesPersonID, TerritoryID FROM Sales.SalesOrderHeader

      WHERE SalesPersonID IS NOT NULL AND TerritoryID IS NOT NULL

    )

  SELECT pr.PivotAnchor AS [SaleYear],

      pr.TerritoryID, pr.SalesPersonID

      [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]

    FROM (SELECT PivotAnchor, PivotAggregate, PivotOn, TerritoryID, SalesPersonID FROM categoriesAndRawData) AS pIn

    PIVOT (SUM(pIn.PivotAggregate) FOR pIn.PivotOn

      IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) pr

    ORDER BY pr.PivotAnchor, pr.TerritoryID, pr.SalesPersonID ASC;

image

From this screenshot we can see that sales person 275 made sales in both territory 2 and 3 in 2005.

Lastly, following this pattern makes it easy to retrieve your text descriptions.  Just be sure to remember to do this after the fact - PIVOT is very fast when it's dealing with a minimalist data set, but it can get bogged down quickly when more columns are included in your pIn subselect*.  Remember the guiding principle of step 3 and include "only the Anchor, Aggregate, and PivotOn fields, plus any "keys to carry through" ".  So we just join in the people and territory info at the end using standard techniques for joining tables with foreign key relationships.  This is done outside the pivot - meaning we join to the results of the pivot not categoriesAndRawData or pIn.

WITH categoriesAndRawData AS (

    SELECT YEAR(OrderDate) AS [PivotAnchor],

      MONTH(OrderDate) AS [PivotOn],

      1 AS [PivotAggregate],

      OrderDate, SalesPersonID, TerritoryID FROM Sales.SalesOrderHeader

      WHERE SalesPersonID IS NOT NULL AND TerritoryID IS NOT NULL

    )

  SELECT pr.PivotAnchor AS [SaleYear],

      pr.TerritoryID, pr.SalesPersonID,

         p.FirstName, p.LastName, st.Name AS [TerritoryName],

      [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]

    FROM (SELECT PivotAnchor, PivotAggregate, PivotOn, TerritoryID, SalesPersonID FROM categoriesAndRawData) AS pIn

    PIVOT (SUM(pIn.PivotAggregate) FOR pIn.PivotOn

      IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) pr

       INNER JOIN Person.Person p ON p.BusinessEntityID = pr.SalesPersonID

       INNER JOIN Sales.SalesTerritory st ON st.TerritoryID = pr.TerritoryID

    ORDER BY pr.PivotAnchor, st.Name, p.LastName, p.FirstName ASC;

 

image 

 

I have purposefully been very prescriptive with naming in this pattern.  You can feel free to name the things whatever you want, but this is what I have found works well for me to use the various layers of the PIVOT without having to rethink "what the heck does this represent again?".  The only fields you actually have to use in your main query (the ones in the pivoted result set) are aliased with "pr." which is fairly quick to type/easy to remember.

*Edit July 9, 2012: fixed this to pIn - it has previously incorrectly said categoriesAndRawData.