As a cost cutting measure, I will be moving this website from PowerDNN to my rack-mounted servers. I'm being forced to cut down on all unnecessary costs. Currently I pay $50/month to host this site and the WheelMUD site. I can't afford that anymore.

I wanted to give everybody a heads up that the site will be down intermittently in the next 5 days. The biggest hurdle will be the DNS servers. The DNS changes can take up to 72 hours for some people.

Just remember, this site is not going away. Don't Panic!  GANGNAM STYLE!

Thanks,

Your invisible and friendly system administrator.

 Just a heads up for everybody that follows me on Twitter. I will be winding down the @systemwidgets account. I'm moving away from software development as a career, and moving into SQL\Data Warehouse development. 

The new Twitter account is @Datasome.

 I was testing one small SSIS package in several different versions of BIDS. I discovered that Visual Studio 2012 (BIDS) does not do a good job of visually distinguising disabled objects.

Here are screenshots for each version I tested:

BIDS 2008

Clearly visible differences.

BIDS 2010

Still distinguishable.

BIDS 2012

Uh-oh. Houston we have a problem!

This issue has been reported over at Microsoft's Connect website. Here's the link: SSIS in VS2012 - Way too hard to distinguish disabled items - accessibility issue

Now to see how the return trip goes this evening... 

As a past developer, I got the now famous FizzBuzz question in interviews several times. I thought I play around a bit and do FizzBuzz in T-SQL.

Here is a simple script that prints out the results:  

DECLARE @counter INT
DECLARE @output VARCHAR(8)
SET @counter = 1
WHILE @counter < 101
BEGIN
SET @output = ''
IF @counter % 3 = 0
SET @output = 'Fizz'
IF @counter % 5 = 0
SET @output = @output + 'Buzz'
IF @output = ''
SET @output = @counter
PRINT @output
SET @counter = @counter + 1
END 

I formatted this a bit for easier reading. Source: Pinal Dave

Here is a sample script if you want use native SQL Server features and a dataset result: 

WITH NumberRange (CurrentNumber)
AS
(
SELECT 1 AS CurrentNumber
UNION ALL
SELECT CurrentNumber + 1 FROM NumberRange
WHERE CurrentNumber < 100
)
SELECTCurrentNumber AS Number,
CASE
WHEN CurrentNumber%3 = 0 AND CurrentNumber%5 = 0 THEN 'FizzBuzz'
WHEN CurrentNumber%3 = 0 THEN 'Fizz'
WHEN CurrentNumber%5 = 0 THEN 'Buzz'
ELSE CONVERT(VARCHAR(3), CurrentNumber)
END AS Result
FROM NumberRange 

This uses a Common Table Expression (CTE). Hopefully, this helps the readers.

I've tried a few textual duration functions that were floating around the Internet. I finally hit on something that is exactly what I want. Here's the T-SQL script to create a scalar function to use in your monitoring and admin scripts:

CREATE FUNCTION [dbo].[GetDuration]
(
    @startTime DATETIME, @endTime DATETIME
)
RETURNS VARCHAR(50)
AS
BEGIN

    DECLARE @duration INT, @hours INT, @minutes INT, @seconds INT, @totalSeconds INT,
    @stringdate VARCHAR(12),@durationText VARCHAR(200),@milliseconds INT

    SET @durationText = ''

    SELECT @stringdate = CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, @startTime, @endTime), 0), 114)

    SET @hours = substring(@stringdate,1,2)
    SET @minutes = substring(@stringdate,4,2)
    SET @seconds = substring(@stringdate,7,2)
    SET @milliseconds = substring(@stringdate,10,3)

    IF @hours > 0
    BEGIN
        DECLARE @hourText VARCHAR(12)
       
        IF @hours = 1
        BEGIN 
          SET @hourText = ' hour '
        END
        ELSE
        BEGIN
          SET @hourText = ' hours '
        END
       
        SET @durationText = cast(@hours AS VARCHAR(3)) + @hourText
    END

    IF @minutes > 0
    BEGIN
        DECLARE @minuteText VARCHAR(12)
       
        IF @minutes = 1
        BEGIN 
          SET @minuteText = ' minute '
        END
        ELSE
        BEGIN
          SET @minuteText = ' minutes '
        END
        
        SET @durationText = @durationText + cast(@minutes AS VARCHAR(3)) + @minuteText
    END

    IF @seconds > 0
    BEGIN
        DECLARE @secondText VARCHAR(12)
       
        IF @seconds = 1
        BEGIN 
          SET @secondText = ' second '
        END
        ELSE
        BEGIN
          SET @secondText = ' seconds '
        END
        
        SET @durationText = @durationText + cast(@seconds AS VARCHAR(3)) + @secondText
    END

    IF @milliseconds > 0
    BEGIN
        -- Only show milliseconds if that's the only time unit
        -- that is not zero
        IF len(@durationText) < 3
        BEGIN    
            DECLARE @millsecText VARCHAR(25)
           
            IF @milliseconds = 1
            BEGIN 
              SET @millsecText = ' millisecond'
            END
            ELSE
            BEGIN
              SET @millsecText = ' milliseconds'
            END
            
            SET @durationText = @durationText + cast(@milliseconds AS VARCHAR(3)) + @millsecText
        END
    END   

    RETURN @durationText
   
END

Comment if you use it. :)

I just had a very unpleasant experience with a trial version of a SSMS addin. This addin reset my keyboard layout, and didn't restore it back to what I had.

I went the usual route of trying the different customization dialogs. However, I was miffed when I found this:

I can't change the shortcuts in SSMS! Arrrggghhh! So now I turned to Google to see if there was a non-UI way of doing this...

This first article I found, said to look for User.vsk in C:\ProgramData\Microsoft\Microsoft SQL Server\100\Tools\Shell. There was nothing there.

SSMS keeps the key shortcuts in a file called User.vsk. Incidentally, when I did a global search on my hard drive, I noticed that Visual Studio uses a file with the same name, but in a different directory.

I actually found it in C:\Users\YourUserName\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell. I deleted User.vsk from that file, and restarted SSMS. The mangled short-cuts are still there. . So I look at the contents of the directory again, and notice that there are 3 files with the *.vsk extension.

I delete Current.vsk and User.vsk, and TADA! The default shortcuts are back. Make sure that you have all copies of SSMS closed, before you delete these files. Otherwise, like zombies, they'll be back.

Yes! Now I can stop being irritated by this. Whew!

A while back, I created a little utility to edit ASP.NET membership services database. The instructions only talked about DotNetNuke. I've now added instructions on how to use it on regular ASP.NET membership databases.

There previous blog post is DotNetNuke user manager - Winform App. The new app is called MembershipUserManager. I've included, as usual, both the source and binaries. The source is now a Visual Studio 2010 solution. It actually uses .NET 2.0, so older versions of Visual Studio and SharpDevelop can use the source.

The ReadMe.txt file, in the zip files, has instructions on how to setup regular ASP.NET membership and DotNetNuke.

I've had one project where I had to replace a WCF web service with a single package. This web service was running across something like 130 locations when I originally wrote it. It is up to 150 locations now.

The package needed to reach out to different MS SQL servers across all locations and perform some tasks according to the state of the data in the remote server. I Googled this and looked at StackOverflow, but nobody had exactly what I needed.

I want to put the solution in this blog, because I can never remember how to do this. I end up firing BIDS with the solution that has this package.

Here is the finished package:

The magenta overlay on the connection is from an AddIn called BIDS Helper. It is free and you can get it from http://bidshelper.codeplex.com/

First let's create a user variable called "CurrentServer."

The key for the whole is the connection. In BIDS, open the Properties window, then select the connection. It should look like this:

Click on the button with the ellipsis (the three dots), to bring up the Expressions Editor.

Click in the area below the label "Property", to use the drop down control for properties, and select "ConnectionString."

Click on the ellipsis button to invoke the Expression Builder dialog.

You will enter your connection string in the Expression textbox. Here is a generic connection string to get you started:

"Data Source=" + @[User::CurrentServer] + ";Initial Catalog=YOURSERVER;Provider=SQLNCLI10.1;Uid=YOURUSER;Pwd=YOURPASSWORD"

This is assuming that all your servers are using the same user name and password. Replace YOURSERVER, YOURUSER, and YOURPASSWORD with the appropriate values. Click on the "Evaluate Expression" button to make sure you got this right.

Click OK in the Expression Builder and the Expression Builder Editor to get back to your package. You should now see this property populated in the Properties window. You might need to expand the Expressions property to see it. Once expanded, it will look like this:

Let's now prepare the data flow task to get our server names. First let's create a varible to hold the server name recordset, which we will use to look through the servers:

Next, let's edit the "Get list of server credentials" task in the Control Flow tab. The data flow will look like this:

Edit the "Server Name Query" OLE DB source. I put something simple just to get this going. You can use the UNION technique in the screenshot, or if you are lucky, there is a database somewhere in your company that contains all of this information.

Click OK. Now, let's edit the recordset destination:

Make sure that the VariableName is using the rsServers variable that we created earlier. I've highlighted it in red above. Select the "Input Columns" tab and make sure that the ServerName column is select like so:

Next, let's edit the ForEach Loop task and configure it to use the recordset from the previous step:

Click on "Collection" on the left, then select "Foreach ADO Enumerator" from the drop down. Next we will use the variable we setup earlier:

Next, click on "Variable Mappings." Click on the space below the "Variable" label to activate the drop down. Now select the user variable that will hold the server names. In our case, this is the "CurrentServer" variable.

We are done setting this up. Now anything that we setup inside the ForEach Loop task will work on all the servers we have used from the query that gets our server names.

Now you can see why I forget how to do this. At least it is now in my blog.

I got a bit peeved that SQL Server Management Studio 2012 did not come with a different icon than the one that comes with SSMS 2008. It was just driving me crazy not being able to tell them apart on the Windows 7 Task Bar.

I created one myself. Here's what it looks like:

It's the one on the right. Here's the 128x128 pixel image turned into a PNG:

I've attached the icon to this post.

Enjoy!