Tuesday, August 28, 2012

Database Mail Executable Minimum Lifetime

We are facing some issue with Sql Server 2008 R2 Database Mail Queue functionality using "msdb.dbo.sp_send_dbmail" system stored procedure. After some time email functionality goes into suspended mode. SQL server executes a SP named "msdb.dbo.sp_readrequest;" which goes into suspended mode. This happen most of the time and we get dead locks alert

I adjusted the database mail executable minimum lifetime ( in seconds ) from 600 to 10 and this problem seems to have dissapear.

Databasemail object on SSMS --------> Configure databasemail ----------------->View or change system parameters--------->Database mail executable minimum lifetime change it to 10 seconds





.

Monday, August 20, 2012

BC30554: masterpage is ambiguous

I did not get this error on my dev machine. I did not get this compilation error in the staging site. I got this error in the produciton site.

Resolution: There was another file called masterpage2.aspx that was using the same namespace masterpage. It looked like a test file or for some reason a blank masterpage file was renamed this. Removed the masterpage2.aspx file , did a build and it worked fine.

Friday, August 17, 2012

Error handling in transactions in sql 2008

BEGIN TRANSACTION TRAN1

BEGIN TRY
--put all the valid statements here--


   CREATE TABLE #test(intREsult int);

   INSERT INTO #test Values (4/0);
   INSERT INTO #test Values (1/2);


  COMMIT TRAN TRAN1
END TRY


BEGIN CATCH
--the error handling code including the rollback

    DECLARE @errorMEssage nvarchar(1000);
   SELECT @errorMEssage = ERROR_MESSAGE();
   ROLLBACK TRAN TRAN1;
   RAISERROR(@errorMEssage,16,1);

END CATCH

Friday, July 20, 2012

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.

Wednesday, July 18, 2012

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

Monday, July 16, 2012

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