January 2009

You are currently browsing the monthly archive for 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:

image

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.

image

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.

Share

Tags: , ,

Some months ago I decided to start helping people on Experts Exchange by subscribing myself as an Expert and answering questions when I have some spare time.  I have several motives for this, such as:

  • it helps me to think about technology
  • it’s interesting to read other people’s opinion on a topic of interest
  • I like looking for a solution to a specific problem, to learn and at the same time help someone else
  • it gives a good idea of what technology other people are working with nowadays
  • Free shirts!

Subscribing as an Expert is free and gives you a Limited Member account.

Answering questions gives you Expert Points.  The maximum amount of Expert Points that one can score on a question is 2,000.

Earning a total of 10,000 Expert Points and maintaining a monthly score of 3,000 Expert Points gives you access to Premium Services, for free.  Your account status gets upgraded from Limited Member to Qualified Expert.  One of the interesting parts of Premium Services is that you get Unlimited Question Points (i.e. the points that you need to ask a question yourself).

Expert Points are scored in the zone where the question was asked (max. 3 zones per question).  My focus is currently on all SQL Server related zones.  The first rank that can be earned is Master and you can get it by scoring 50,000 Expert Points in a zone.

About a week ago I earned my first rank, Master in MS SQL Server.

Profile of ValentinoV on Experts Exchange

Currently I have scored a total of 66,604 Expert Points and I have gained these by answering 53 questions.

Answer History

As the screenshot above shows, on Experts Exchange I am known as ValentinoV.  See my profile for up-to-date info.

Gaining a rank gives you the ability to generate an email signature:

Email Signature

Another feature is to generate a certificate with your ranks:

Certificate

Clicking the Create Certificate button will generate a PDF file such as this one.

And finally, you get a free shirt which specifies your rank on the sleeve.  Looking forward to receiving it.

Free shirt

As you see, it earns to be an Expert. :-)

Share

Tags: ,

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…

image

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.

Share

Tags: , ,

Have you ever had the situation that you wrote an MDX query to get some data out of a cube in Management Studio and executed it and waited and waited and the query never seems to end so you decide to cancel it so you click the red square button and then you wait and wait and wait some more and….  Well, it’s a bug.

And the worst part of it, in my opinion, is that, even when you close your client connection to Analysis Services, the query keeps running on the server consuming CPU and memory and will finally take it down.

The good part is that Microsoft has fixed it in Cumulative Update 2 for Service Pack 2 for SQL Server 2005.  And now of course SP3 can be used as well to fix this situation.

The bug is described here: http://support.microsoft.com/kb/935832

Share

Tags: , , , , , , , ,

© 2008-2017 BI: Beer Intelligence? All Rights Reserved