Tuesday, August 14, 2012

How to tell which SQL Server version, service pack, edition, etc. is running


Execute the following query:
SELECT  
    SERVERPROPERTY('productversion') as 'Product Version', 
    SERVERPROPERTY('productlevel') as 'Patch Level',  
    SERVERPROPERTY('edition') as 'Product Edition',
    SERVERPROPERTY('buildclrversion') as 'CLR Version',
    SERVERPROPERTY('collation') as 'Default Collation',
    SERVERPROPERTY('instancename') as 'Instance',
    SERVERPROPERTY('lcid') as 'LCID',
    SERVERPROPERTY('servername') as 'Server Name'

 
This useful info came from this blog post.

Friday, August 10, 2012

Perform an action on (e.g., "drop") all tables in a SQL Server database

Supposed you want to perform an action on all tables in a SQL Server database.  (E.g., delete or truncate every table.)

You could generate a list of all the tables in the database by (first connecting to the database you want then) executing:
SELECT * FROM sys.Tables Order by name ASC
 Or you could use the hidden Microsoft stored procedure, sp_MSforeachtable.
EXEC sp_MSforeachtable @command1 = "PRINT '?'"
      --print the tables names with owner(dbo)


EXEC sp_MSforeachtable @command1 = "SELECT * FROM ?" 
      -- Selects all the rows form all the table
Or more destructively:
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"
EXEC sp_MSforeachtable @command1 = "DELETE FROM ?"
EXEC sp_MSforeachtable @command1 = "TRUNCATE TABLE ?"
This was gleaned from this blog post.

Friday, July 27, 2012

File Types tab missing -- NirSoft to the rescue

Windows 7 and (worse) Windows Server 2008 no longer provide a GUI to fully edit file type associations.

Earlier Windows versions (e.g., XP) allowed you to edit this via the Windows file system explorer menu Tools > Folder Options... dialog, the File Types tab.   There you could define which program is associated with each file extension:


If you pushed "Advanced", you could then view the various file operations that could be performed:


And you could redefine how the programs were actually invoked to perform each action:


The "File Types" tab is missing in Windows 7 and Windows Server 2008.

To the rescue is NirSoft with their FileTypesMan utility.



Wednesday, June 27, 2012

Alway give extremely verbose error information

When programs encounter errors, they should always make available detailed information about what actually went wrong. If it's too verbose, or too technical for the expected audience, it should still be made available; either behind a "Details" button, or written in a log file.

An anti-example is the when the awesome tool, ReSharper, runs an NUnit test, it can first automatically build any changed projects. (Nice.) But when that build fails, it says "Build failed". That's it. No output in the build output or errors windows. No nothing.

(I found this nice blog post explaining what the problem was.)

A corollary of this philosophy is that when catching an exception and throwing a different exception, you should always nest/wrap/chain the caught exception as the inner exception of the new one. The outer exception might repeat information from the inner exception; include the inner exception anyway. The inner exception may be verbose, confusing, etc.; include it anyway.

(The only, rare "exception" to this rule is when the inner exception reveals sensitive information -- credentials, HIPPA info, etc. This is usually code running on a server, in which case the inner exception should instead be logged into a secure log file for possible later problem analysis.)

Monday, June 11, 2012

Outlook does not include Deleted Items in Instant Search by default

Outlook's Instant Search doesn't include Deleted Items in the search results by default.

You can change this by enabling the corresponding checkbox in the Search Options dialog.

See this article, the section titled "INCLUDE THE DELETED ITEMS IN INSTANT SEARCH".

In Outlook 2010, the dialog looks like this:


Wednesday, May 16, 2012

How .NET Exception stack traces are broken. How they can be fixed.

Unlike Java, .NET Exception stack traces are Less Than Worthy. There are many ways this is true. Today we will discuss how exception stack traces get lost when rethrown.

If you don't need to be convinced that this is a bug, not a feature, you can skip ahead to the workaround.

The Problem


Imagine a program where Method1 calls Method2 calls Method3 calls Method4 which throws an exception. Imagine that one of these intermediate methods, Method2, needs to catch the exception, do some stuff (not shown), and then rethrow the caught exception.

        public static void MyMain()
        {
            try { Method1(); } catch (Exception e) { Console.WriteLine(e); }
        }

        public static void Method1()
        {
            Method2();
        }

        public static void Method2()
        {
            try { Method3(); } catch (Exception e) { throw e; }
        }

        public static void Method3()
        {
            Method4();
        }

        public static void Method4()
        {
            throw new Exception("From Method4");
        }


The ultimate caller, MyMain, prints the exception, including it's stack trace. Here's what it prints:
System.Exception: From Method4
   at MiscCS4Tests.PreserveExceptionStackTrace2.Method2()
   at MiscCS4Tests.PreserveExceptionStackTrace2.Method1()
   at MiscCS4Tests.PreserveExceptionStackTrace2.MyMain()

 Wait, what happened to Method3 and Method4?

The problem is that, when C# throws an exception, it throws away (no pun intended) all stack trace information and starts over.

That is never what I want. (And if it was, I would more likely create a new exception object to throw for other reasons.)

There are ways around this:
  • Instead of throw e; use throw;  That will preserve the stack trace. But this form of throw is only allowed inside a catch block.
  • Create & throw a new exception object that nests the caught exception as the "inner exception". That will retain the full stack trace, if you chain through all the nested exceptions (which Exception.ToString does).
The problem is that often you are no longer in the catch block when you want to rethrow. And neither do you want to nest the exception merely to preserve the stack trace, since that typically requires changing the Exception type (unless you use reflection to create the exact same Exception sub-type that you caught, assuming you understand it's constructor).

As a typical example, suppose that you catch several different explicit exceptions using different catch blocks, but the body of the catch blocks are identical.

            try {
                AMethod();
            } catch (ArgumentException e) {
                DoSomething();
                throw;
            } catch (IOException e) {
                DoSomething();
                throw;
            }


As a good programmer, you consider duplicated code to be evil, so you refactor it into a shared place. Perhaps a separate method; more often inline like this:

            Exception ex = null;
            try {
                AMethod();
            }
            catch (ArgumentException e) { ex = e; }
            catch (IOException e)       { ex = e; }
            if (ex != null) {
                DoSomething();
                throw ex;
            }


But however you do it, you're no longer in a catch block when you rethrow. So you have to explicitly specify the exception on the throw. And you've lost your stack trace.

The Workaround


It turns out that Microsoft realized that they needed to preserve (remote) stack traces when rethrowing remote exceptions. So they built that functionality into the Exception class. Too bad they didn't realize the rest of us needed it too; so they made it private.

Here's a handy Exception extension method that will use reflection to invoke this private method. 

    public static class ExceptionHelper
    {
        public static void PreserveStackTrace(this Exception e)
        {
            _internalPreserveStackTrace(e);
        }

        private static readonly Action<Exception> _internalPreserveStackTrace =
                (Action<Exception>)Delegate.CreateDelegate(
                    typeof(Action<Exception>),
                    typeof(Exception).GetMethod(
                                "InternalPreserveStackTrace",
                                BindingFlags.Instance | BindingFlags.NonPublic));
    }
 

Here's Method2 (from the above example) rewritten to use this:

        public static void Method2()
        {
            try { Method3(); }
            catch (Exception e) { e.PreserveStackTrace(); throw e; }
        }


And here is the stack trace now:

System.Exception: From Method4
   at MiscCS4Tests.PreserveExceptionStackTrace2.Method4() in xxx.cs:line 170
   at MiscCS4Tests.PreserveExceptionStackTrace2.Method3() in xxx.cs:line 165
   at MiscCS4Tests.PreserveExceptionStackTrace2.Method2() in xxx.cs:line 159
   at MiscCS4Tests.PreserveExceptionStackTrace2.Method2() in xxx.cs:line 161
   at MiscCS4Tests.PreserveExceptionStackTrace2.Method1() in xxx..cs:line 154
   at MiscCS4Tests.PreserveExceptionStackTrace2.MyMain() in xxx..cs:line 149

Note that Method2 appears twice in the trace: once where the exception was caught, and once where it was thrown. This is undesirable but better than losing the rest of the stack trace.

Caveats:
  • There are security restrictions for using reflection. Refer to the Accessing Members That Are Normally Inaccessible section in this article.
  • This depends on internal implementations of the Exception class which could change. Although, if that happens, you can at least rewrite this extension method to use less efficient, but publicly supported, interfaces as described here. This involves serializing & deserializing the exception.
     

Monday, March 5, 2012

Examining SQL Server sessions

I recently needed to see the number of SQL Server sessions connected to a database, and what they were doing.

There are these handy stored procedures to list the current (and currently executing) sessions in a SQL Server database.

sp_who: Comes pre-installed. Lists session currently connected to a database. More info here.



But typically people prefer sp_who2, which displays more information.

sp_who2: Comes pre-installed. Lists session currently connected to a database. More info here.


But to easily see the active sessions, and what they're currently doing, you want sp_who3.

sp_who3: You need to install this yourself.  (Definition is here.) Lists only sessions with a currently executing request, along with what that SQL statement is.




Tuesday, January 31, 2012

ASP.NET limits the number of form variables

Did your form post suddenly stop working?

On Dec. 29, 2011, Microsoft released a security fix through Windows Update (MS11-100). Among other things, this fix limits the maximum number of form variables, files, and JSON members in an HTTP request to 1000.

As described in http://support.microsoft.com/kb/2661403, HTTP clients that make these kinds of large requests will be denied, and an error message will appear in the web browser. The error message will usually have an HTTP 500 status code.

But the catch is, the only true indication that you're encountering this problem is the specific exception stack trace visible in the event log (or in the rendered ASP.NET page, if your web.config <customErrors mode="..."> allows it).  An example:
[InvalidOperationException: Operation is not valid due to the current state of the object.]
   System.Web.HttpRequest.FillInFormCollection() +468
   System.Web.HttpRequest.get_Form() +79
   System.Web.HttpRequest.get_HasForm() +73
   System.Web.UI.Page.GetCollectionBasedOnMethod(Boolean dontReturnNull) +54
   System.Web.UI.Page.DeterminePostBackMode() +90
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +268

This new Microsoft limit can be configured on a per-application basis in web.config. In the <appSettings> section, add a new key for "aspnet:MaxHttpCollectionKeys" with an appropriately large value for your specific site:
 <add key="aspnet:MaxHttpCollectionKeys" value="2000" />

There is a separate limit for JSON members:
 <add key="aspnet:MaxJsonDeserializerMembers" value="2000" />

Note: If you are using ASP.NET 1.1, the setting is adjusted by adding a DWORD value to registry keys; see http://support.microsoft.com/kb/2661403.