Thursday, July 28, 2011

Export DataTable to Excel

private void btnExportToExcel_Click(object sender, EventArgs e)

{

//Export to Excel code


try

{

string attachment = "attachment; filename=Contact.xls";


HttpContext.Current.Response.Clear();

HttpContext.Current.Response.Clear();

HttpContext.Current.Response.AddHeader("content-disposition", attachment);

HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";

string sTab = "";

foreach (DataColumn dc in resultTable.Columns)

{

HttpContext.Current.Response.Write(sTab + dc.ColumnName);


sTab = "\t";

}

HttpContext.Current.Response.Write("\n");


int i;

foreach (DataRow dr in resultTable.Rows)

{

sTab = "";


for (i = 0; i < resultTable.Columns.Count; i++)

{

HttpContext.Current.Response.Write(sTab + dr[i].ToString());


sTab = "\t";

}

HttpContext.Current.Response.Write("\n");


}

HttpContext.Current.Response.End();


}

catch (Exception ex)


{

ErrorMessage = ex.Message;

ErrorLabel = new Label();


ErrorLabel.Text = ErrorMessage;

this.Controls.Add(ErrorLabel);


}

}

Wednesday, July 20, 2011

SSRS 2008 R2 - Unable to Connect to Remote Server Error

I had installed SQL Server 2008 R2 on windows 7, but could not able to connect to the report server at
http://pc-0260:13000/Reports

Try doing the following things
Go to Reporting Services Conf. Manager.

1. Change the server account to local account.
2. Remove the SSL if configured.
3. Go to C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer
4. Change the  SecureConnectionLevel value to 0 in rsreportserver.config

Try refreshing the page, it should work now!

Friday, July 8, 2011

Spliting String in SQL Server Stored Procedure

declare @par varchar(255)
set@par = 'string1, string2,string3'

declare @val as varchar(255)

create table #temp (col1 varchar(255))

while (charindex(',',@par)>0)

begin

insert into #temp

select Cast(replace(substring(@par,1,charindex(',',@par)),',','') as varchar)

set @par = substring(@par,charindex(',',@par)+1,len(@par))

end

insert
into #temp Select @par


Select * from #temp

drop table #temp