Monday, May 31, 2010

Report Viewer Control: Exporting Reports to Word, PDF and Excel Programmatically

When Reporting Services reports are shown with ASP.NET Report Viewer control, one of the common requirements for exporting facility is, limiting it to few output formats. By default Export drop-down contains 7 output formats. If we need to limit for 1-2 output formats, one way is, hide the ExportControl and implement it with our own code. Here is the way of implementing it;

Here is a sample screen for a ASP.NET page with Reporting Services report. Note that ExportControl is hidden in the toolbar and drop-down is added to show output formats for exporting.


Here is the code of Page_Load.

protected void Page_Load(object sender, EventArgs e)
    ReportViewer1.ShowExportControls = false;
    ReportViewer1.ProcessingMode = ProcessingMode.Remote;
    // this can be set with control itself.
    //ReportViewer1.ServerReport.ReportServerUrl = new Uri(@"http://localhost/reportserver");
    //ReportViewer1.ServerReport.ReportPath = @"/Report Project1/Report2";
    if (!IsPostBack)
        DropDownList1.Items.Add(new ListItem("Word", "Word"));
        DropDownList1.Items.Add(new ListItem("Excel", "Excel"));
        DropDownList1.Items.Add(new ListItem("Acrobat (PDF) file", "PDF"));

Here is the code for Button-Click.

protected void Button1_Click(object sender, EventArgs e)
    string mimeType;
    string encoding;
    string fileNameExtension;
    string[] streams;
    Warning[] warnings;
    byte[] bytes = ReportViewer1.ServerReport.Render(DropDownList1.SelectedValue, null, out mimeType, out encoding, out fileNameExtension, out streams, out warnings);
    HttpContext.Current.Response.Buffer = true;
    HttpContext.Current.Response.ContentType = mimeType;
    HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=SalesReport." + fileNameExtension);

If you need to find out other output formats and control related info, visit

This explains ServerReport.Render method:

Sunday, May 30, 2010

AdventureWorks 2008 R2 RTM is available

AdventureWorks sample SQL Server databases are available for SQL Server 2008 R2 RTM. When you install, you get R2 databases as AdventureWorks2008R2 and AdventureWorksDW2008R2, hence new databases can be resided side by side with AdventureWorks2008 databases. Sample databases are located in

SQL Azure vs. SQL Server

azureThe cloud-based relational database service, SQL Azure provides relational database functionality as a service. Although this platform provides many benefits, it has limitation too. If you are looking for a Comparison between SQL Azure and SQL Server, here is a document.

Friday, May 28, 2010

Understanding GRANT, REVOKE and DENY T-SQL Commands

GRANT, REVOKE, and DENY commands are T-SQL commands in SQL Server for managing permission. Although we know the correct usage of them, REVOKE and DENY have confused most of us, what exactly SQL Server does for these two commands. Here is a brief explanation on them;

  • GRANT – Let users to perform an operation on objects.
  • REVOKE – Removes assigned GRANT permissions on an object for one or more operations. Main thing you have remember is, this does not restrict user accessing the object completely. If user is in a role that has permission on the object for the operation, user will be able to perform the operation.
  • DENY – Denies permission to the object for an operation. Once it set, since it takes precedence over all other GRANT permissions, user will not be able to perform the operation against the object.

Here is a code that shows it clearly.

-- create a login and user
USE [AdventureWorks]
-- grant permission on product table
GRANT SELECT ON OBJECT::Production.Product TO [Joe]
-- create a role and set SELECT permission to it
-- and add Joe to the role
GRANT SELECT ON [Production].[Product] TO [NewRole]
EXEC sp_addrolemember N'NewRole', N'Joe'
-- Run SELECT with Joe's credentials and see
-- He sees records
SELECT * FROM Production.Product
-- Remove permisson assigned to him
REVOKE SELECT ON OBJECT::Production.Product FROM [Joe]
-- He still sees data, because not all GRANTs are
-- removed by REVOKE
SELECT * FROM Production.Product
-- This explicitly denies permission on Product to Joe
-- Once this is eecuted, he will not be able to see data
-- even we grant him again.
DENY SELECT ON OBJECT::Production.Product TO [Joe]

Saturday, May 22, 2010

Column GRANT overrides DENY Table, Avoiding with Common Criteria Compliance

One of my friends was configuring security in his databases last week and I was helping him on it. The configuration is more on authorization, so, a bunch of GRANT and DENY statements. Yesterday he called me and explained a situation where users can execute SELECT statements on granted columns even though they have been denied for the table. I was confused. If users are denied to the table, how can they see values in columns, even though they are granted permission on columns. I thought, the rule of thumb is, DENY always overrides GRANT, isn’t it?

I tried to find reasons for this. Finally I managed to dig up reasons for this. By default, Column GRANT overrides DENY Table in SQL Server. I am not sure whether it is the default setting for all other DBMSs, but that is the normal behavior of SQL Server. Let me show you this behavior.

-- Create a login and add user to AdventureWorks
WITH PASSWORD = 'Pa$$w0rd'
USE [AdventureWorks]
-- Denies permissions to user on Production table
DENY SELECT ON Production.Product TO [Yeshan]
-- Set the Execution Context to user and see
-- whether he can access the table
SELECT * FROM Production.Product
-- The error "The SELECT permission was denied on..."
-- is thrown.
-- Grant permission to user on two columns
GRANT SELECT ON OBJECT::Production.Product
(ProductID, Name) TO [Yeshan]
-- Execute the SELECT statement again on all columns
SELECT * FROM Production.Product
-- Still no permission
-- Now lets see whether user can see the granted columns
SELECT ProductID, Name FROM Production.Product
-- Required result comes
-- Column GRANT overrides DENY TABLE

If this is not what you wish, you can enhance security to avoid it. It is done through enabling Common Criteria Compliance option. The Common Criteria for Information Technology Security Evaluation (abbreviated as Common Criteria or CC) is an international standard for computer security. SQL Server 2005 SP2 was evaluated against CC and now SQL Server is complied with Common Criteria Evaluation Assurance Level 4. Enabling this option in SQL Server, following Common Criteria are enforced;

  • Table DENY overrides Column GRANT
  • Viewing login statistics capability with sys.dm_exec_sessions
  • Residual Information Protection (RIP) compliance

What we need here is, the first criteria. Following code enables Common Criteria Compliance in SQL Server.

sp_configure 'common criteria compliance enabled', 1

You may have to restart the services in order to set the run_value of Common Criteria Compliance Enabled option. If you do not see this option with sp_configure, enable show advanced option first and reconfigure.

Once it is enabled, Column GRANT does not override DENY Table.

Friday, May 21, 2010

Reporting Services: Textbox changes position when the report is previewed

Have you ever faced a situation where a textbox (or image) changes the fixed position when the report is previewed?  One of my colleagues faced it yesterday. I had no answer for him but fixed the issue. Here is the issue:

Assume that you have designed a report like below. The region has been set as a tablix, the CalenderYear column grows based on number of years available in the dataset. Three textboxes have been placed on top of the data region.

textimage1Here is the preview of the report. The position of third textbox has been changed, aligning with the edge of tablix data region.

textimage2What I understood was, if the Location-Left property of textbox is greater than Location-Left + Size-Width of tablix data region, Location-Left property of textbox is set to Location-Left + Size-Width(Actual) when the report is previewed. But, this is not going to happen if the Location-Left property of textbox is less than Location-Left + Size-Width of tablix data region.



I tested this with a table data region and noticed that it has no such an issue, obviously, reason is, no grouping on columns in it.

There is a workaround for this. All you have to do is, place all textboxes in a rectangle. Once you have all textboxes in a rectangle, positions of textboxes are not going to be changed.


I googled for finding a reason for this but could not find. Although there is a workaround, I would like to know the reason for this, or what I have missed, and the proper way to fix it. Can you help me on it?

Wednesday, May 19, 2010

New way of Emailing…… Meet the New Hotmail

Microsoft is announcing New Hotmail, a fantastic way of emailing, it is not just another product…….

“The new Hotmail: sweep out annoying clutter, save time and do more with email than ever before, view and edit Microsoft Office documents anywhere you’re online, regardless of whether you have Office installed. Read on to learn why the new Hotmail is the most efficient email for busy people.”

Here are key updates;

  • Stay better organized
    Today’s inboxes are overflowing with mail that most people consider junk, even though it’s mail that’s legitimate.  To help keep inboxes clean and organized, the new Hotmail delivers the first and only virtual broom, enabling customers to easily “sweep” unwanted mail out of their inbox.  It also helps cut through clutter with intelligent filters that in a single click will filter the entire inbox to show mail just from contacts, just social network alerts, or just mail from the groups users belong to.

  • Manage your social networks from your inbox
    With the new Hotmail, customers will be able to see rich previews of photos and videos from popular sites like Flickr, YouTube, Hulu and SmugMug, and see the status of tracked packages in real time, inside their emails. They’ll also be able to accept invitations to connect with someone on a social or professional networking site like LinkedIn – all from the convenience of their inboxes.

  • Increased attachment size
    One of the biggest changes to Hotmail is the increased ability to manage and share personal photos and documents. Today, people are sharing over 1.5 billion photos and   350 million Microsoft Office documents per month in Hotmail.  Still, the “attachment size problem” remains, regardless of which mail service people use.  The new Hotmail lets’ customers send up 10 GB of attachments (200 attachments – each up to 50 MB in size) in a single message, erasing worries of attachment size limits.

You can also find out more at

Tuesday, May 18, 2010

Article: Reporting Services 2008 R2: Geospatial Visualization – Part I

image001My latest article, Reporting Services 2008 R2: Geospatial Visualization – Part I, has been published at SQLServerPerformance.Com. Click here to read the article. This article speaks about;

  • Map report item and sources for spatial data
  • Map layers
  • Creating a Map Report using shapefiles
  • Adding analytical data onto Map Layer

It would be good if you can read and give me feedbacks on this. The second part of it has already been written, will be published soon.

Monday, May 17, 2010

Book Review: SQL Server 2008 – Administrator’s Pocket Consultant, Second Edition

cat Title: Microsoft SQL Server 2008 – Administrator’s Pocket Consultant, Second Edition

Author: William R. Stanek

Publisher: Microsoft Press

Price: US$ 44.99

Pages: 736

O’REILLY Catalog Page:

William, who has written more than 100 books, has come up with his second edition of a superb book, SQL Server 2008, Administrator Pocket Consultant. The Administrator Pocket Consultant is a book series, started with SQL Server 7.0. This is the latest. The First Edition of this was originally written, focusing on SQL Server 2008, and it has been updated for R2 and released as the Second Edition.

This book is mainly for SQL Server Database Administrators but no doubt, it helps Database Architects too. I am not saying that the book is not for Database Developers. There are plenty of useful guidance, best practices and Tips that can be learnt and applied by them.

What exactly the book serves us? Obviously, a quick, comprehensive answers for questions raised by SQL Server Professionals, more precisely, by DBAs. This book allows SQL Server Professionals to get things related to their day to day operations clarified, sorted out in a simple and fruitful way. For example, one might ask, “What would be the appropriate SQL Server 2008 R2 edition which supposed to be installed on a Server that has four processors and supports low-cost high availability mechanisms?”, or “How often my database get shrunk? Enabling AUTO_SHRINK is a good practice?”. Answer for this can be easily found in this book. Not only that, things related and important to the questions too.

As the book itself says, it is organized according to job-related tasks of DBAs rather than SQL Server features. Chapters listed below clearly explain it.

Chapter 1: SQL Server Administration Overview
This chapter discusses on editions of SQL Server 2008 and 2008 R2, services of SQL Server, and command line tools, including PowerShell.

Chapter 2: Deploying SQL Server 2008
This starts discussing main components of SQL Server, such as Integration Services, Analysis Services, and Reporting Services, with their features. The discussion continues with deployment, and installation.

Chapter 3: Managing the Surface Security, Access, and Network Configuration
Chapter 3 explains how to enable/disable features of SQL Server. In addition to that, it describes Services Configurations, Network Configuration and Client Network Configuration.

Chapter 4: Configuring and Tuning SQL Server 2008
This chapter is dedicated for Configuring and Tuning SQL Server with various options. Options and ways include System Catalog Views, System Stored Procedures, Management Studio, Server Options and Database Options.

Chapter 5: Managing the Enterprise
This is about managing all your SQL Servers. It shows ways of managing SQL Server Groups, Servers, Startup, DTC and Activities.

Chapter 6: Implementing Policy-Based Management
This discusses all about Policy-Based Management. It explains how to create, manage, configure facets, conditions, evaluate and troubleshoot policies.

Chapter 7: Configuring SQL Server with SQL Server Management Studio
This chapter shows how you can use Management Studio for configuring your SQL Servers. It discusses about Utility Control Points, Security, Auditing, Optimizing and Tuning memory and CPU. You can find the usage of sp_configure too.

Chapter 8: Core Database Administration
This chapter is dedicated to databases; discussing almost all related things, from creating to deleting, including options can be set. In addition to that, William has added a useful section called Tips and Techniques for this chapter that shows tricks for managing databases more efficiently.

Chapter 9: Managing SQL Server 2008 Security
This chapter focuses on security, mainly on authentication and authorization on SQL Server objects. It discusses about logins, users, roles, permissions and administration in detail.

Chapter 10: Manipulating Schemas, Tables, Indexes, and Views
Operations related to tables such as creating, adding columns, selecting data types, modifying, adding indexes, adding constraints, partitioning and internals are discussed in this chapter. A section for Views is included too.

Chapter 11: Importing, Exporting, and Transforming Data
This chapter explains the ways of importing and exporting data from and to SQL Server. SSIS Packages with Import and Export wizard, BCP and BULK INSERT are explained with this.

Chapter 12: Linked Servers and Distributed Transactions
Chapter 12 is about, integrating SQL Server with other heterogeneous data sources. Distributed queries, transactions, DTC Service, Linked Servers and their security are discussed.

Chapter 13: Implementing Snapshot, Merge, and Transactional Replication
This chapter is for SQL Server Replication. Replication types and related components are explained with this chapter.

Chapter 14: Profiling and Monitoring SQL Server 2008
One of key areas to DBAs, Monitoring activities and performance are discussed with this chapter. Monitoring through Replication Monitor, Event Logs, Performance Counters, and Profiler are explained and Management Data Warehouse feature is explored.

Chapter 15: Backing Up and Recovering SQL Server 2008
This chapter starts from creating backup and recovery plans and then continues with various related components to backup operations such as backup devises, media and strategies, finally restoring.

Chapter 16: Database Automation and Maintenance
Some of main database automation and maintenance components such as Database Mail, SQL Server Agent, and Database Maintenance Plans are discussed with Chapter 16.

Chapter 17: Managing Log Shipping and Database Mirroring
This chapter is for high availability and disaster recovery. It explains that how Log Shipping and Database Mirroring help to achieve high availability and design disaster-recovery solution.

Overall, this book is a really a true-friend for DBAs, and they must have it with them. I would be glad to see SQL Server Clustering with this book, unfortunately it has not been added as a Chapter, or has not been included in detail with any other chapters. Anyway, if you are a SQL Server DBA, trust me :), you do not want to think twice, buy the book.

Sunday, May 16, 2010

DBCC CHECKIDENT does not reset identity value as you need?

DBCC CHECKIDENT is used for checking the current identity value in a table and can be used for changing and resetting the current identity value too. Sometime, resetting the current identity value with RESEED option makes developers confused (once it confused me too :)) because it does not always use the given value as current identity value for next insert statement. For example, below code is for resetting the current identity value of Table1 as 0.


SQL Server will use the value ‘0’ as the identity value for next insert statement if:

  • No rows have been inserted to the table since it was created
  • Records have been deleted using TRUNCATE statement

Otherwise, identity value for next insert will be 0 + increment, in this case, if the increment has been set as 1, value 1 will be used as the identity value for next insert.

RAID for SQL Server: Advantages and Disadvantages

What would be the best RAID configuration for SQL Server databases? It is always being asked and discussed in my classes, so thought to put what we discussed (explained) as a post:

What is RAID?
RAID stands for Redundant Array of Independent Disks. It is a technology that can be used for increase (high) data availability and increase IO performance and it was introduced by David Patterson, Garth Gibson, and Randy Katz at the University of California, Berkeley in 1987. It was formerly known as Redundant Array of Inexpensive Disks. There are different types of architectures for RAID, which are named with the word RAID suffixed by a number, such as RAID 0, RAID 1, and RAID 5.

What is different between Hardware RAID and Software RAID?
Hardware RAID is implemented with set of physical storage devises and RAID controller cards. It is expensive than Software RAID. Software RAID IS implemented with system resources and managed by the OS. It is less expensive than Hardware RAID but requires more CPU power and memory. Software RAID does not support (or merely support) fault-tolerance but Hardware RAID.

RAID 0 with SQL Server
RAID 0 is known as Disk Striping and uses set of configured disks called as Stripe Set. This can be configured with minimally two disks and set as an array. It provides good read and write performance because data is distributed across multiple disks. This does not support fault-tolerance, hence if one disk is failed, the entire Stripe Set fails. Although it does not provide fault-tolerance, it can be used with SQL Server, mainly for data files, to speed up read and write performance. In addition to that, it uses all the space available, and worth for what you have spent on it.

Note: All RAID configured SQL Server systems I have seen are NOT RAID 0. Most go for other RAID configuration when it comes to SQL Server, though the cost and performance are key factors for deciding the RAID.

RAID 1 with SQL Server
RAID 1 is known as Disk Mirroring and uses two disks configured as a Mirror Set. Data is written (duplicated) to both disks, hence supports fault tolerance. If one fail, recovery is easier, and because of a full duplicate disk, operation can be continued until the failed one is replaced. It does not give good performance on data writes because data must be written for two disks. Data read performance is higher than a single disk because reading can be split over two disks.

Biggest disadvantage of RAID 1 is, losing 50% of disk space. If your requirement is 100 GB, you have purchase 200GB for setting up RAID 1. You have to double the required amount of space but gain no additional storage space.

RAID 1 is a good choice for Operating System of SQL Server system and SQL Server log files. Since RAID 1 can continue with a failed disk and replacement is faster than any other RAID configuration, it is recommended to have the OS in RAID 1. SQL Server Log files are mostly written to, rarely read from, if there is a transaction to rollback. Although RAID one takes some additional resources/time for writing, dedicated RAID 1 for each log file is recommended. One reason could be the way it fills the log file, it writes sequentially. Other reason is fault-tolerance. Considering all these reasons, best choice for log files is RAID 1.

RAID 5 with SQL Server
This is know as Striping with Parity and configured as a Stripe Set. This is similar to RAID 0 which write data across multiple disk. This requires at least three disks. Not like RAID 0, RAID 5 support fault-tolerance by striping data across multiple disks and storing parity information as data is written. Since parity information is available, in case of a disk failure, parity information can be used for re-creating the data lost on failed disk, allowing us to continue the operation. Note that in case of multi-disks failure, parity will not be available for re-creating all data lost, hence entire Stripe Set will fail.

You noted that RAID 1 gives good read performance. RAID 5 gives good read performance too, but better than RAID 1. This is because reads are performed across multiple disks. More disks in array gain more performance on reads. But write performance in RAID 5 is poor. This is because of more IO operations and parity calculation. In terms of storage space, again, RAID 5 is ahead. RAID 5 with three disks used, uses one-third of total disk space for holding parity information, hence the overhead is around 33%. This overhead decreases as number of disks are increased.

RAID 5 is usually used for holding history data which frequently read, and rarely write. When a table is partitioned, old data can be placed on a file group which files are configured with RAID 5. So, can we use RAID 5 for SQL Server data files? Yes you can, useful rule of thumb is, if your database has 90% read operations and 10% write operations, your choice would be RAID 5.

RAID 0+1 with SQL Server
RAID 0+1 is a combination of disk striping and mirroring. It is a two Stripe Sets mirrored, or two duplicates of Stripe Sets (do not confuse with 1+0, which is a Stripe Sets configured with 2 or more mirrored sets). This gives best performance of Stripe Sets while providing fault-tolerance. Data read is faster, just like RAID 0 or 5, and read can be split over mirrored set too. Data write requires two IO operations because of duplicate set. Overall this provides best performance while supporting fault-tolerance. This has an ability to continue the operation even after multiple disks failure in one Stripe Set. Failure of disk from both side of mirror will be a failure of entire RAID.

As stated in RAID 5, it is better for more read operations. But RAID 0+1 offers performance on both read and write. The biggest disadvantage of RAID 0+1 is the number of disk required. This RAID configuration minimally requires 4 disks.

Generally, we use RAID 0, 1, 5 or 0+1 for SQL Server. Key factors to be considered for selecting the architecture are cost, amount of each type of operations (reading and writing) and whether fault-tolerance is required or not.

Monday, May 10, 2010

SQL Server 2008 R2 Pricing and Licensing

Here are some links which will help you to understand editions, pricing, and licensing of SQL Server 2008 R2.

Editions of SQL Server 2008 R2 (two new editions: Datacenter, Parallel Data Warehouse)

Pricing of SQL Server 2008 R2 (per processor pricing has gone up, Enterprise by 15%, Standard by 25%)

Purchasing options of SQL Server 2008 R2

Edition features and comparisons of SQL Server 2008 R2

Friday, May 7, 2010

Reporting Services 2008: Showing HTML content, and what it really renders

Embedding HTML within text is possible with Reporting Services. But the problem is, whether Reporting Services supports all HTML tags or not, and that is what I am going show here:

Simply, if you get a value that contains HTML tags, the value can be shown as “HTML”, not as just a value. For example, if a value coming from a column is something like this:

<b><i>Can this be render as HTML?</i></b>

This can be shown as:

Can this be render as HTML?

This does not automatically happen. If you do not  instruct to Reporting Services to render the values as HTML, it will be shown as it is.
REPORT HTML The instruction has to be passed via placeholders. As you know, when you place a field in a cell, it is placed in a placeholder, and all your formatting will be applied to the placeholder. Reporting Services 2008 allows you to add multiple placeholders in a single cell and have different formatting on them, resulting a cell contains values from multiple columns, with different formatting. 





So, if you get a value like above and need show them as you want, not just set of HTML tags, get the properties of the placeholder and select the second radio button HTML – Interpret HTML tags as styles.

REPORT HTML2When I tested this, I noticed a kind of issue (for me :)), a discrepancy with BIDS preview and SSRS Report Manager preview. Let me show you. I created below table in tempdb and inserted one record. The values in the record contains set of HTML tags. What I have really added to both Description1 and Description2 is:
Here is the code.

    Id int PRIMARY KEY,
    Description1 varchar(max) NOT NULL,
    Description2 varchar(max) NOT NULL
    (Id, Description1, Description2)
<p><span style=''font-size:8.5pt;font-family:"tahoma";color:blue''>
<b>SQL Server 2008 R2 has RTMed</b></span></p>
<p><font size=8.5 color=black face=tahoma>Exciting News! SQL Server 2008 R2 has RTMed 
today. You can get more information, resources and download a free trial
here:&nbsp;<a href="" target="_blank"><font color=blue ></font></a>. 
<p><font size=8.5 color=blue face=tahoma><b>SQL
Server 2008 R2 has RTMed</b></font></p>
<p><font size=8.5 color=black face=tahoma>Exciting News! SQL Server 2008 R2 has RTMed 
today. You can get more information, resources and download a free trial
here:&nbsp;<a href="" target="_blank"><font color=blue ></font></a>.

If you clearly go through the code, you will see that, though the output of HTML tags are same, a small different is exist between Description1 HTML tags and Description2 HTML tags, for the title which is “SQL Server 2008 R2 has RTMed”. Then I created a Reporting Services project and created a report with this record. Here is my report with BIDS.

Then I opened the placeholder properties of Description1 and set the Markup type as HTML. I did same for Description2 too. Here is the preview of the report.

My next step was, publishing the report. This is what I see when the report is published.

Can you see the difference of preview between designer and report manager? This is because of the HTML tags we have used with Description1. Designer preview shows both columns as we need but Report Manager does not show it properly. I am not sure about the reason, it must be a limitation with HTML renderer in Reporting Services.

HTML Tags supported by Reporting Services 2008
As I mentioned before, Reporting Services 2008 does not support all HTML tags. Here are tags that can be used with Reporting Services 2008.

<A> <FONT> <H1>, <H2>, <H3>, <H4> <SPAN> <DIV> <P> <BR> <LI> <B> <I> <U> <S> <OL> <UL>

Remember, although it supports tags like <SPAN> and <DIV>, it seems it does not take the attributes we add to them. Note that I have set the same style for Description1 title, just like the one I have applied to Description2. Only difference is, I have used <SPAN> for Description1 and <FONT> for Description2. Once complied and published, it seems the Reporting Services has not taken the added attributes in <SPAN> tag to the complied report.

Now you know the how and what we can apply. Have fun!