SQL Server, Analytics, .Net, Machine Learning, R, Python
Mitch Wheat has been working as a professional programmer since 1984, graduating with a honours degree in Mathematics from Warwick University, UK in 1986. He moved to Perth in 1995, having worked in software houses in London and Rotterdam. He has worked in the areas of mining, electronics, research, defence, financial, GIS, telecommunications, engineering, and information management. Mitch has worked mainly with Microsoft technologies (since Windows version 3.0) but has also used UNIX. He holds the following Microsoft certifications: MCPD (Web and Windows) using C# and SQL Server MCITP (Admin and Developer). His preferred development environment is C#, .Net Framework and SQL Server. Mitch has worked as an independent consultant for the last 10 years, and is currently involved with helping teams improve their Software Development Life Cycle. His areas of special interest lie in performance tuning
Sunday, February 27, 2011
Powershell: Get SQL Server Default File Paths using SMO
I recently needed to find the location of SQL Server’s default data file path in order to create multiple database data files as part of an automated production install. After looking at and discarding a few options that included reading the registry directly, SQL Server Management Objects (SMO) seemed a logical choice. Talking to one of my colleagues, Piers, whose Powershell wizardary has to be experienced to fully appreciate, we (well he!) fired up a Powershell GUI and we took a look at the methods available.
As an aside, if you are not aware of this ‘trick’ it’s worth explicitly mentioning:
In Powershell, first load the relevant assembly into memory (which in this instance is Microsoft.SqlServer.Smo):
> [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") > $null
Then, create an instance of the type you are interested in (the Server type):
> $smoServer = new-object Microsoft.SqlServer.Management.Smo.Server “servername”
and pipe the object instance default method output through Get-Member to list all the Events, Methods and Properties exposed:
> $smoServer gm
So having done that we found a property named DefaultFile which looked promising, but it just returned an empty string. After a bit of digging around, it transpires that it only returns a path if the current location is different to where the master DB is located, so here it is in Powershell:
Update: Piers pointed out that Books Online contains a useful section on programming tasks using SQL Server Management Objects (SMO)
Friday, February 25, 2011
Visual Studio Database Guide
CodePlex is hosting an excellent whitepaper created by the Visual Studio ALM Rangers, containing guidance on working with Visual Studio 2010 Database projects:
You can download the Visual Studio Database Guide here.
Thursday, February 24, 2011
SQL Sentry Plan Explorer
It may not be the greatest named application out there but SQL Sentry Plan Explorer is a free, lightweight, standalone tool that improves dealing with SQL Server query plans so much, you’ll wonder why it hasn’t been incorporated into SSMS (and let’s face it, viewing plans in SSMS really sucks!).
There are several ways to open a plan: In SSMS, right-click a graphical plan and select “Show Execution Plan XML”, then copy and paste the plan XML into Plan Explorer. Or, save an execution plan from SSMS to a .sqlplan file, then open the file from Plan Explorer.
Download SQL Sentry Plan Explorer here.
Service Pack 1 (SP1) for Windows 7 and Windows Server 2008 R2
Service Pack 1 for Windows Server 2008 R2 and Windows 7 has been released. Get it here.
Thursday, February 17, 2011
TSQL: Round to Nearest 15 Minute Interval
A colleague asked me if I had any TSQL to hand that would round down to a 15 minute interval and also round to nearest 15 minute interval. A quick search found several formulas but several had rounding errors. Here are both without any rounding errors.
declare @adate datetime = '2010/02/15 23:59:00'
-- The epoch, or start of SQL Server time: '1900-01-01 00:00:00.000'
-- select cast(0 as DateTime) as Epoch
-- Both these formulas will only work until '5983-01-24 02:07:00.000' !!
-- select dateadd(n, 2147483647, cast(0 as DateTime))
-- Round down to nearest 15 minute interval (avoiding any rounding issues)
select dateadd(n,(DATEDIFF(n, cast(0 as DateTime), @adate)/ 15) * 15, cast(0 as DateTime))
-- Round to nearest 15 minute interval (avoiding any rounding issues)
select dateadd(n,((DATEDIFF(n, cast(0 as DateTime), @adate) + 7)/ 15) * 15, cast(0 as DateTime))
As noted, they have the limitation of working only until 5983 AD, but I figure I won’t be around!
Saturday, February 12, 2011
How to Configure SQL Server 2005 to allow Remote Connections
Just so I remember where this article is (also applies to SQL Server 2008 Express):
Friday, February 11, 2011
Mark Russinovich: “Zero Day”
His fiction debut, Zero Day, is set in a world completely reliant on technology (sounds familiar), and surrounds the events of cyber infrastructure attacks released on a largely unprepared world.
Sunday, February 06, 2011
How to give great presentations
Assuming your user group is actually holding meetings :), User Group Support Services (UGSS) have released a series of videos on “How to give great presentations” aimed at first time speakers and anyone wanting to improve their skills (I know I need to):
MSN, Email: mitch døt wheat at gmail.com