Take Rows orignal values after update

CREATE TABLE testOutPutClause
(id INT IDENTITY (1,1)
,Name VARCHAR(MAX)
,ModifiedBy INT
,Modifiedon DATETIME
)
INSERT INTO testOutPutClause VALUES('A',1, GETDATE())
INSERT INTO testOutPutClause VALUES('B',1, GETDATE())
INSERT INTO testOutPutClause VALUES('C',1, GETDATE())
INSERT INTO testOutPutClause VALUES('D',1, GETDATE())
INSERT INTO testOutPutClause VALUES('E',1, GETDATE())
DECLARE @tmpTable TABLE(
id INT
,Name VARCHAR(MAX)
,ModifiedOn DATETIME
,ModifiedBy INT
)
UPDATE testOutPutClause
SET Name=Name+'U'
,ModifiedBy = 1 + 10
OUTPUT
deleted.id
,Deleted.Name
,Deleted.Modifiedon
,Deleted.ModifiedBy
 INTO @tmpTable

 SELECT * FROM testOutPutClause

  SELECT * FROM @tmpTable
 
  DROP TABLE testOutPutClause

Rest Identity column

DBCC CHECKIDENT('tablename', RESEED)

Dynamic script for add columns

DECLARE @TableNameContainer TABLE
(
id INT IDENTITY(1,1)
,tableName VARCHAR(MAX)

)
DECLARE @RowCount INT, @index INT = 1;
INSERT INTO @TableNameContainer
SELECT name FROM sys.tables t2 WHERE
t2.name NOT IN ('Table1','Table2')-- list of table where you did not want to add
SELECT @RowCount = COUNT(*) FROM @TableNameContainer
WHILE (@index <= @RowCount)
BEGIN
DECLARE @tableName NVARCHAR(MAX) =(SELECT tableName FROM @TableNameContainer WHERE id= @index )
DECLARE @dynamicAlter NVARCHAR(MAX) =
'IF NOT EXISTS (SELECT * FROM   sys.columns  WHERE  object_id = OBJECT_ID(N''[dbo].['+@tableName+']'') AND name = ''CreatedBy'')
BEGIN ALTER TABLE ['+@tableName+'] ADD [CreatedBy] [int] NULL END

IF NOT EXISTS (SELECT * FROM   sys.columns  WHERE  object_id = OBJECT_ID(N''[dbo].['+@tableName+']'') AND name = ''CreatedDate'')
BEGIN ALTER TABLE ['+@tableName+'] ADD [CreatedDate] [datetime]  NULL  END

IF NOT EXISTS (SELECT * FROM   sys.columns  WHERE  object_id = OBJECT_ID(N''[dbo].['+@tableName+']'') AND name = ''ModifiedBy'')
BEGIN ALTER TABLE ['+@tableName+'] ADD [ModifiedBy] [int] NULL  END

IF NOT EXISTS (SELECT * FROM   sys.columns  WHERE  object_id = OBJECT_ID(N''[dbo].['+@tableName+']'') AND name = ''ModifiedBy'')
BEGIN ALTER TABLE ['+@tableName+'] ADD [ModifiedDate] [datetime] NULL  END

IF NOT EXISTS (SELECT * FROM   sys.columns  WHERE  object_id = OBJECT_ID(N''[dbo].['+@tableName+']'') AND name = ''IsDeleted'')
BEGIN ALTER TABLE ['+@tableName+'] ADD [IsDeleted] [bit] NULL  END

IF NOT EXISTS (SELECT * FROM   sys.columns  WHERE  object_id = OBJECT_ID(N''[dbo].['+@tableName+']'') AND name = ''DeletedBy'')
BEGIN ALTER TABLE ['+@tableName+'] ADD [DeletedBy] [int] NULL  END
     
IF NOT EXISTS (SELECT * FROM   sys.columns  WHERE  object_id = OBJECT_ID(N''[dbo].['+@tableName+']'') AND name = ''DeletedBy'')
BEGIN ALTER TABLE ['+@tableName+'] ADD [DeletedDate] [datetime] NULL  END    
'
EXECUTE sp_executesql @dynamicAlter
SET @index = @index + 1;
END-- OF WHILE

CPT vs HCPCS

CPT is a code set to describe medical, surgical ,and diagnostic services; HCPCS are codes based on the CPT to provide standardized coding when healthcare is delivered.

2. CPT is largely private, as the AMA has copyright ownership and does not wish to give the codes freely; according the Health Insurance Portability and Accountability Act of 1996, everyone must have access to HCPCS codes.

3. DRG codes are separate from HCPCS and CPT.Codes. DRG related to secondary diagnosis and primary diagnosis.

Scroll automatically to the bottom of the page

window.scrollTo(0,document.body.scrollHeight);

hide a DIV when the user clicks outside of it

hide a DIV when the user clicks outside of it

$(document).mouseup(function (e)
{
    var container = $("YOUR CONTAINER SELECTOR");

    if (!container.is(e.target) // if the target of the click isn't the container...
        && container.has(e.target).length === 0) // ... nor a descendant of 

//the container
    {
        container.hide();
    }
});

The magic is the not using click. 

Stop SQL job from commands

Jobs are not stopping when click on stop button of job. so I used follwing command to stop it.

SELECT j.name as jobname

   

              , j.*,ja.*

FROM msdb.dbo.sysjobactivity ja

JOIN msdb.dbo.sysjobs j

    ON ja.job_id = j.job_id

WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)

AND start_execution_date is not null

AND stop_execution_date is null;

Step 2-> Copy all rows in Excel.

Step 3-> look the DB name, Job execution time, Job started date etc.

Step 4-> Now select correct SPID and stop it.

USE msdb ; GO EXEC dbo.sp_stop_job N'Job Name' ; GO

--sp_who2

--kill -58-  

Where 58 Is SPID which I want to stop.

SSRS-Report builder calculate date difference

=Datediff("d", Fileds!fromdate.Value,  now())

below are the list of 'code':

Setting	Description
yyyy	Year
q	Quarter
m	Month
y	Day of year
d	Day
w	Weekday
ww	Week of year
h	Hour
n	Minute
s	Second

SSRS: Detecting NULL dates and showing empty string

=IIF(Fields!f.Value= nothing, " ", Format(CDate(Fields!f.Value),"MMM dd, yyyy hh : mm tt"))
Thanks
Mahesh

How to split minutes into days, hours and minutes in tsql

Declare @theMinutes int
Set @theMinutes = 2147483647

Select convert(varchar(15), @theMinutes / 1440 ) +' Days'
       + ' ' + REPLICATE('0', 2 - DATALENGTH(convert(varchar(2), (@theMinutes % 1440) / 60 ))) + convert(varchar(2), (@theMinutes % 1440) / 60 ) + ' Hours '
       + ' ' + REPLICATE('0', 2 - DATALENGTH(convert(varchar(2), (@theMinutes % 60)))) + convert(varchar(2), (@theMinutes % 60)) + ' Minutes 'as Days_Hours_Minutes

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"),
       FileMode.Create);
       fs.Write(bytes, 0, bytes.Length);
       fs.Close();

       //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));
       document.Open();
       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) {
          document.NewPage();
          p++;
          i++;

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

       document.Close();
    }

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

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.

=FormatNumber(Fields!Balance_Due.Value,2)

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

Syntax for formating the date

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

=FormatPercent(Fields!ColumnName_Interest_Rate.Value/100,0)

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

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

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

2.
<asp:ScriptManager ID="ScriptManager1" runat="server">
    </asp:ScriptManager>
        <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="http://192.168.2.12/ReportServer" />
        </SpadezReport:ReportViewer>

3.


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

Find out the last usage date of each database

SELECT
stat.database_id AS database_id
,db.name 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,db.name
ORDER BY db.name ASC

Get description writen in Table

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

2.

SELECT   u.name + '.' + t.name AS [table],
            td.value AS [table_desc],
      c.name 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 = t.id
    AND  td.minor_id = 0
    AND  td.name = 'MS_Description'
INNER JOIN  syscolumns c
    ON  c.id = t.id
LEFT OUTER JOIN sys.extended_properties cd
    ON  cd.major_id = c.id
    AND  cd.minor_id = c.colid
    AND  cd.name = 'MS_Description'
WHERE t.type = 'u'
ORDER BY    t.name, c.colorder

SSRS: Test Pickup directory of SMTP server

to: mahesh.sharma@sttttttttt.com
from: uuuuuu.yy@gmail.com
subject:This is a test.

This is a test.

SSIS: Derived Column

Add in Expression column
If single if else
(Active ==  0  ? "IAmNotActive" : "IamActive")
Where active is column name.

Second when you have multiple account:

(Age< 20 ? 1 :
(Age>= 20 && Age< 30 ? 2 :
(Age>= 30 && Age< 40 ? 3 :
(Age>= 40 && Age< 50 ? 4 :
5))))

means if (Age< 20){1}
else if (Age>= 20 && Age< 30) {2}
else if (Age>= 30 && Age< 40) {3}
else if (Age>=  40 && Age< 50 ){4}
else {5}
Where Age is column name

Prevent table row onclick event to fire when clicking button inside the row

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
   <script type="text/javascript">
      function test(e) {
         alert('button');
         e.stopPropagation();
      }
   </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
      <table border="1" cellpadding="0" cellspacing="0">
          <tr onclick="javascript:alert('li')" style="cursor:pointer">
             <td>
             <input  type="button" onclick="test(event)" value="click me" />
             </td>
             <td>ll</td>
          </tr>
       </table>
    </div>
    </form>
</body>
</html>

SQL Update table column if parameter has value in sigle update statement

CREATE TABLE test2(clm1 INT,clm2 INT)

INSERT INTO test2 VALUES(1,1)
INSERT INTO test2 VALUES(2,2)
INSERT INTO test2 VALUES(3,3)
INSERT INTO test2 VALUES(4,4)


DECLARE @par1 INT = 100;
DECLARE @par2 INT = NULL;

UPDATE test2
SET clm1 = COALESCE(@par1,clm1)
,clm2 = COALESCE(@par2,clm2)

WHERE clm1 = 1

SELECT * FROM test2

DROP TABLE test2

search text in a stored procedure

declare @Text varchar(max);
set  @Text =1016
SELECT DISTINCT o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON m.object_id=o.object_id
WHERE m.definition Like '%'+@Text+'%'

Get List of All Database created on server

SELECT name, user_access_desc, is_read_only, state_desc, recovery_model_desc,create_date
FROM sys.databases ORDER BY create_date DESC

Fetch All td from table and add events dynamicly with JavaScript No jQuery

Table

  <table  border="1" cellpadding="0" cellspacing="0" width="600px" >
   <tbody class='tbodyClass'>
 
      <tr>
         <td id="a" class="a">
         AAAAAAAA
         AAA
         aAA
         </td>
         <td class="b"></td>
      </tr>
        <tr>
         <td id="b" class="a">
         BBB  <br />
         BBB<br />
         BBB<br />
         </td>
         <td class="b"></td>
      </tr>
        <tr>
         <td   id="c"  class="a">
         CCC<br />
         CCC<br />
         CCC<br />
         </td>
         <td class="b"></td>
      </tr>
        <tr>
         <td   id="d"  class="a">
         DDD<br />
         DDD<br />
         DDD<br />
         </td>
         <td class="b"></td>
      </tr>
       </tbody>
   </table>

Script

 <script type="text/javascript">

      function clickCellTest() {
         alert(this.id);
      }
      window.onload = function MaheshTest() {
         var ab = document.getElementsByClassName('tbodyClass')[0];

         ab = ab.getElementsByTagName('td');
         for (var i = 0; i < ab.length; i++) {
            var element = ab[i];
            element.addEventListener("click", clickCellTest, false);
         }
      }

   
   </script>