Month: January 2013

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:

“Not just millions of lines of DBA written code, but plenty of oracle internal code no doubt too. This topic came up in a session with Steven Feuerstein and he said he didn’t think they would ever change it.”

“They couldn’t exactly trumpet it as a new feature, either… they’d spend a lot of time extending the limit, and then announce “you can now use names longer than 30 characters!”. They’d be the laughing stock”

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…

I wasn’t aware of the handy TextFieldParser class, that parses a text file in a reader oriented way:

The TextFieldParser object provides methods and properties for parsing structured text files. Parsing a text file with the TextFieldParser is similar to iterating over a text file, while the ReadFields method to extract fields of text is similar to splitting the strings.

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"))
{
    reader.TextFieldType = FieldType.Delimited;
    reader.Delimiters = new [] { Constants.vbTab };

    while (!reader.EndOfData)
    {
        try
        {
            // If ReadFields encounters blank lines, they are skipped and next non-blank line is returned.
            string[] currentRow = reader.ReadFields();

            // Process row....
            foreach (string currentField in currentRow)
            {

            }

        }
        catch (MalformedLineException ex)
        {
            // Handle malformed line....
        }
    }
}

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:

• It should run from command line (or through a UI)
• It should be standalone, require no installation, and not require Excel or Office to be installed.
• It should work with new (and old) versions of his scripts, and not require updating when the scripts are updated.
• It should not require a blank excel template file.
• It should identify and run Server and database specific queries automatically.
• It should be able to target multiple databases at once.

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 
SQLDiagCmd -S MUTLEY -U username -P password -i queries.sql -o C:\outputfolder -d databaselist –A

 

-S, –servername	Required. Server name or instance to run queries against.
-E	Use a trusted connection (Windows Authentication).
-U, –username	Username for SQL Login.
-P, –password	Password for SQL Login.
-i, –inputfile	Required. Query diagnostic file to run.
-o, –outputfolder	Required. Folder location to write results file.
-A, –autofit	Auto-fit Excel columns.
-t, –timeout	Query timeout in seconds. Defaults to 360 seconds (for longer running queries).
-d, –databases	Semicolon separated list of specific databases to examine. Separate each database with a semicolon. Do not include spaces between databases and semicolon.
–help	Display this help screen

 
The SQLDiagCmd git repository is available at github: SQLDiagCmd

This project uses  EPPlus and the Command Line Parser Library.