How to generate the alphabet from ASCII codes in T-SQL

Just now I had the requirement to create a loop in SSIS, and have that loop the alphabet.
In order to facilitate that, I generated a T-SQL statement, that provides the alphabet including the Danish Æ, Ø and Å. If you don’t need these, you can leave out the latter part of the last where clause (197, 198, 216).

   1:  SELECT CHAR(number) FROM master..spt_values
   2:   WHERE
   3:      Type = 'P'
   4:      AND
   5:      ( number between 65 and 90
   6:          OR
   7:        number IN (197, 198, 216)
   8:      )

Link to an ASCII table with possible values ranging from 0 to 255.

Subtle differences in Teradata and SQL Server

Imagine the hair pulling experience, when you port knowledge from one RDBMS to another, and it just doesn’t work. I had one such experience today, when I had to generate a certain filter for at Query. I wanted to combine the year and month value, to filter on the unique month key which consisted of the concatenation of year and month (YYYYMM).

In SQL Server, you could do something like this:

SELECT
    CAST
(DATEPART(YEAR,GETDATE()) AS VARCHAR(4))+
    IIF(DATEPART(MONTH,GETDATE())< 10
    ,‘0’+CAST(DATEPART(MONTH,GETDATE())AS VARCHAR(2))
,
CAST(DATEPART(MONTH,GETDATE())AS VARCHAR(2))
)

The result of the above query, would be ‘201402’. But, in Teradata, that just results in 2016 (for the current month of February), even when doing the explicit cast mind you.

Turns out, that CONCAT(…) is your friend – Otherwise you just end up with a number, even when explicitly casting to VARCHAR(x)

SELECT
CONCAT(
CAST( EXTRACT( YEARFROMCURRENT_DATE ) AS VARCHAR(4) ),
CASE
WHEN EXTRACT( MONTHFROMCURRENT_DATE ) < 10THEN
CONCAT( '0' , CAST( EXTRACT( MONTHFROMCURRENT_DATE ) AS VARCHAR(2) ) )
ELSE
CAST( EXTRACT( MONTHFROMCURRENT_DATE ) AS VARCHAR(2) )
END
)

 

Never lose your server list in SSMS again…

Having to install new laptops, re-install old ones, configuring virtual servers or whenever you get the opportunity to install SQL Server from scratch, you always end up with an empty list of servers, when you try to connect through SQL Server Management Studio (SSMS).

Empty List

There is a way around this minor, but still sometimes very annoying issue.
The information is stored in one file only, on the profile you are currently logged on to. So copying this file, from an existing setup or a shared repository (like SkyDrive or TFS), to the new environment will bring you up to speed really quick.

The file is named SqlStudio.bin and can be found at <DRIVE>:\Users\<PROFILE>\AppData\Roaming\Microsoft\SQL Server Management Studio\<SQLVersion>

Disclaimer:
I have not tested if any third-party tools register anything in this file, so beware and be sure to have the original file ready, in case of dissaster