Deleting duplicate rows

A very cool technique to find and delete duplicate rows and just keep one unique row uinsg CTE ( common table expression) in SQL 2008

Initially the table had data

updateId  ptno
1                95689
2                95689
3                95689
4               91458
5               91000

Result desired
1               95689
4               91458
5               91000

(3 unique rows)

SELECT UpdateID,Ptno INTO #TempPtno From  dataTable1
WHERE uploaded = 0
AND deleted=0
AND flag=0 ;

/* Delete Duplicate records */
WITH CTE ( UpdateId,Ptno, DuplicateCount)
AS
(
SELECT UpdateId,Ptno,
ROW_NUMBER() OVER(PARTITION BY Ptno ORDER BY ptno) AS DuplicateCount
FROM #TempPtno
)
DELETE
FROM CTE
WHERE DuplicateCount > 1



**If there is a semicolon missing at the end of the statement before WITH CTE the following ing error will be thrown
Msg 319, Level 15, State 1, Procedure  procName , Line number
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Accessing control in a listview from code behind

In this case we are accessing the hyperlink control from the code behind to format the NavigateURL property of the control

 Protected Sub lvTACRlist_ItemDataBound(ByVal sender As Object, ByVal e As  System.Web.UI.WebControls.ListViewItemEventArgs) Handles lvTACRlist.ItemDataBound

        Dim h1 As HyperLink = CType(e.Item.FindControl("hyperLink1"), HyperLink)
        h1.NavigateUrl = h1.NavigateUrl + "&H=" + ddhoslist.SelectedValue.ToString()

 End Sub

Accessing control in a gridview from code behind

Protected Sub GridView1_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.PreRender

For Each gvRow In GridView1.Rows
 If gvRow.RowType = DataControlRowType.DataRow Then
   Dim h1 As HyperLink = CType(gvRow.Cells(0).Controls(0), HyperLink)
   h1.NavigateUrl = h1.NavigateUrl & "&H=" & Request.QueryString("H")
 End If
Next

End Sub

Accessing masterpage objects from child page

I had a project where I had to pass in the hospital code to the link button that was at the top of the each page . The top section of the each page was a masterpage.

I added a property called SetHospitalCode on the masterpage as follows

Public m_HosCode As String
    Public Property SetHospitalCode() As String
        Get
            Return m_HosCode
        End Get

        Set(ByVal value As String)
            m_HosCode = value
        End Set

    End Property

--------
Protected Sub DashBoard_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles DashBoard.Click
      
   If m_HosCode Is Nothing Then
            Response.Redirect("DashBoard.aspx")
    Else
           Response.Redirect("DashBoard.aspx?H=" + m_HosCode.ToString())
   End If
End Sub

I set the property from the each child page as follows
Dim getMaster As MasterPage
 getMaster.SetHospitalCode =  hospitalcode 

where hospitalcode is the new value of the hospital from each page.
DashBoard is the ID of the linkbutton

 

Data Conversions

taken from http://msdn.microsoft.com/en-us/library/ms187928.aspx

Error:The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

Here is the sp that caused the error

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PROC_ARCHIVE_EOM]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PROC_ARCHIVE_EOM]
GO
/** ***** Version *****
* $Revision:  $
* $Date: $
* $Author: $
* $Archive: $
*
* Sample use: exec PROC_ARCHIVE_EOM
* Comments : sp to perform the monthly archive of the transactions table.
* */
CREATE PROC PROC_ARCHIVE_EOM
AS
DECLARE @myCheckdate varchar(10)
SELECT @myCheckdate = convert(varchar(10),SuccessDate,101) from dbo.JobsRunlog where JobID = 10
--SELECT @myCheckdate
If @myCheckdate = convert(varchar(10),GETDATE(),101)
BEGIN

BEGIN TRAN TRAN1

---insert into the archive table
INSERT INTO Table_Archive
SELECT p.* FROM Patient_Table p WITH (nolock)
INNER JOIN clients c
ON p.hospital = c.hospitalcode
WHERE c.active = 1
AND
 year(RCVD) = year(getdate()) and month(RCVD)< month(getdate()))

IF @@error <> 0 GOTO  Error_Handler


COMMIT TRAN TRAN1

END


Error_Handler:
RAISERROR('error occurred while archiving the patient table',16,1)
ROLLBACK TRAN TRAN1




Added @@tranCount check to avoid this error

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PROC_ARCHIVE_EOM]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PROC_ARCHIVE_EOM]
GO

CREATE PROC PROC_ARCHIVE_EOM
AS
DECLARE @myCheckdate varchar(10)
SELECT @myCheckdate = convert(varchar(10),SuccessDate,101) from data2link.statusUpdates.dbo.JobsRunlog where JobID = 10
--SELECT @myCheckdate
If @myCheckdate = convert(varchar(10),GETDATE(),101)
BEGIN
BEGIN TRAN TRAN1

---insert into the archive table
INSERT INTO Table_Archive
SELECT p.* FROM Patient_Table p WITH (nolock)
INNER JOIN clients c
ON p.hospital = c.hospitalcode
WHERE c.active = 1
AND
year(RCVD) = year(getdate()) and month(RCVD)< month(getdate()))


IF @@error <> 0 GOTO Error_Handler


IF @@TRANCOUNT > 0

 COMMIT TRAN TRAN1


END


Error_Handler:
RAISERROR('error occurred while archiving the patient table',16,1)
IF @@TRANCOUNT > 0
ROLLBACK TRAN TRAN1

To find which processID the current SQLServer is running on

• Fire up the SQL Server configuration Manager
• Double Click on the SQL Server Services
• On the right hand side panel find the SQL SERVER
• Right Click on it , go to properties and the Process ID is listed on the Services tab.