Archive Page 2

Refeshing Views

Running on a recently updated to SQL Server 2005 cluster, we were noticing some performance issues with one stored procedure in particular. We initially blamed the issue on optimizer changes, but were looking a fix that did not involve and object change.

Someone came up with the idea (not me) to refresh the view. I am aware that a view that contains the dreaded “SELECT *” may not contain new table columns until the view is refreshed, but I have never actually run into this issue in real life. In our situation the view definition had not changed, nor had the underlying objects, but refreshing the view resolved the performance issues.

So on that note i wrote a quick little cursor up refresh all views in a database. I hope someone finds this useful.

/*******************************************************************************
Name:			Refresh Views
Description:	Declare our variables and cursor. The cursor looks at and 
                schema bound views since the are not able to be refreshed.
Dependencies:	
Testing:		
********************************************************************************
Author - Date - Change Description
--------------------------------------------------------------------------------
Ron Carpenter - 07/01/2010 - Initial Version
*******************************************************************************/
DECLARE @View varchar(255) ;

DECLARE ViewCursor CURSOR LOCAL READ_ONLY FORWARD_ONLY
    FOR SELECT
            [Name]
        FROM
            sys.objects o
        WHERE
            Type_Desc = 'VIEW' AND
            NOT EXISTS (SELECT
                            1
                        FROM
                            sys.sql_dependencies d
                        WHERE
                            o.object_id = d.object_id AND
                            d.class = 1)

OPEN ViewCursor ;

FETCH NEXT FROM ViewCursor INTO @View ;
WHILE @@FETCH_STATUS = 0 
    BEGIN
        EXECUTE sp_refreshview @View ;

        FETCH NEXT FROM ViewCursor INTO @View ;
    END ;

CLOSE ViewCursor ;
DEALLOCATE ViewCursor ;

Display Job History

We have a service that polls a myriad of things several times an hour to send pages and alerts to the appropriate people based on a specific set of criteria. One of the things that it polls is the SQL job history, which can be helpful to be proactive in catching long running jobs or even if a job gets disabled.

The time that a job is scheduled to run and it’s run duration is saved as an integer in hhmmss format, so converting this to the datetime datatype requires some manipulation. You can find a lot of ways to do this using string functions, which is the way we had completed the conversion … until is started erroring.

The statement below will convert the Integer value to the datetime datatype using mathmatical functions instead of string functions, which is less prone to error.

The first thing that we will do is extract the hours, minutes and seconds into their separate parts using the modulo operator. Once we have that we will convert these values into seconds (the smallest interval we are working with) and add them together. Once we have the offset value in seconds from midnight we use the dateadd function and add the seconds to the run date. Viola! And it’s even a little faster.

SELECT
    DATEADD(ss,(((Run_Time - Run_Time % 10000) / 1e4) * 36e2 + 
                ((Run_Time % 10000 - Run_Time % 100) / 1e2) * 6e1 + 
                  Run_Time % 100),CAST(CAST(Run_Date AS char(8)) AS datetime)) StartTime,
    DATEADD(ss,(((Run_Time - Run_Time % 10000) / 1e4) * 36e2 + 
                ((Run_Time % 10000 - Run_Time % 100) / 1e2) * 6e1 + 
                  Run_Time % 100 + 
                ((Run_Duration - Run_Duration % 10000) / 1e4) * 36e2 + 
                ((Run_Duration % 10000 - Run_Duration % 100) / 1e2) * 6e1 + 
                  Run_Duration % 100),CAST(CAST(Run_Date AS char(8)) AS datetime)) EndTime,
    j.name JobName,
    jh.step_name StepName
FROM
    msdb.dbo.sysjobhistory jh
INNER JOIN msdb.dbo.sysjobs j
    ON jh.job_id = j.job_id
WHERE
    jh.step_id > 0
ORDER BY
    j.job_id,
    jh.step_id ;

Efficient way to do Paging in SQL Server

I was going through SQLServerCentral.com and found this pretty nice article to do paging in an efficient manner. So thought of sharing with you all and ofcourse it is a note to myself 🙂


WITH Keys
AS
(
 SELECT
 TOP (@PageNumber * @PageSize)
 rn = ROW_NUMBER() OVER (ORDER BY P1.Post_ID ASC)
 P1.Post_ID
 FROM
 dbo.Post P1
 ORDER BY
 P1.Post_ID ASC
),
SelectedKeys AS
(
 SELECT
 Top(@PageSize)
 SK.rn,
 SK.Post_ID
 FROM
 Keys SK
 WHERE
 SK.rn >((@PageNumber -1 ) * @PageSize)
 ORDER BY
 SK.Post_ID ASC
)
SELECT
 SK.rn,
 P2.Post_ID,
 P2.Thread_ID,
 P2.Member_ID,
 P2.Created_Date,
 P2.Title,
 P2.Body
FROM
 SelectedKeys SK
JOIN
 dbo.Post P2
ON
 P2.Post_ID = SK.Post_ID
ORDER BY
 SK.Post_ID ASC

Happy Programming!!!

Cheers,

Raja

Getting the controlname which caused postback

I am working on a site in which I use a lot of JQuery but there are some pages which I had to use Server Controls and there was a need to get which control caused the postback.  After a bit of searching I found this nice method which did the trick.


private string  getPostBackControlName()
{
 Control control = null;
 //first we will check the "__EVENTTARGET" because if post back made by       the controls
 //which used "_doPostBack" function also available in Request.Form collection.
 string ctrlname = Page.Request.Params["__EVENTTARGET"];
 if (ctrlname != null && ctrlname != String.Empty)
 {
 control = Page.FindControl(ctrlname);
 }
 // if __EVENTTARGET is null, the control is a button type and we need to
 // iterate over the form collection to find it
 else
 {
 string ctrlStr = String.Empty;
 Control c = null;
 foreach (string ctl in Page.Request.Form)
 {
 //handle ImageButton they having an additional "quasi-property" in their Id which identifies
 //mouse x and y coordinates
 if (ctl.EndsWith(".x") || ctl.EndsWith(".y"))
 {
 ctrlStr = ctl.Substring(0, ctl.Length - 2);
 c = Page.FindControl(ctrlStr);
 }
 else
 {
 c = Page.FindControl(ctl);
 }
 if (c is System.Web.UI.WebControls.Button ||
 c is System.Web.UI.WebControls.ImageButton)
 {
 control = c;
 break;
 }
 }
 }
 return control.ID;
}

Happy Programming!!!

Cheers,

Raja

Getting column description in SQL Server 2005

We are working on code generation and thought it would be neat to pull information from description (where we intend to put validations for ex. DOB – No future date).  The following query did that trick.


SELECT
 [Table Name] = i_s.TABLE_NAME,
 [Column Name] = i_s.COLUMN_NAME,
 [Description] = s.value
FROM
 INFORMATION_SCHEMA.COLUMNS i_s
LEFT OUTER JOIN
 sys.extended_properties s
ON
 s.major_id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME)
 AND s.minor_id = i_s.ORDINAL_POSITION
 AND s.name = 'MS_Description'
WHERE
 OBJECTPROPERTY(OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME), 'IsMsShipped')=0
 --AND i_s.TABLE_NAME = 'table_name'
ORDER BY
 i_s.TABLE_NAME, i_s.ORDINAL_POSITION

Hope this helps someone.

Happy Programming!!!

Cheers,

Raja

3.14

Happy PI day!

~Ron

Anniversary

So I just realized that Ctrl+Shift+B has been around for just over a year. Happy Birthday to us! 🙂