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
Monday, January 28, 2013
31 Characters Should be Enough for Anyone, Right?
I’ve always had a good laugh at Oracle for having a 30 character limit on table/column/index names (and probably other objects I don’t know about)
Mentioned here on StackOverflow:
While writing SQLDiagCmd (a runner for Glenn Berry’s SQL Server diagnostic scripts), I re-discovered that Excel 2010 still has a limit of 31 characters for Worksheet names (and several weird bits of behaviour relating to that limit). Really?!? Why would anyone want more than 31 characters for a work sheet name? It is 2013 right, not 1970?
Add that to the fact that worksheet names have to be unique (I understand the need for that), and Voila! unnecessary code to guarantee uniqueness with 31 characters! Someone please tell me there’s a way to override this ludicrous limit…
Friday, January 25, 2013
.NET: Parse Structured Text Files
I wasn’t aware of the handy TextFieldParser class, that parses a text file in a reader oriented way:
Maybe because it’s in the Microsoft.VisualBasic.FileIO namespace… How to: Read From Comma-Delimited Text Files in Visual Basic
Sure, you can write one yourself, but it’s handy for quick spikes:
using (var reader = new TextFieldParser(@"c:\logs\bigfile"))
Thursday, January 17, 2013
SQLDiagCmd: a Standalone Runner for Glenn Berry’s SQL Server Diagnostic Scripts
If I said that I use Glenn Berry’s SQL Server diagnostic queries a fair bit it would be an understatement. Glenn releases versions of these very useful TSQL scripts (comprised of 50 plus queries) and a blank Excel Template to save the results in.
Rather than running each query individually, highlighting the results in SSMS and then copying and pasting to Excel, I decided to write a standalone, no install required runner that would do the donkey work for me:
The only (optional) alteration to Glenn’s scripts, is a small addition to the comment line which describes each query, that is used as the worksheet name (if this is missing, it uses the text at the start of the comment, but this won’t be unique due to Excel’s ridiculous limitation of having a 31 character maximum length for worksheet names!).
So, for each comment preceding a query, instead of:
-- SQL Server Services information (SQL Server 2008 R2 SP1 or greater) (Query 5)
I've altered to explicitly specify what text to use as the worksheet name (in bold):
-- SQL Server Services information (SQL Server 2008 R2 SP1 or greater) (Query 5)(SQL Server Services Info)
I’ve emailed Glenn to see if I can get this addition in future scripts.
You can download version 1.0 of the zipped executable from here: SQLDiagCmd.zip (targets .NET Framework 4.0)
OK, How do I run it?
Download Glenn’s diagnostic script that targets your version of SQL Server and download SQLDiagCmd.exe.
Assuming they are in the same directory:
SQLDiagCmd.exe -E -S MUTLEY -i ".\SQL Server 2008 Diagnostic Information Queries (January 2013).sql"
-o c:\temp -d "AdventureWorks2008R2;AnotherUserDatabase" -A
Typing SQLDiagCmd.exe –-help displays usage and parameters:
Usage: SQLDiagCmd -E -S MUTLEY -i queries.sql -o C:\outputfolder -d databaselist –A
MSN, Email: mitch døt wheat at gmail.com