A Gift of Reading

The day before father’s day my wife presents me with a Kindle 2. Never before have I seen one, let alone touched a Kindle device for that matter. My only experience was from reading about the Kindle on Amazon.com. She knew that I wanted to return to my old reading affair but due to my career I end up selecting technical centric titles. With this new device I am able to take the ebooks that came (as an added bouns) with my print version books and upload them to my Kindle 2. In some cases the font size is pretty small but can be adjusted if the option is available.

Before the purchase was made she knew that I did some research and that I did weight out some of the differences between the Barnes and Nobel (Nook), Apples (iPad), Sony’s (ebook Reader), Amazon’s (Kindle 2) and the (Kindle DX). Of course each of the devices has their strengths and weaknesses so it boils down to native features and overall price. Well at least for me it does.

In the beginning I was set on a device that offered at least a 9″+ touch-screen (color and readable in direct sunlight), storage expansion via SD slot, USB support, long-lasting battery life and broadband wireless with WiFi support. However, after some consideration I decided that a compact factor would be more suitable for my actual needs. With this decision this immediately removed the iPad (though very cool) from the selection. Not to mention the price tag of the iPad did not sit well with me. Besides the iPad’s next generation(s) will most likely include a ton of added features; hopefully, at a more affordable price. Which will make it even more attractive but until then I must pass for now. This also removed the Kindle DX from the selection.

With those factored out I sought for wireless options which immediately terminated Sony’s Reader from the standing competition. From here it boiled down to services. Even though the Nook and Kindle stood toe-to-toe in terms of internal storage the Nook had the available of expansion and the added Wireless support. Which were a feature I felt I needed. After careful consideration I felt that I really did not need WiFi support (though extremely useful) or had the desire to sign-up for Wireless services especially from AT&T. I have never been a fan of AT&T; further more, based on the stories from some my friends and colleagues that are or were AT&T subscribers, the coverage was apparently an issue.

This left one candidate standing and that was the Kindle 2. Though it does not offer storage expansion nor WiFi support I am granted FREE 3G wireless service throughout the United States. Unfortunately my version is not equipped with Global coverage but all new versions of the Kindle 2 and Kindle DX come with standard Global service for FREE. This means I can pretty much purchase and download books from anywhere right from the convenience of my Kindle 2. This alleviates the need of a computer to transfer files, though it is a standard and readily available option.

I am very pleased with the Kindle 2 with its convenient compact size and the crisp-clear (E-Ink) display that is remarkably visible in direct sunlight. In darker settings a light will be required since the Kindle(s) are not equipped with back illumination (reduces stress on the eyes this way). The battery barely dropped a notch or two during my week away on business. This includes uses on the airplane, in the hotel for an hour or two per day and on some occasions during lunch (30 mins – 1 hr). Of course I kept the WiFi disabled since I did not need it.

Features B & N
Nook
Apple
iPad
Sony
Reader Touch
Amazon
Kindle 2
Amazon
Kindle DX
Internal Storage 2GB 16/32/64GB 512MB 2GB 4GB
Storage Expansion Micro-SD No SD
Memory Stick Pro
No No
Wireless 3G1 + WiFi 3G1 + WiFi No 3G (Free)
Global
3G (Free)
Global
Screen Size 6″ Grey Scale 9.7″ Color 6″ Grey 6″ Grey 9.7″ Grey
USB Support Micro-USB No Micro-USB Micro-USB Micro-USB
Battery Life 10 days2 10hrs3/9hrs4 2 wks 2 wks2 2 wks2
Touch Screen 3.5″ Menu only Yes Yes No No
Text to Speech No Yes5 No Yes6 Yes6
Price $149-$199 $499-$829 $199 $189 $489

Table – 1: eBook Reader Comparison


1 3G service by AT&T
2 With WiFi disabled
3 With WiFi usage
4 With 3G usage
5 Available in some applications
6 Available in most books

SQLSaturday #47 – Postponed!

As you have probably heard SQLSaturday #47 – Phoenix has been postponed. Though this decision was difficult to make, we felt it was in the best interest of the SQL community and the event. The summer season played a significant factor and from it we lost presenters and event goers. We began to receive emails, DM’s and such from registered participants that decided to withdraw due to the excessive heat and other planned summer activities, which is completely understandable. So without surprise we made a decision after meeting with the folks from PASS.

Our goal is to provide the best experience possible for everyone involved. So when the community spoke we listened and we will continue to listen. We take extreme pride in this event and we do not look at this postponement as a failure, but as a learning experience! Like John Powell said, “The only real mistake is the one from which we learn nothing”. Rest assured that we will not make the same mistake twice.

Please keep in mind that is merely a postponement… not a cancellation! We are still actively organizing SQLSaturday and will post more information about it as we cover more ground. As always we are available if you have any questions, comments or suggestions. Feel free to drop us a line and we will gladly try to answer all inquires to the best of our abilities.

We still need your involvement and we will still need presenters, event goers, sponsors and volunteers. So take this time and think about the sessions you would like to see and the possible tracks you believe we should have. We are all ears and would love to hear from you.

Being the organizer responsible for sponsorship relations I want to take this opportunity to tell you that we appreciate you supporting SQLSaturday Phoenix. I also want you to know that if you wish for a refund I will do everything possible to expedite the process. I will make my rounds to touch-base with all sponsors, hopefully before you have to call me.

Thank you for your understanding and we look forward to seeing you at SQLSaturday #47 – Phoenix!

–samson
samson[at]wetmatter.com | @sqlsamson

Create Virtual Directory in IIS 6.0 via Command line

Well for most of my implementations I deploy variations of Windows 2003 Server with x86 and x64 editions included. I always use IIS because I deploy web based applications so I like to keep things standardized as much as humanly as possible. Its just better that way. No arguments necessary.

Well in order to keep settings consistent across the board I need to ensure all of my IIS settings are intact. My implementations utilize virtual directories, but I also like to have a separate application pool associated to this virtual directory for obvious reasons. In the event you are not familiar: separating applications into their own application pool or group is called (isolation mode). This allows a site or a group of sites to be taken offline without affecting all sites that might reside on that server. If you have a application server that hosts many sites hopefully you have them configured using their own application pools.

In terms of default documents I like to specify only what I need which is typically (default.aspx) while removing everything else. Then I set the framework version to .NET 2.0. Of course I prefer to do this auto-magically to remove chances of human error so no manual labor for me I tell ya!

Below is what the contents of my batch file looks like. Feel free to use it if you find it helpful. Of course you want to test it on a test or dev environment first. It has worked for me several times but environments differ and you never know. Besides this particular script works if you have the inetpub on the C:\. (Notice Line 7)

Also note that this script is dependent on two VBS scripts (adsutil.vbs & IISvdir.vbs) both of which you will find on the Windows Server platforms and not on the desktops.

@echo off
color 17
Title My Standard IIS 6.0 Configuration . . .

:: ****************************************
SET var=MyApplicationName
SET dir=C:\Inetpub\AdminScripts
SET ito=120
SET dd=Default.aspx
:: ****************************************

:: ========================================
::   STEP 1: CREATE APPLICATION POOL
:: ========================================
echo Creating Application Pool . . .
echo ------------------------------------------------------------
CSCRIPT //nologo %dir%\adsutil.vbs CREATE w3svc/AppPools/%var% IISApplicationPool
echo.

:: ========================================
::   STEP 2: SET IDLE TIMEOUT
:: ========================================
echo Setting Idle Timeout . . .
echo ------------------------------------------------------------
CSCRIPT //nologo %dir%\adsutil.vbs SET w3svc/AppPools/%var%/IdleTimeout %ito%
echo.

:: ========================================
::   STEP 3: CREATE VIRTUAL DIRECTORY
:: ========================================
echo Creating Virtual Directory . . .
echo ------------------------------------------------------------
CSCRIPT //nologo %systemroot%\system32\IISvdir.vbs /create "Default Web Site" "%var%" %systemdrive%\inetpub\wwwroot\%var%
echo.

:: ========================================
::   STEP 4: SET .NET VERSION to 2.0
:: ========================================
echo Setting .NET Version . . .
echo ------------------------------------------------------------
%windir%\Microsoft.Net\Framework\v2.0.50727\aspnet_regiis.exe -s w3svc/1/root/%var%
echo.

:: ========================================
::   STEP 5: SET DEFAULT DOCUMENT
:: ========================================
echo Setting Default Documents . . .
echo ------------------------------------------------------------
CSCRIPT //nologo %dir%\adsutil.vbs SET w3svc/1/Root/%var%/DefaultDoc %dd%
echo.

:: ========================================
::   STEP 6: ASSIGN APPLICATION TO VIRDIR
:: ========================================
echo Assigning Application Pool to Virtual Directory . . .
echo ------------------------------------------------------------
CSCRIPT //nologo %dir%\adsutil.vbs SET w3svc/1/Root/%var%/AppPoolid %var%
echo.
PAUSE

Before we start running scripts let’s take a look at how IIS currently looks like. This way we can have a before and after comparison. So below is a screen cast of my demo system’s IIS. As you can see it is very clean because it’s a fresh install. I haven’t really done anything to it yet. Once we run the script we will see a newly created application pool and a newly created virtual directory.
01.IISMgr

Below is what the script looks like once it is executed on a server system that has IIS installed.
02.RunBatchFile

Now let’s verify the differences between the before and after. As you can see the Application Pool & Virtual Directory have been created…
03.AppPool_VirDirCreated

Looking into the details

The default Idle Timeout is now 120 mins. In a typical install this is defaulted to 20 mins.
04.VerifyIdleTimeout

Virtual Directory details… this is pretty much the same but in this case it was configured via script.
05.VerifyVirtualDirectory

Again… in a typical setup you would usually see (Default.htm, Default.asp, index.htm and iisstart.htm) listed as default documents. In this case we only specify what we really need and that is the (Default.aspx).
06.VerifyDefaultDocs

And lastly the Microsoft.NET Framework is set to v2.0 rather than v1.1…
07.VerifyASPNET_Ver

As you can see this is relatively straightforward and helps me keep implementations consistent.

Using sp_MSforeachtable

I actually stumbled upon this very stored procedure on the blog post by Suprotim Agarwal of sqlservercurry.com titled: How to skip a Table or Database while using sp_MSforeachdb or sp_MSforeachtable while just doing a little research about SQL Server. I honestly didn’t know this existed. I barely learned about the stored procedure “sp_MSforeachdb” only after attending Brent Ozar’s BLITZ! 60 Minute SQL Server Takeovers presentation during the 24hrs of PASS event. I walked away with valuable information as Brent delivered the content very well. So when I learned of “sp_MSforeachtable” I had to immediately test it.

I have used “sp_spaceused” in the past and of course I constructed a CURSOR that looped through the tables using either INFORMATION_SCHEMA.TABLES or sys.tables. This time around I wanted to achieve the same results without a CURSOR and thankfully “sp_MSforeachtable” with a little creativity allowed me to do so. So here is what I came up with.

CREATE TABLE #tmpTblSpace
(
ID INT IDENTITY(1,1)
,name NVARCHAR(75)
,rows INT
,reserved NVARCHAR(50)
,data NVARCHAR(50)
,index_size NVARCHAR(50)
,unsed NVARCHAR(50)
)
INSERT INTO #tmpTblSpace
EXEC sp_MSforeachtable 'EXEC sp_spaceused ''?'''
SELECT * FROM #tmpTblSpace
DROP TABLE #tmpTblSpace

Results

results

If I had just ran (Line: 12) alone the results are not quite as appeasing which is precisely why I created the temp table, better formatting.

EXEC sp_MSforeachtable 'EXEC sp_spaceused ''?'''

Results

02.results

Amended

As I was practicing database mirroring with SQL Server 2008 I loaded up the sample databases (AdventureWorks) and needed to change the recovery model from Simple to Full. Then it dawned on me… that you can use the sp_MSForEachDB to change the recovery model for all of the adventureworks databases. Here is how it’s done.

EXEC sp_MSforeachdb
'IF (''?'' NOT IN (''master'', ''tempdb'', ''model'', ''msdb''))
EXECUTE (''ALTER DATABASE [?] SET RECOVERY FULL'')'

UNION and Reporting Services 2008

I have used the UNION operator a few times in the past and during a recent Business Intelligence project I quite possibly used it more then I had in the past combined. Typically you can merge queries together simply by placing a UNION operator between two queries. Well it’s not that simple there are some restrictions.

  • the number of fields must be the same in both queries
  • the order of the fields must be the same in both queries
  • data types must be compatible

What you don’t believe me? Well then visit the TechNet site: UNION Transact-SQL and see it for yourself.

syntax

The syntax is really nothing…

SELECT Column1, Column2 FROM table1
UNION
SELECT Column1, Column2 FROM table1

That’s the gist of the it. Kind of boring right? Yeah a little bit. When we incorporate it within a stored procedure that is used for Reporting Services it becomes a tad more interesting. I can’t speak for you but at least it is for me. Now let’s get into some scenarios where it comes handy.

Let’s say I want to offer a report that uses a dropdown list for selecting and passing the parameters. Well typically in a select list you can only choose one, but what if you wanted an option to select all? This is where the UNION comes in handy.

procedure

Typically you would construct your procedure similarly to the one below.

USE [AdventureWorksDW2008]
GO
IF EXISTS (SELECT name FROM sys.objects WHERE (name = 'sp_RP_GetProspectBuyerData'))
DROP PROCEDURE sp_RP_GetProspectBuyerData
GO

CREATE PROCEDURE sp_RP_GetProspectBuyerData
(
	@id INT
)
AS
SELECT	ProspectiveBuyerKey
		,FirstName
		,MiddleName
		,LastName
		,CONVERT(varchar,BirthDate,101) 'DOB'
		,EmailAddress
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]
WHERE (ProspectiveBuyerKey = @id)

The basics of the procedure is a simple SELECT statement. So let’s take a look at the results when I just run (lines: 12-18) only.

SELECT	ProspectiveBuyerKey
		,FirstName
		,MiddleName
		,LastName
		,CONVERT(varchar,BirthDate,101) 'DOB'
		,EmailAddress
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]

02.BasicQueryInDataSproc

The only problem with this procedure is with (line: 19). The choice is either one value or another. There is not option for a “show all value” to be passed.

WHERE (ProspectiveBuyerKey = @id)

This is where the UNION operators comes in. Not necessarily in the stored procedure per se but in the dataset you create to populate the select list. However you still need to modify the stored procedure to prepare for whats to come. This is a slight modification to the WHERE clause of the stored procedure.

WHERE (ProspectiveBuyerKey = @id OR @id = -1)

Here is the final base for the “sp_RP_GetProspectBuyerData” stored procedure with the added OR operator.

USE [AdventureWorksDW2008]
GO
IF EXISTS (SELECT name FROM sys.objects WHERE (name = 'sp_RP_GetProspectBuyerData'))
DROP PROCEDURE sp_RP_GetProspectBuyerData
GO

CREATE PROCEDURE sp_RP_GetProspectBuyerData
(
	@id INT
)
AS
SELECT	ProspectiveBuyerKey
		,FirstName
		,MiddleName
		,LastName
		,CONVERT(varchar,BirthDate,101) 'DOB'
		,EmailAddress
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]
WHERE (ProspectiveBuyerKey = @id OR @id = -1)

dataset

Now we need to construct the dataset stored procedure that we will use to populate the select list which will be used in our report. This is the basics of the stored procedure that we will be using to drive the select list on the report.

USE [AdventureWorksDW2008]
GO
IF EXISTS (SELECT name FROM sys.objects WHERE (name = 'sp_RP_ProspectBuyerDDL'))
DROP PROCEDURE sp_RP_ProspectBuyerDDL
GO

CREATE PROCEDURE sp_RP_ProspectBuyerDDL
AS
SELECT	[ProspectiveBuyerKey] 'ID'
		,[FirstName] + SPACE(1) + 
		[LastName] 'Full Name'
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]

Well the above procedure is cool and all but it does not meet our needs as we need to have an “ALL” option listed as a selectable item in the select list. In order to achieve this let’s add another SELECT statement that will correspond to the changes we made to the first procedure “sp_RP_GetProspectBuyerData” where we added the following: ( OR @id = -1 ) and use UNION operator to merge them.

To list the “ALL” in our select list we need to add it using the same data type, order and we must have the same amount of fields. So here is what we need.

SELECT	-1 AS 'ID'
		,'<ALL>' AS 'Full Name'

So now we need to merge the two SELECT statements and this is how its done.

Step 1: First we take…
SELECT	-1 AS 'ID'
		,'<ALL>' AS 'Full Name'
Step 2: Then we add
UNION
Step 3: And finally we append…
SELECT	[ProspectiveBuyerKey] 'ID'
		,[FirstName] + SPACE(1) + 
		[LastName] 'Full Name'
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]
Step 4: We wrap them into a stored procedure
USE [AdventureWorksDW2008]
GO
IF EXISTS (SELECT name FROM sys.objects WHERE (name = 'sp_RP_ProspectBuyerDDL'))
DROP PROCEDURE sp_RP_ProspectBuyerDDL
GO

CREATE PROCEDURE sp_RP_ProspectBuyerDDL
AS
SELECT	-1 AS 'ID'
		,'<ALL>' AS 'Full Name'
UNION
SELECT	[ProspectiveBuyerKey] 'ID'
		,[FirstName] + SPACE(1) + 
		[LastName] 'Full Name'
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]

Now if we run (lines: 9-15) only here is what the results look like.

04.DatasetSelect

As you can see the “ALL” is on top which is what we need and for the sake of testing. Let’s run through two quick scenario of passing a value of 1 and a value of -1 to the @id parameter just to see what the end results look like.

Here I set the @id variable to 1…

USE [AdventureWorksDW2008]

DECLARE @id INT = 1

SELECT	ProspectiveBuyerKey
		,FirstName
		,MiddleName
		,LastName
		,CONVERT(varchar,BirthDate,101) 'DOB'
		,EmailAddress
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]
WHERE (ProspectiveBuyerKey = @id OR @id = -1)

Results
05.PassedValue1

Now let’s see what happens when we pass a value of -1…

USE [AdventureWorksDW2008]

DECLARE @id INT = -1

SELECT	ProspectiveBuyerKey
		,FirstName
		,MiddleName
		,LastName
		,CONVERT(varchar,BirthDate,101) 'DOB'
		,EmailAddress
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]
WHERE (ProspectiveBuyerKey = @id OR @id = -1)

Results
06.PassedValue-1

Boom! All records are returned. Exactly what I needed. Now let’s run through the report! I already set the available values for the parameter properties so we can just jump right into the report and see it in action.

Business Intelligence Development Studio

01.Rpt

Step 1: Select All

02.Rpt

Step 2: Run Report

03.AllRecs

Results when value other than All is selected

04.SingleRec

Checking Permissions with Batch Process

There is no denying it… I am a die-hard command-line junkie. When deploying to new systems I have my batch file arsenal with me at all times strictly because it keeps my processes very procedural and standardized. There are times when I come into an environment where the systems have already been deployed. Which makes things a bit challenging especially when dealing with multiple profiles on a single machine. Because of the fact that most of my deployments require SQL Server Express I have batch files setup accordingly that specifies the needed firewall exceptions along with other added automated steps. Just to name a few.

When dealing with existing systems I try to make dual processes 1) when I have admin access and 2) when I don’t have admin access. This all depends on the logged-on user of course. So in order to preserve the natural order of life I do my best to not interfere with user’s settings because it tends to throw them off and generally upsets them. So I keep their environment pretty much intact. In order to maintain consistency I run my dual processes. Not ideal but it works. Of course there are instances where I absolutely require admin access, but for the little items that are merely file placement and such I get by with the separate process.

It sounds like a lot of work, but once you have your batch file laid out the rest is cake. The first thing I do is check to see if the user has elevated privileged. I find trying to query the registry works well in indicating your permission level.

reg query "HKU\S-1-5-19" >NUL
echo %errorlevel%

If I ran this under a user account that is in the Users or Power Users group then the errorlevel will return a value of one.

01.StandardUser

If ran using an account with admin privledges you will see a value of zero returned.

02.AdminUser

The next step is to define the process to execute once the permission level has been determined. This is easily directed with the goto statement. Let’s take a look.

@echo off
color 17
reg query "HKU\S-1-5-19" >NUL
CLS
If Not %errorlevel% == 0 goto UserDeploy
If %errorlevel% == 0 goto AdminDeploy

:UserDeploy
::Run this process
goto end

:AdminDeploy
::Run this process
goto end

:end

I added the CLS on line two to clear the “Error: Access is denied.” message that is displayed on the first screenshot.

CLS

In terms of the dual processes I setup self-extracting zips to deploy to certain directories based on privileges.

Concatenation Fields

In some instances you are required to piece together data by slapping fields together. This is probably most common with names, addresses and such. Using the AdventureWorksLT database I will illustrate how to concatenate a few fields to comprise a single full name field. First we need to analyze the data to see what we have.

SELECT FirstName
       ,MiddleName
       ,LastName
       ,Suffix
FROM [AdventureWorksLT].[SalesLT].[Customer]

Results
01.SampleData

As you can see we have rows that contain only a first and last name, some that have a middle initial and some have a suffix. Unfortunately there was not a record that had a first, last and suffix so I modified record ID 12 because I wanted to touch base on all scenarios. I find it helpful.

Based on the data we don’t need to worry about the first and last name fields but we do need to be concerned with the middle initial and suffix since some are populated while others are not. So how are we going to handle this? Well there are a few ways but I will show you how to address this with SELECT CASE.

SELECT Firstname + space(1) +
       CASE
          WHEN MiddleName IS NULL THEN LastName
          ELSE MiddleName + space(1) + LastName
       END +
       CASE
          WHEN Suffix IS NOT NULL THEN space(1) + Suffix
          ELSE space(0)
       END 'FullName'
FROM [AdventureWorksLT].[SalesLT].[Customer]

Well let’s review before we move on to the results… line numbers 3 & 4 basically state that when you find a NULL value for the MiddleName just display the LastName instead otherwise display the MiddleName add a space then display the LastName.

Lines 7 & 8 is similar. When the Suffix field contains a value other than NULL add a space and display the Suffix otherwise show nothing.

Results
02.FieldsConcat

Now let’s put everything together to see how it all looks.

SELECT FirstName
       ,[MiddleName]
       ,[LastName]
       ,[Suffix]
       ,FirstName + space(1) +
       CASE
          WHEN MiddleName IS NULL THEN LastName
          ELSE MiddleName + space(1) + LastName
       END +
       CASE
          WHEN Suffix IS NOT NULL THEN space(1) + Suffix
          ELSE space(0)
       END 'FullName'
FROM [AdventureWorksLT].[SalesLT].[Customer]

Note:

The fact this dataset has explicit NULL values as opposed to blank/empty values for the MiddleName and Suffix columns made this easy. If it were the latter of the two the results would differ and require additional measures.

Results
03.ConcatFields

I modified CustomerID 20 by replacing NULL in the MiddleName field with a single space and CustomerID 22 by replacing NULL in the Suffix with a single space. Of course the MiddleName is more apparent as it spaces out the FirstName and LastName more than it should and the Suffix is less obvious but it does add a space after the LastName.

Results
04.NonNULLValues

I was able to address these by adding a two additional WHEN statements to the existing CASE statements.

SELECT FirstName
       ,[MiddleName]
       ,[LastName]
       ,[Suffix]
       ,FirstName + space(1) +
       CASE
          WHEN MiddleName IS NULL THEN LastName
          WHEN MiddleName = space(1) THEN LastName
          ELSE MiddleName + space(1) + LastName
       END +
       CASE
          WHEN Suffix = space(1) THEN space(0)
          WHEN Suffix IS NOT NULL THEN space(1) + Suffix
          ELSE ''
       END 'FullName'
FROM [AdventureWorksLT].[SalesLT].[Customer]

Results
05.NonNULLFixed

Another issue:

What if there were leading or trailing spaces in the FirstName and LastName fields? I modified Record ID 1 and added a 10 space before and after Orlando.

Results
06.Whitespace-FirstnameColumn

Well in this case I would keep the same syntax but would encase the fields within: RTRIM(LTRIM(FirstName)) respectively as it removes the whitespaces before and after.

SELECT FirstName
       ,[MiddleName]
       ,[LastName]
       ,[Suffix]
       ,LTRIM(RTRIM(FirstName)) + space(1) +
       CASE
          WHEN MiddleName IS NULL THEN LastName
          WHEN MiddleName = space(1) THEN LastName
          ELSE MiddleName + space(1) + LastName
       END +
       CASE
          WHEN Suffix = space(1) THEN space(0)
          WHEN Suffix IS NOT NULL THEN space(1) + Suffix
          ELSE ''
       END 'FullName'
FROM [AdventureWorksLT].[SalesLT].[Customer]

Results
07.Trimmed-Firstname