Admin: Change drive and directory in CMD

Ok, so its not SQL based but one of the things I ALWAYS forget is the syntax to move quickly to a directory on a different drive when using the command prompt.

If i'm on c:\Dog and want to move to d:\Cat then simply issuing the following command doesn't cut the mustard:

C:\Dog\cd d:\Cat

Instead, I need to issue it with the /D command

C:\Dog\cd /D d:\Cat

Why I Love Conflict!!

Ok, so thats a bit strong. I don't like conflict for conflicts sake. But i do love the benefits that healthy conflict can bring to successful teams.



Disagreement

Disagreeing isn't easy. But it is important. And remember, just because you're the only one disagreeing, it doesn't mean you are wrong. The fact is that there are always at least 2 sides to a story and from a problem solving perspective, there are always options- you just may not like them! People who are not afraid to challenge can be misconstrued as being difficult or contrary or awkward. But, often they are just playing Devils Advocate and are just presenting all the options or the potential flip side to the coin and I think this is a valuable trait.

It's important that people demonstrate the courage to have an opinion even if its one thats not popular or shared. It's all part of the feedback process which promotes discussion, ideas and solutions and often leads to successful teams. This can be a culture shock for some but in the long run, the team benefits and you generally get a better result.

If everyone buys in the cause and people trust that they are heading in the same direction, a bit of conflict can be very healthy. People like to use the phrase, singing from the same song sheet and in general, I agree with the sentiment - lets all work together for a common goal. But this should NOT be lead to blind acquiescence. There is a danger that having an opinion that is different to the majority or the key influencers marks you out as someone who is not a team player, a tag which nobody wants. As a result, this can lead to people just going along with the consensus so as not to "rock the boat", avoiding conflict which is not in the long term interests of the team.

People can be tagged as obstructive or blockers rather than "can do". Beware. While I acknowledge that these people do exist, you need to check their motives. If they are doing so for personal gain then yes they are negative and are trying to "slow the bus down" for the wrong reasons. It may just be that they are trying to offer balance. Trying to apply the handbrake to stop the bus crashing off the cliff. But remember, if you mark yourself out as someone who is willing to challenge, you must be prepared to take criticism yourself as you'll be wrong often! And above all, never make it personal.

Balance

Sticking with the song sheet analogy - a good song doesn't mean everyone is singing the same notes or perhaps even using their voices at all! You will have people playing different instruments and then within a set of singers you may have some who sing high and some low. Its harmonising. There has to be a balanced set of voices. Too much of the same and it doesn't work.

You need some brakemen and some accelerators so that you have a controlled efficient vehicle which can move forward at pace without losing control. Try a football analogy. Too many brakers and you have Arsenal - a good team without being great, just falling short perhaps as a result of not inveseting. Too many accelerators and you have Leeds, living the dream and crashing and burning.

And do not let people think that doing the boring/admin/BAU work is not worthy. All the eye-catching football, the cool development stuff, the good ideas - they all need to be built on solid foundations. Again, think football and the water carrier. You need a Deschamps to do the dirty work to allow Zidane to flourish. And at a personal level, even the worlds best footballers are able to do the basics. You don't think that Ronaldinho can only do fancy flicks and tricks? No, he's built his skills on a foundation of solid basics. The same applies to IT.

Adaptability

So back to the world of development. I like to think I am adaptable. I recognise the need for both roles - driver and brakeman and depending on the position I am in, I will adopt either role. In a highly autocractic environment with lots of red tape, it may be necessary to be a bit more cavalier to try and break some of the resistance to change. In a company where there is minimal control and change is virtually omnipresent, it is necessary to try and apply the brake and introduce process for the sake of stability. After all, while I love change- i also love stability and scalability.

From a personal perspective, an area where I am trying to improve is getting my point across effectively- getting buy in - as it can be difficult for people to understand somone with extreme opinions, be it braker or driver. It is often only after a period of time (and some clashes!) that people begin to understand your motives and give you the respect which lets your message in.

I dare say there are a mixture of people and styles in your work. Spot the gap, spot the problems and fill that niche. You can bring balance and a smoother ride for all.

T-SQL: Table Variables Ignore Rollbacks

I've been spending more and more time on SQL Server Central over the past month or so and am finding their Question of the Day series fun (and educational!). The question from Oct 6th, 2010 particularly caught my eye and I thought i'd jot down an example here to illustrate its point:

Look at the differences between the 2 sql batches, one using a temp table and one using a table variable:

Script 1: Temp Table

CREATE TABLE #T (i INT)

INSERT INTO #T SELECT 1
INSERT INTO #T SELECT 2
INSERT INTO #T SELECT 3

BEGIN TRAN
INSERT INTO #T SELECT 4
INSERT INTO #T SELECT 5
INSERT INTO #T SELECT 6
ROLLBACK TRAN

-- only 3 rows
SELECT * FROM #T

Script 2: Table Variable

DECLARE @t TABLE (i INT)

INSERT INTO @t SELECT 1
INSERT INTO @t SELECT 2
INSERT INTO @t SELECT 3

BEGIN TRAN
INSERT INTO @t SELECT 4
INSERT INTO @t SELECT 5
INSERT INTO @t SELECT 6
ROLLBACK TRAN

-- 6 rows!!
SELECT * FROM @t


Although the SQLServerCentral article suggests there isn't any documentation, there is a comment in Books Online which does explain the behaviour albeit that its not that easy to find.

http://msdn.microsoft.com/en-us/library/ms175010.aspx

"because table variables have limited scope and are not part of the persistent database, they are not affected by transaction rollbacks."

SSIS: Excel Source Causes NULLs

I've come across a strange issue using SQL2008 recently trying to import data from an Excel datasource. Essentially, i was using the Unpivot task in order to import some legacy time series data to a SQL table. Some of the data in the columns was incomplete for the oldest dates so some cells were blank early on and this is where the problems came. Essentially, for those columns with "leading" blanks, I was getting no data at all for any of the dates once it had been unpivotted. Using the dataviewer I was able to establish that the problem was with the Excel Source component.

Hopefully, the screenshots will show the issue:

Excel Source

Note here how there are leading blanks in 2 of the columns.



SSIS Package

Nothing fancy in this package. Just a simple task to unpivot that data into a normalised table.



Datagrid View

But look what happens when we run it. The Datagrid View shows us that the 3s in ColC have been replaced by NULLs. This will have ramifications for the data as these will not be imported into the database.



So why has this happened? The key is with the behaviour of the Excel driver. Essentially, it will guess the datatypes of the values in the columns based upon what is in the first 8 rows. If it there is a conversion error then the value will be exposed as a NULL and this is exactly what is happening in this example. The values in ColB are unaffected as there are values in the sampled 8 rows whereas ColB has blanks for the first 8 rows and it assumes that anything after this will be Text.

You can read about the behaviour here where it tells you of the workaround which is to:

"add IMEX=1 to the value of Extended Properties in the connection string of the Excel connection manager in the Properties window"

Example:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\SSIS\TestExcelSource.xls;Extended Properties="EXCEL 8.0;HDR=YES";

T-SQL: Deduping with the CTE

NOTE: I'm re-publishing this blog post to join in T-SQL Tuesday #18 hosted by Bob Pusateri (blog | @SQLBob). This post had previously been published on 08/04/2011.


I blogged a while back on just how much I love CTEs due to their simplicity, ease of use, intuitiveness and flexibility. One of my most common uses of them is removing duplicates from a table. Its easy enough to write a query to find dupes, especially with the ranking functions but the great thing about using a CTE is that you can run your UPDATE/DELETE command on the resultant CTE and it effects the base table. This means you don't have to run a separate query to find the dupes and then join this back to your original table.

Here's an example:

-- set up our test dataset of all FIFA World Cup Winners
CREATE TABLE dbo.WorldCupWinners (Yr DATE, Winner NVARCHAR(255))
INSERT INTO dbo.WorldCupWinners VALUES ('19300101', 'Uruguay')
INSERT INTO dbo.WorldCupWinners VALUES ('19340101', 'Italy')
INSERT INTO dbo.WorldCupWinners VALUES ('19380101', 'Italy')
INSERT INTO dbo.WorldCupWinners VALUES ('19500101', 'Uruguay')
INSERT INTO dbo.WorldCupWinners VALUES ('19540101', 'Germany')
INSERT INTO dbo.WorldCupWinners VALUES ('19580101', 'Brazil')
INSERT INTO dbo.WorldCupWinners VALUES ('19620101', 'Brazil')
INSERT INTO dbo.WorldCupWinners VALUES ('19660101', 'England')
INSERT INTO dbo.WorldCupWinners VALUES ('19700101', 'Brazil')
INSERT INTO dbo.WorldCupWinners VALUES ('19740101', 'Germany')
INSERT INTO dbo.WorldCupWinners VALUES ('19780101', 'Argentina')
INSERT INTO dbo.WorldCupWinners VALUES ('19820101', 'Italy')
INSERT INTO dbo.WorldCupWinners VALUES ('19860101', 'Argentina')
INSERT INTO dbo.WorldCupWinners VALUES ('19900101', 'Germany')
INSERT INTO dbo.WorldCupWinners VALUES ('19940101', 'Brazil')
INSERT INTO dbo.WorldCupWinners VALUES ('19980101', 'France')
INSERT INTO dbo.WorldCupWinners VALUES ('20020101', 'Brazil')
INSERT INTO dbo.WorldCupWinners VALUES ('20060101', 'Italy')
INSERT INTO dbo.WorldCupWinners VALUES ('20100101', 'Spain');

-- we only want to get the distinct countries that have won
-- along with their first triumph which will be the FirstWin = 1
WITH wins
AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY Winner ORDER BY Yr ASC) AS FirstWin
FROM dbo.WorldCupWinners
)
DELETE
FROM wins -- note how we're deleting from the CTE and NOT the base table
WHERE FirstWin > 1
GO

-- And hey presto, we have our desired output
SELECT *
FROM dbo.WorldCupWinners

/*
Yr         Winner
1930-01-01 Uruguay
1934-01-01 Italy
1954-01-01 Germany
1958-01-01 Brazil
1966-01-01 England
1978-01-01 Argentina
1998-01-01 France
2010-01-01 Spain
*/


Powershell: Script out SQL Objects

I'm still committed to realising my goal this year of using Powershell more, and this week I've come across a bonafide use for it. The use case I'm dealing with is the ever troublesome SQL source control issue and the connected nature of developing SQL objects (as opposed to scripts).

My idea was to just have a scheduled job to script out all the relevant SQL scripts from my shared development database server to a repository and then have these checked in to my source control provider. I'm not quite there yet, but I thought i'd share my script for scripting out objects and one of the cool short hands I found in PS.

Its stored as a ps1 script and you pass in the server you wish to interogate:

param($sqlserver)

$filePath = "C:\SQLScripts\Tables_" + $sqlserver + "_"

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-null

$srv = new-object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver
$options = new-object "Microsoft.SqlServer.Management.Smo.ScriptingOptions"
$options.AppendToFile = $true
$options.ToFileOnly = $true
$options.ClusteredIndexes = $true
$options.NonClusteredIndexes = $true
$options.DriAll = $true

foreach($db in $srv.Databases | where {$_.IsSystemObject -eq $false})
{
   $options.FileName = $filePath + $db.Name + ".sql"

   $tables = $db.Tables

   foreach($table in $tables | where {$_.IsSystemObject -eq $false})
   {
       $table.Script($options)

   }
}

The cool short hand i found was the ability to include a WHERE clause in the foreach loop essentially removing the need for a further if block within each loop:

foreach($table in $tables | where {$_.IsSystemObject -eq $false})

This is my first cut at doing this and I'll be looking to extend this to parameterise the scripts location and perhaps change to individual files. Still, another step on the powershell journey!