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
.
This blog is created to add all the information and experiences that I have learnt while working on MS sql server as a DBA. The blog is also updated frequently with the real world problem that I encounter hands on at work and the resolutions to resolve them. I hope this blog will be of some use to you and you will revisit. Thank you for stopping by and you are welcome to leave comments.
Tuesday, August 28, 2012
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.
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
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.
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
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
Labels:
accessing control,
asp.net,
code behind,
controls,
listview
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
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
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
Subscribe to:
Posts (Atom)