Sunday, March 30, 2014

RDLC: Generate PDF form RDLC wtih itext Sharper

 private void GeneratePDFFRomRDLC() {
       Warning[] warnings;
       string[] streamids;
       string mimeType;
       string encoding;
       string extension;

       byte[] bytes = ReportViewerName.LocalReport.Render("PDF", null, out mimeType,
                      out encoding, out extension, out streamids, out warnings);

       FileStream fs = new FileStream(HttpContext.Current.Server.MapPath("output.pdf"),
       fs.Write(bytes, 0, bytes.Length);

       //Open existing PDF
       Document document = new Document(PageSize.LETTER);
       PdfReader reader = new PdfReader(HttpContext.Current.Server.MapPath("output.pdf"));
       //Getting a instance of new PDF writer
       PdfWriter writer = PdfWriter.GetInstance(document, new FileStream(
          HttpContext.Current.Server.MapPath("Print"+Guid.NewGuid().ToString()+".pdf"), FileMode.Create));
       PdfContentByte cb = writer.DirectContent;

       int i = 0;
       int p = 0;
       int n = reader.NumberOfPages;
       Rectangle psize = reader.GetPageSize(1);

       float width = psize.Width;
       float height = psize.Height;

       //Add Page to new document
       while (i < n) {

          PdfImportedPage page1 = writer.GetImportedPage(reader, i);
          cb.AddTemplate(page1, 0, 0);


SQL: Create temp table without declaring and add new column or remove existng column

select * into #temptest from existingTable

alter table #temptest add LoantypeID int

alter table #temptest  drop column InstallmentType 

drop table #temptest

Sunday, March 23, 2014

SSRS:The '@paramenterName ' parameter is missing a value

  • The '@paramenterName' parameter is missing a value.
  • It can happen when parameter contains value which is not in the data source of report.
  • for this you have to get value in parameter as field in report.
  •                                                                            ReportViewer1.ServerReport.GetParameters()["@paramenterName"].ValidValues.Select(t => t.Label).ToList().Distinct().ToList()

SSRS: Report Builder- how to format Number in reprot

Right clikc on value column. go to expression.

select CommonFunciton go to Text go to format number and set it.


Balance_Due: is value column name. 2 is decimal places.

Syntax for formating the date

Format(Cdate(Fields!ColumnName_Date.Value),"MMM dd, yyyy")


Tuesday, March 18, 2014

SSRS: Pass multiple value in rdlc report stored on report server from application.

Create a multiple selection report on report service.
Test it by selecting drop down checkboxes.
when it ready:
Add following in application:

<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
   Namespace="Microsoft.Reporting.WebForms" TagPrefix="SpadezReport" %>

<asp:ScriptManager ID="ScriptManager1" runat="server">
        <SpadezReport:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana" Font-Size="8pt"
            InteractiveDeviceInfos="(Collection)" ProcessingMode="Remote" WaitMessageFont-Names="Verdana"
            WaitMessageFont-Size="14pt" Height="100%" Width="100%">
            <ServerReport ReportPath="/BranchNameWithDropdown" ReportServerUrl="" />


List<String> bn = new List<string>() { "Branch1","Branch2","Branch3"
//Parameters values from dropdown.
       if (!IsPostBack) {
          var reportParameter = new ReportParameter("pBranchName", bn.ToArray(),false);

Saturday, March 15, 2014

Find out the last usage date of each database

stat.database_id AS database_id
, AS database_name
,MAX(stat.last_user_scan) AS last_user_scan
FROM  sys.dm_db_index_usage_stats AS stat
JOIN sys.databases AS db
ON db.database_id = stat.database_id
GROUP BY stat.database_id,

Friday, March 14, 2014

Get description writen in Table

select * from sys.extended_properties where name = 'MS_Description'

SELECT + '.' + AS [table],
            td.value AS [table_desc], AS [column],
      cd.value AS [column_desc]
FROM     sysobjects t
INNER JOIN  sysusers u
    ON  u.uid = t.uid
LEFT OUTER JOIN sys.extended_properties td
    ON  td.major_id =
    AND  td.minor_id = 0
    AND = 'MS_Description'
INNER JOIN  syscolumns c
    ON =
LEFT OUTER JOIN sys.extended_properties cd
    ON  cd.major_id =
    AND  cd.minor_id = c.colid
    AND = 'MS_Description'
WHERE t.type = 'u'
ORDER BY, c.colorder

Tuesday, March 11, 2014

SSRS: Test Pickup directory of SMTP server

subject:This is a test.

This is a test.