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
3: Type = 'P'
5: ( number between 65 and 90
7: number IN (197, 198, 216)
Link to an ASCII table with possible values ranging from 0 to 255.
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:
CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR(4))+
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)
CAST( EXTRACT( YEARFROMCURRENT_DATE ) AS VARCHAR(4) ),
WHEN EXTRACT( MONTHFROMCURRENT_DATE ) < 10THEN
CONCAT( '0' , CAST( EXTRACT( MONTHFROMCURRENT_DATE ) AS VARCHAR(2) ) )
CAST( EXTRACT( MONTHFROMCURRENT_DATE ) AS VARCHAR(2) )
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).
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>
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
In case you missed it, here is the Microsoft SQL Server 2014 CTP2 Data Tools Business Intelligence project templates for Visual Studio 2012.
This fixes one major pain on my laptop: No longer need for SSDT on one machine and SQL2014 on another. Good times!
Finally the SQL Server 2014 Community Technology Preview 2 (CTP2) was released early Thursday morning.
Get it here: http://technet.microsoft.com/en-us/evalcenter/dn205290.aspx
Get the product guide here: http://www.microsoft.com/en-us/download/confirmation.aspx?id=39269