Friday, May 29, 2009

Two Books on SQL Server 2008

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. 

Working with Sinhala Letter in SQL Server

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

image

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

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

image

SELECT * FROM Fruits WHERE NAME Like N'%'

image

SELECT * FROM Fruits WHERE NAME Like N'%'

image

Wednesday, May 27, 2009

Restoring a Database with Symmetric Encryption

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.

Thursday, May 14, 2009

Collation, Danish_Norwegian Vs Latin1_General

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) )

GO

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

SELECT * FROM t ORDER BY c COLLATE Danish_Norwegian_CI_AS

--Result 2

SELECT * FROM t ORDER BY c COLLATE Latin1_General_CI_AS

Following are the two result sets.

Result1
(Danish_Norwegian)
Result2
(Latin1_General)
a a
A A
B Aa
Za B
Aa Za

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

SELECT

* FROM t WHERE c LIKE 'A%' COLLATE Danish_Norwegian_CI_AS

--Result 4

SELECT

* FROM t WHERE c LIKE 'A%' COLLATE Latin1_General_CI_AS

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.

Wednesday, May 13, 2009

Error when SSRS Report Running from a .Net Report Viewer

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.

Sunday, May 10, 2009

Report Builder is not Launching

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.