TFS not compatible with SQL collation during setup

Installing TFS in existing database and come across this issue.

The following SQL Server instance is not compatible with Team Foundation Server: TFS\tfs_db. The default collation is set to Latin1_General_CI_AI. Team Foundation Server requires that the default collation be case insensitive and accent sensitive. You must choose a SQL Server instance whose default collation settings match these requirement

Solution is to run the following code in visual studio command prompt, the code can change the sql collation to support collation. Latin1_General_CI_AS

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=TFS /SQLSYSADMINACCOUNTS=sa /SAPWD=pass@word1 /SQLCOLLATION=Latin1_General_CI_AS

Migrating SQL Database to SQL Azure

Microsoft SQL provides serveral options for users to migrate their on-premises instance of SQL database to SQL Azure. In this post, I will use the simplest method (generate script) to prepare script for SQL Azure. Before we begin, it is good to know the limitation of SQL Azure compare to on-premises instance type of SQL, we can found the full list at MSDN here.

One of the key concern is you have to use SQL server 2008 R2 version, SQl 2008 does not support the target database to be SQL Azure.

Navigate to the database you wish to migrate. Mouse right click, select task , then generate scripts.

A wizard screen called "generate and publish script" will pop up. Select your object and define the script location. Key thing here is click the advance button.

Inside the advance option;
Set "script for the database engine type" to SQL Azure Database
Set "Convert UDDTs to Base types" to True.
Set "Types of data to script" to schema and data 
Finsih...
Now, execute the scripts in SQL Azure management portal, you should able to see your data in the cloud :)

Invalid object name 'sys.configurations' while connecting to SQL Azure

If you connect the SQL Azure from SQL Management Studio (Database Engine) by using the Full qualified server name given by Azure, you will hit some invalid object name type of error alert.

Workaround/ solution:

1. Close the connection dialog
2. Click on the "New query"
3. It will prompt similar connection dialog, this will be working fine.

*Remember to set your database name by clicking the option button in connection dialog
screen. :)

SQL divide by zero "isZero" function

Recently come across a requirement on SQL to divide 2 columns and populate in a custom fields. Infact is just something simple like "SELECT column1/column2 AS result"


Issue raised because the "column2" might contains zero or null value, what we need to do is if column 2 is zero replace it with another fix non zero varible.

• Null issue can be easily solve by using isNull finction, but we don't have 'isZero' function

The solution is to use the nullif function with isnull function;
set column2 to null value if it is zero, outer level if is null replace with variable X.

ISNULL(NULLIF(column2, 0),variableX)