.:: Dinesh Asanka ::.

Being a Sri Lankan and SQL Server professional, I would love to see Sinhala font in SQL Server.

In SQL Server 2005 there is a matching collation for Sinhala called Indic_General_90_BIN. In SQL Server 2008,  there is a collation called Indic_General_100_BIN
Refer http://msdn.microsoft.com/en-us/library/ms188046.aspx

DROP TABLE fruits

CREATE TABLE Fruits

    (

      NAME NVARCHAR(50) COLLATE Indic_General_90_BIN

    )

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'අඹ')     

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'කොස්') 

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'නාරං')           

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'දං')     

INSERT  INTO [Fruits] ( [NAME] )VALUES  (N'ඇපල්')     

INSERT  INTO [Fruits] ( [NAME] )VALUES  (N'කෙසෙල්')

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'අන්නාසි')     

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'ලාවලු') 

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'කැකිරි') 

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'රඹුටන්')     

INSERT  INTO [Fruits] ( [NAME] )VALUES  ( N'දෙලුම්') 

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'නෙල්ලි')     

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'අලි පේර')

You can order them by the Sinhala alphabet. 

SELECT  * FROM    [Fruits] ORDER BY NAME

It also works with normal searching operations such as = and like.

SELECT * FROM Fruits WHERE NAME = N'රඹුටන්'

SELECT * FROM Fruits WHERE NAME Like N'ක%'

SELECT * FROM Fruits WHERE NAME Like N'අ%'

I had the privilege of becoming a contributing editor for two SQL Server 2008 books published by Syngress. Those books are The Real MCTS SQL Server 2008 Exam 70-433 Prep Kit: Database Design  and The Real MCTS SQL Server 2008 Exam 70-432 Prep Kit: Database Implementation and Maintenance.

I was involved around seven books to date and this is the first time one of my books are sold in Amazon. 

Encryption was introduced into SQL Server with SQL Server 2005. This series of articles will tell you how to implement Encryption to protect your valuable data.

This blog is to how to restore a database to another server.

1. Backup the database on the [SOURCE] server.

2. Backup the service master key on the [SOURCE] server.

BACKUP SERVICE MASTER KEY TO FILE = 'C:\ENCRYPT_KEYS\MYSERVICE.KEY'
     ENCRYPTION BY PASSWORD = 'pa$$w0rd'

3. Restore the service master key on the [DESTINATION] server.

RESTORE SERVICE MASTER KEY FROM FILE ='C:\ENCRYPT_KEYS\MYSERVICE.KEY'

DECRYPTION BY PASSWORD = 'pa$$w0rd'    [FORCE]

4. Restore the database on the [DESTINATION] server.

Collation is a clause that can be applied to a database definition or a column definition to define the collation, or to a character string expression to apply a collation cast.  We all think that Collation is effected for Sorting and Case Sensitive data.

 CREATE TABLE t ( c char(2) )

INSERT INTO t VALUES ( 'a' ) 

INSERT INTO t VALUES ( 'Aa' )

INSERT INTO t VALUES ( 'Za' )

INSERT INTO t VALUES ( 'A' )

INSERT INTO t VALUES ( 'B' )

--Result 1

Following are the two result sets.

You can see that in the in the both result sets only difference is placement of Aa.

If you run following two statements, things will be quite annoying.

Result 3 will return 2 rows, a and A and Result 4 will return a, A and Aa. Which means that Aa is missing in Danish_Norwegian collation. Above results were obtained from SQL Server 2005 SP3 and these results are same with SQL Server 2008 SP1.

When report viewer report added to the .Net application and report path is set to a deployed report to the report server following error message will occur.

The permissions granted to user '<DOMAIN>\IUSR_<DOMAIN>' are insufficient for performing this operation. (rsAccessDenied)

1. Open Internet Explorer.
2. Type the following in the Address bar:
   http:// application-tier/Reports/Pages/Folder.aspx
   You can find the name of the report server by opening Team Explorer, expanding the Reports node, and viewing the properties of a report.
3. Click the Properties tab and then click New Role Assignment.
4. In Group or User Name, and add the Windows logon name for the person you want to add to this group.
5. In Role, select Content Manager, and then click OK.

In SQL Server Reporting Services (SSRS) 2005 clicking the Report Builder option in Report Manager does not launch Report Builder Tool

You need dotnet framework 2.0 in your client machine to launch Report Builder. Verify whether you have installed dotnet framework 2.0. If it is already installed check the application log from the event viewer and verify whether there are any errors relevant to the Report Builder.

This small video describes how to use Output command in SQL Server 2005.

You can download SQL 2008 SP1 here.  If you get a 404 error, come back and try it again.  Seems that the download servers aren’t all synced up.

The April 2009 update to the SQL Server 2008 Feature Packs is also available today. In this release few bugs were fixed.

This video will show you how to use Merge Command in SQL Server 2008.

SQL Server Profiler is a tool which you can use to gather information about what are the events happening at the SQL Server.  Following is the sample which you will get from the Profiler Trace file.

If you pay attention to the Application Name Column (3rd Column form your left hand side), You can see there are entries. For the First four rows, you can see a SQL Prompt and next four lines you will see Microsoft SQL Server Management Studio. This is due to the fact that those applications are using this particular instance of SQL server. If you look closer, you will see that next 3 lines has a entry name .Net SqlClient Data Provider.

Obvious question is what is this application. Well, this is because Application Name is not specified by the application. You have to set it from the client application by adding another parameter to the connection string.

string connectionstring = "Data Source=.; Integrated Security=SSPI; Initial Catalog=DB;Application Name=MyApplication";

NB: Whenever the Application Name is not specified it will use default application Name, .Net SqlClient Data Provider.

After if you analyze the Profiler trace again you will see the correct application name.

This very important option as this will allow you to analyze your Profiler Trace for application wise. Also, you can use filter option for Application Name in Profiler to filter by application.