January 2009

I have a habit of using keyboard shortcuts now and then.  In the SQL Server Management Studio I have some favorites like CTRL-N to open a new query window, F7 to get the Object Explorer Details window or CTRL-SHIFT-M to get the popup to fill out the parameters in a generated template.

If you don’t know what I’m talking about you can try the following: in the Object Explorer, right-click the Stored Procedures node under the Programmability node of one of your databases and choose New Stored Procedure.  This will open the Create Procedure template in a new query window.  It looks like this:

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
    -- Add the parameters for the stored procedure here
    <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
    <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

As you can see, it mentions the keyboard shortcut in the comments on top.  Hitting CTRL-SHIFT-M opens the following popup:

In this popup you enter whatever values you’d like to fill in.  When clicking OK, SSMS replaces the placeholders with the filled-in values.

So to cut to the chase, since I started using SSMS2008 I occasionally noticed that these keyboard shortcuts didn’t work anymore.  At first I didn’t give it too much thought but when it started to really bug me I decided to check it out.  And it appears to be a bug of some sort.  There’s a really easy fix though.

Switching to the 2000 keyboard layout and switching back to 2008 will fix the problem.  To switch keyboard layouts, go to Tools > Options.  Under Environment > Keyboard you can select two different keyboard schemes.

A “Thank you” goes to Tibor Karaszi because it was on his blog that I found the solution.

Microsoft is aware of the issue as well, but there appears to be no info on what causes it.

This time I’ll start with the conclusion: it is not a good idea to compress the \Program Files\Microsoft SQL Server\ folder using NTFS compression, not even on a laptop with less than a GB free on the C: partition.  Because if you do, you will get an error when installing SP3 for SQL Server 2005.  (I know, it’s not a best practice in terms of performance but for once that was really not what I was trying to optimize here. )

When going through the installation steps of the service pack, it will start upgrading your components.  And suddenly you get the famous "hotfix.exe has encountered a problem and needs to close" popup.  No explanation or any error description, just the usual popup to sent the error report to Microsoft.  The application event log gave me more info:

Product: Microsoft SQL Server 2005 — Error 28062. SQL Server Setup cannot install files to the compressed or encrypted folder: C:\Program Files\Microsoft SQL Server\. To continue, make sure that your installation directories are not compressed or encrypted, or specify a different directory, and then run SQL Server Setup again.

The folder properties tell me that I will lose about 1.5 GB when I decompress my SQL Server folder.  Luckily I now have 5 GB free (I compressed my whole Program Files folder about a month ago) so that should be okay…

In the meantime the Microsoft SQL Server 2005 Service Pack 3 Setup keeps on running, upgrading the other components.  Only the Database Services component is now showing a failure status.  When all other components are upgraded, you have the option to click the View Summary button in the Installation Complete screen.  This also shows the error mentioned earlier.  And of course you could also open the log file (C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB955706_sqlrun_sql.msp.log) for details.

Sidenote: my C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\ folder currently contains 1.97 GB of log files.  Another folder to clean out once the SP is fully installed.

After decompressing the folder I started setup again.  It selected Database Services as the only remaining component for upgrade.  This all went smoothly and my SQL 2005 is now happily reporting version 9.00.4035.00.

Microsoft is aware of the issue and said in 2005 that it will consider adding the functionality in a future version.  Anyone who wants to try this out on 2008 once the first SP arrives?

If you want to know the inner details of why you should never compress your SQL Server data and log files in a production environment, check out this great article by Ken Henderson.