SSRS: Export report to the Excel 2003 file

Using the SQL Server 2005 SSRS you can format the reports and use the RDL file in your application to export it to an EXCEL 2003 file format
Note: Excel 2007 is not supported and
The deployment machine should have "Microsoft.ReportViewer.WinForms" in the GAC.

Sample Code:
//Namespace
using System;
using System.Data;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Collections.Generic;
using System.Drawing.Imaging;
using System.Drawing.Printing;
using Microsoft.Reporting.WinForms;
//Namespace
// Function to get the datatable
private static DataTable LoadSalesData()
{
// code to get the Data Table
}
// Main code to render report to EXCEL 2003 file
static void Main(string[] args)
{
Warning[] warnings;
string[] streamids;
string mimeType;
string encoding;
string extension;
ReportViewer reportViewer = new ReportViewer();
// Set Processing Mode
reportViewer.ProcessingMode = ProcessingMode.Local;
// Set RDL file
reportViewer.LocalReport.ReportPath = "MyReport.rdl"; //Keep the RDL file in the Bin Folder
// Supply a DataTable corresponding to each report data source
reportViewer.LocalReport.DataSources.Add(
new ReportDataSource("MyDataset", Program.LoadSalesData()));
// The Above Dataset name should be same as in the RDL file
// Process and render the report
reportViewer.RefreshReport();
// Render to Excel
byte[] bytes = reportViewer.LocalReport.Render("Excel", null, out mimeType, out encoding, out extension, out streamids, out warnings);
FileStream fs = new FileStream(@"c:\outputReport.xls", FileMode.Create);
fs.Write(bytes, 0, bytes.Length);
fs.Close();
}

Deployment in the production Box
Deploying Reports and ReportViewer Controls
You can freely distribute reports and the ReportViewer controls as part of your application. Deployment requirements vary widely depending on the control type and whether your report is configured for local or remote processing. You can deploy locally processed and remotely processed reports in the same application.
Redistributing the ReportViewer Controls
The ReportViewer control redistributable is a self-extracting file named ReportViewer.exe that includes an .msi and other files. You can find ReportViewer.exe at the following location: C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\BootStrapper\Packages\ReportViewer\ReportViewer.exe.
When you run ReportViewer.exe, the following files are copied to the Global Assembly Cache folder on the deployment computer.
File
Microsoft.ReportViewer.Common
Microsoft.ReportViewer.ProcessingObjectModel
Microsoft.ReportViewer.WebForms
Microsoft.ReportViewer.WinForms
for more information visit http://msdn.microsoft.com/en-us/library/ms251723(VS.80).aspx

Issue:

While formatting data in the html, we can use CanGrow and CanShrink to adjust the Cell width and Height, but while exporting/rendering to Excel, all the cells in the Excel sheet having Text Wrap Enabled. There is no way to resolve this, except using Interop Services of Excel, but it requires Excel to be installed on the Production Box.

Resolution:

The issue occured when i select a single column and made CanGrow = False and CanShrink = True. If you select all the columns and set these values then the issue is resolved. ;-)

SQL SERVER 2005: Use XACT ABORT to roll back non trapable error transactions

The Usual way to code for transaction is
BEGIN TRANSACTION;
BEGIN TRY
--What if you are calling a function which is missing in the DB
--What if you are reference a table in another database without synonym
--Under some of these above issues the transaction will not roll back
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
SELECT @Return = ERROR_NUMBER()
SELECT ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_LINE () as ErrorLine,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_MESSAGE() as ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;

RETURN @Return
END CATCH

So in order to catch non trapable error transactions use SET XACT_ABORT ON it traps all the errors and rolls back the Transaction

Another good link to check http://wiki.lessthandot.com/index.php/Use_XACT_ABORT_to_roll_back_non_trapable_error_transactions

SQL SERVER 2005: Removing Duplicate records

The following post tell you how to remove duplicate records from a table not having primary key
CREATE TABLE T1
(
[Ename] char(10) NULL,
[ECity] char(10) NULL
)
GO
INSERT INTO T1 (Ename, ECity)
SELECT 'Hamed', 'Bellevue'
UNION ALL
SELECT 'Billg', 'Redmond'
UNION ALL
SELECT 'Billg', Redmond
Now that duplicate records exists...
; WITH T1CTE(Ename, ECity, Ranking) AS
(
SELECT Ename, ECity, Ranking = DENSE_RANK() OVER (PARTITION BY Ename, ECity ORDER by NEWID() ASC)
FROM T1
)
DELETE FROM T1CTE WHERE Ranking > 1
-- Duplicates from the underlying table T1 is removed when we process the Virtual table T1CTE
-- To verify run the following query
SELECT * FROM T1;

Another good reference: http://blogs.techrepublic.com.com/datacenter/?p=420